October 23, 2008 at 10:06 am
Okay, I have to bite on this one. What server settings prevent you using indexed views?
😎
October 23, 2008 at 10:17 am
Unless I'm missing something, you're setting up a query that ultimately can't be optimized using regular indexes.
Indexing isn't going to help when you're doing a WHERE clause with a leading wildcard. This is going to be a table (or clustered index if the table has one) scan, since the B-tree is going to be of no help on syntax like
LIKE '%xyz%'
Unless what you happen to be looking for is an actual word, there's not much you're going to be able to do to optimize. If it IS a word - look at full-text indexing, which would help you (assuming you then switch over to a CONTAINS syntax instead)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 23, 2008 at 10:53 am
The issue I'm seeing is that Nested Loop operator, NodeId = 17, right before the Hash Match, NodeId = 16, that's performing the aggregation. Two issues there, first, it's using a loop join on 488686 rows. That should be a merge or hash join. Second, it's showing an estimated row count of 1 and an actual of 488686. That usually means you're dealing with multi-statement table valued functions or your statistics are totally mucked.
Another thing that jumps out is all the scans. You're seeing index scans or clustered index scans all over the place. That means the indexes are really being used. You may need to reevaluate your indexing scheme. You're ultimately returning 176 rows. Scanning through 1/2 million to get to 176 is the key issue.
I'd have to see the actual view script to make more sense of this.
"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
October 24, 2008 at 1:57 am
HI Grant,
Ok there are no nest table functions, so I'll try and do a another stats update but with full scan this time.
After that I'll have to supply the tables and indexes I guess 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 24, 2008 at 9:39 am
If you force this query you WILL wind up with some of them being just as bad as you have now. You can rob Peter or you can rob Paul, but you cannot keep both happy all the time. 🙂 I am with the person who recommended Full Text Indexing here. LIKE '%somecrap%' is just NOT the way to query data efficiently.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2008 at 8:57 am
HI all,
Thanks for all the support.
The wierd thing is that the where clause is on a table that only has 170+- rows in it.
It seems that the problem is coming in when it joins that result set onto one of the other views that is returning loads of rows.
I'll have a look into the full-text stuff, and see what comes of that.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 27, 2008 at 10:14 am
Christopher Stobbs (10/27/2008)
HI all,Thanks for all the support.
The wierd thing is that the where clause is on a table that only has 170+- rows in it.
It seems that the problem is coming in when it joins that result set onto one of the other views that is returning loads of rows.
I'll have a look into the full-text stuff, and see what comes of that.
Thanks
Chris
Given that bit of information I have another recommendation to try: take the stuff with the %% where clause and put it into a temporary table (NOT a table variable). Then join to that object in the refactored query. You may need to explicitly create index/stats on the column you are joining to get good optimization, but that will be a small price to pay to keep from having horrible queries due to imprecise row estimates.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2008 at 9:11 am
HI all,
OK the view can't be indexed cause it has left joins in it 🙁
Oh I also can't use temp tables cause the query is created dynamically by our Website which I'm not allowed to change.
It's looking more like a situation where the website gets priority over the DB and as a result they gonna be stuck with a slow query 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 28, 2008 at 9:28 am
HI all,
I think this is all the information need with regards to the from clauses of the views and that tables with there row counts and all the indexes on those tables.
I know it's a lot to look through so if no-one has time I would totally understand 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 28, 2008 at 9:47 am
What a mess! 😀 I can see why the optimizer can't get a good query! Not sure why you ask someone to review the code if you can't change it since it comes from a website though. Also, tuning a set of data and queries that complex could well be a days-long effort unless a lucky break occurs! Perhaps someone on this site has enough free time to give it a go. Best of luck with it! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2008 at 10:00 am
Sorry Guru,
I totally understand.
I guess I was just hoping that someone would spot a QUICK lucky fix for me.
wishful thinking I guess
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 28, 2008 at 10:52 am
quick lucky fixes can and do happen on this and other sites! It is just that the probability of 'quick' is inversely proportional to the size of the stuff being reviewed. And at over 10K (with the actual select's removed), this one can be classified as a beast. 🙂 Note too that there may well not be a magic bullet for this system. That level of complexity (umpteen nested views, huge numbers of tables, aggregates, likes, ins, etc, etc) really does make it almost impossible for the optimizer to have any reasonable chance at getting a good query plan. A possible solution may be forcing what the optimizer should do for a given input and then dealing with the poor performance that will arise when 'unfortunate' inputs are chosen.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 29, 2008 at 7:35 am
The wierd thing is that the where clause is on a table that only has 170+- rows in it.
It seems that the problem is coming in when it joins that result set onto one of the other views that is returning loads of rows.
Even though the front-end is where the SQL is being constructed, there is nothing that prevents it from building something a little more complicated than a single query. The problem is likely that the JOINS are processed before your WHERE. I would suggest that you do something like this:
DECLARE @xref TABLE (sId int)
SELECT sId
FROM [vw_69ClaimRprt]
WHERE [vw_69ClaimRprt].[ClientName] LIKE '%3663%'
AND [vw_69ClaimRprt].[StatusDesc] LIKE '%live%'
SELECT --all your fields go here--
FROM @xref x
INNER JOIN [vw_69ClaimRprt] ON x.sId = [vw_69ClaimRprt].sId
LEFT JOIN [vw_69FinancialsTotal_ULR] on [vw_69ClaimRprt].[sId]=[vw_69FinancialsTotal_ULR].[id]
ORDER BY [vw_69ClaimRprt].[Handler], [vw_69ClaimRprt].[StatusDesc]
-- Begin Edit --
Hmm, it would help if I would actually look at the execution plan... Okay looking at it now, my suggestion if expanded upon, would work if you could actually bypass the views to do some of you filtering and once you find the keys you want, then bring in the views to retrieve the data.
-- End Edit --
Scott
October 29, 2008 at 7:41 am
Scott, I really don't think that will help (and for more reasons than you missed the INSERT statement). 😀
Table variables cannot have any statistics against them, nor indexes. That will leave the optimizer back to a guess. That is why I explicitly called for a temporary table and NOT a table variable for the interim storage. I think this may be only marginally helpful though now that I have seen the complexity of the entire chain of statements.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 29, 2008 at 7:54 am
Do INSERTs really help? They just have a tendency to fill up tables with data.
Even though table variables do not have any statistics or indexes, I have found that they can help a lot when I have a large query and SQL Server can't figure out how to process it optimally.
Once the keys that I want are in the table variable, any index on them wouldn't matter that much if at all. Since I care about every one of the keys, I would want/need to do a complete scan of them when I JOIN that table into the rest of my query to fetch the rest of the data.
By breaking up my large query into a couple of smaller queries to handle the filtering of the data and finding the keys that I really care about, I have at times been able to greatly reduce the processing time of my query.
Scott
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply