April 5, 2018 at 3:22 pm
ChrisM@Work - Tuesday, February 13, 2018 6:07 AMThe majority of your tables don't have a clustered index. It's highly unlikely that this omission is deliberate. Addressing the issue will change the execution plan of almost every query referencing this database. There is little point in optimising this query until your database has been properly redesigned.
I have now added indexes as advised by Sentry One. The database design was pre me joining the team and I believe this was an ommision at the time of Dev. It has improved performance though. Can you offer any tips on optimizing. The query hasn't changed. Apologies, Im a noob in this area.
April 6, 2018 at 1:42 am
jason 84805 - Thursday, April 5, 2018 3:22 PMChrisM@Work - Tuesday, February 13, 2018 6:07 AMThe majority of your tables don't have a clustered index. It's highly unlikely that this omission is deliberate. Addressing the issue will change the execution plan of almost every query referencing this database. There is little point in optimising this query until your database has been properly redesigned.I have now added indexes as advised by Sentry One. The database design was pre me joining the team and I believe this was an ommision at the time of Dev. It has improved performance though. Can you offer any tips on optimizing. The query hasn't changed. Apologies, Im a noob in this area.
Sure - buy this: https://www.apress.com/gb/book/9781430242031
Do your tables have clustered indexes now? Have you had a DBA cast an experienced eye over your database?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2018 at 6:55 am
ChrisM@Work - Friday, April 6, 2018 1:42 AMjason 84805 - Thursday, April 5, 2018 3:22 PMChrisM@Work - Tuesday, February 13, 2018 6:07 AMThe majority of your tables don't have a clustered index. It's highly unlikely that this omission is deliberate. Addressing the issue will change the execution plan of almost every query referencing this database. There is little point in optimising this query until your database has been properly redesigned.I have now added indexes as advised by Sentry One. The database design was pre me joining the team and I believe this was an ommision at the time of Dev. It has improved performance though. Can you offer any tips on optimizing. The query hasn't changed. Apologies, Im a noob in this area.
Sure - buy this: https://www.apress.com/gb/book/9781430242031
Do your tables have clustered indexes now? Have you had a DBA cast an experienced eye over your database?
There is a newer version of the book. The material there is better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2018 at 7:15 am
Lots and lots of scans. Despite a large number of filters, you're getting scans all over the place. Ignoring the fact that you don't have clustered indexes, the indexes you do have are not being used well. The 10 million row scan on Object 7 is going to kill you on performance. The filter there is on column47 and is a not equals. Depending on the values in that column, you might have a hard time indexing that. Can you filter that data in some other way? Certainly, since it joins to Object11, which is also doing a scan, maybe filter there. Basically, the indexes you do have just aren't cutting it. Yeah, returning 60k rows can be somewhat time consuming, but we should be able to filter at the source to reduce the time and it's not happening. Also, since we can only see the anonymized values, I can't tell, but the LIKE filters, are they done this way '%xxx%' or this way 'xxx%' or this 'xxx'. If the first one, you're in trouble and that could explain all the scans. Wild cards on both sides of a LIKE will lead to scans. If it's the third choice, you can use an equality search instead, giving the optimizer more choices. I still think it all goes back to your indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply