slxdeveloper.com Community Forums

The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!

Architect Forums - ADO General
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to ADO General | New Thread

View: Search:

Author

Thread: Multiple Select Statments REALLY slow

Marc Johnson
Posts: 252

Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 18 Aug 06 10:44 AM

I have a form with about 8 Select statments that do counting and such. I've run the statements using the Database Engine Tuning Advisor in MSSQL 2005. It ended up creating some new indexes. However it does not seem to have improved the speed much at all. Is there anything specific to SalesLogix I can use?

Basically I'm counting account records with specific Type and Sub Type criteria. I'm also creating three account product listings (for our three primary product lines). These list the product name and a count per product.

I'd estimate this form takes about 5-10 seconds from the time the button is clicked to the time it displays on screen. Any tips? We are wanting to do a lot more of these 'live' reports.

[Reply][Quote]

Frank Chaffin
Posts: 475

Re: Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 18 Aug 06 12:25 PM

I do similar things for a client that has a large database. Some of the table I am going after have 1,000,000+ records. I ended up creating summary fields at the account, contact, and product level. I then run a nightly job that calculates the summary information. So all my view show stats as of yesterday.

[Reply][Quote]

Bob (RJ)Ledger
Posts: 1103

Re: Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 20 Aug 06 2:56 PM

Quote:

Originally posted by Marc Johnson

I have a form with about 8 Select statments that do counting and such. I've run the statements using the Database Engine Tuning Advisor in MSSQL 2005. It ended up creating some new indexes. However it does not seem to have improved the speed much at all. Is there anything specific to SalesLogix I can use?

Basically I'm counting account records with specific Type and Sub Type criteria. I'm also creating three account product listings (for our three primary product lines). These list the product name and a count per product.

I'd estimate this form takes about 5-10 seconds from the time the button is clicked to the time it displays on screen. Any tips? We are wanting to do a lot more of these 'live' reports.

So What version are you on? If it's v6.2.3/4 than you need to use the SLXProfiler to see what's going on. In fact in SP4 (and Scorpion) there is a SalesLogix Query Optimizer built-in.
Thsre's othere things you can do as well..
--
rjl

[Reply][Quote]

John Gundrum
Posts: 632

Re: Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 21 Aug 06 7:20 AM

I'm counting account records with specific Type and Sub Type criteria.

Have you tried executing these select statements as views or stored procedures? This would force the processing to occur on the SQL Server box itself.

Something else to think about is to combine your counting into one SELECT statement? For example

CREATE VIEW c_AccountCounts AS
SELECT SUM(CASE WHEN type = 'Customer' THEN 1 ELSE 0 END) AS cnt_Customer,
SUM(CASE WHEN type = 'Prospect' THEN 1 ELSE 0 END) AS cnt_Prospect,
SUM(CASE WHEN type = 'Suspect' THEN 1 ELSE 0 END) AS cnt_Suspect,
SUM(CASE WHEN subtype = 'Type A' THEN 1 ELSE 0 END) AS cnt_Type_A,
SUM(CASE WHEN subtype = 'Type B' THEN 1 ELSE 0 END) AS cnt_Type_B,
SUM(CASE WHEN subtype = 'Type C' THEN 1 ELSE 0 END) AS cnt_Type_C
FROM account

This makes it easy.

John G.

[Reply][Quote]

John Gundrum
Posts: 632

Re: Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 21 Aug 06 7:34 AM

I'm also creating three account product listings (for our three primary product lines). These list the product name and a count per product.

Oh.. sorry... the second half of your question was dealing with product listings. You could do the same thing like my provious examble for counting account type and subtypes. Only in this example you would be GROUPing your records. This SELECT assumes the product lines are using the product table productgroup field.

CREATE VIEW c_ProductLineCount AS
SELECT b.productgroup,
c.account AS accountname,
b.name AS productname,
COUNT(*) AS cnt_Product
FROM accountproduct a JOIN product b ON a.productid = b.productid
JOIN account c ON a.accountid = c.accountid
GROUP BY b.productgroup,
c.account,
b.name

You might already have something like this. Maybe need to tweak it to your needs.

John G.

[Reply][Quote]

Marc Johnson
Posts: 252

Re: Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 21 Aug 06 1:51 PM

I'm running 6.2.4. Is the optimizer in the Administrator app?

[Reply][Quote]

Mike Spragg
Posts: 1226

Re: Multiple Select Statments REALLY slow Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 22 Aug 06 4:26 AM

No, it's a sep exe in the pf\saleslogix folder. Run slxprofiler.exe and then login as Admin - monitor the SLX app and then select the Optimise option - it will then display the query you are running and the query that is actually running e.g. after security has been applied. You should look for the execute(ms) times and see how long it's taking to run and then run the actual query it runs via SQL Query Analyser and obtain the query plan. You should then see what the problem is and you can then optimise the original query via Query Analyser etc. What you are looking to do is see if query hints make a difference or whether your query can be altered to streamline it.

Once you have the optimised query - right click the query in SLXProfiler and select Optimise - select Shared/MSSQL and paste in the query you optimised. Note: You should only use this to optimise queries - don't try to "get clever" and take a "select * from x" and "optimise" it to "delete/update..." ! You have to use it in the spirit for what it was intended for !

If you have many users & teams defined then you may find that the security layer is causing the issue - if this is the case then you should alter all the "...inner join usersecurity..." queries to "inner merge join" - as this query hint has a huge benefit. I've seen queries drop from minutes to seconds when using this hint.

Good luck !

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 5/7/2024 10:07:04 AM