May 21, 2014 at 11:46 am
The report runs much quicker now that I have gotten rid of the unnecessary tables, fields, etc. but I've attached the execution plan for your review. I'm not really sure what information you are looking for with the tables and indexes. If you could spell it out for me, I would appreciate it.
May 21, 2014 at 12:35 pm
aberndt (5/21/2014)
The report runs much quicker now that I have gotten rid of the unnecessary tables, fields, etc. but I've attached the execution plan for your review. I'm not really sure what information you are looking for with the tables and indexes. If you could spell it out for me, I would appreciate it.
This is enough to get started. You desperately need to update your indexes on OPERATION. The estimated row count is 484,136 but the actual row count is 7,746,176. Ola Hallengren has an awesome page that discusses index and statistics maintenance. http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Also the where predicates are killing you here. When you have a leading wildcard you force the sql engine to look at each and every row. Sometimes this is unavoidable but without more knowledge of the data and the requirements it is tough to know what is correct here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2014 at 1:04 pm
That is a great article. I am going to re-read it before I do anything. I had a consultant for my Manufacturing ERP set up SQL for me and they only put in the maintenance tasks that they felt I needed, defrag indexes and integrity checks, and backups, of course. That was almost 5 years ago. I am now planning another new SQL server and ERP upgrade so I will definitely look more closely at the maintenance tasks. I don't think there is much I can do with the Where statements as I need to have the date parameter but I may try to use a view on the server to narrow down the amount of data that the report has to review. Thank you so much for your help and patience!
May 21, 2014 at 2:37 pm
aberndt (5/21/2014)
That is a great article. I am going to re-read it before I do anything. I had a consultant for my Manufacturing ERP set up SQL for me and they only put in the maintenance tasks that they felt I needed, defrag indexes and integrity checks, and backups, of course. That was almost 5 years ago. I am now planning another new SQL server and ERP upgrade so I will definitely look more closely at the maintenance tasks. I don't think there is much I can do with the Where statements as I need to have the date parameter but I may try to use a view on the server to narrow down the amount of data that the report has to review. Thank you so much for your help and patience!
The date parameter is not the issue. It is when you have stuff like this:
Where MyField LIKE '%SomeValue%'
If you can change that to remove the leading wildcard you will see a marked performance improvement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2014 at 6:49 am
That did make a difference...I guess I never thought about that before. Thanks 🙂
May 22, 2014 at 7:14 am
aberndt (5/22/2014)
That did make a difference...I guess I never thought about that before. Thanks 🙂
Think of it like a phone book. If you want to find all names beginning with "Smi" it is pretty easy. You "seek" the pages where the first "Smi" is, then you seek the pages until the end. On the other hand if you try to find all names that contain the letters "Smi" you would have to "scan" every single name on every single page. Obviously the second way is MUCH slower.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply