October 16, 2009 at 9:01 am
Go and rebuild all your indexes on staging as well. Do it after the stats update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2009 at 9:13 pm
Sorry guys for the delayed response but I've been trying to figure this out. The query seems to be very unpredictable even with the indexes on their. I even purge the data I do the comparision with and yet still it takes 40 minutes to sometimes 4 hours.
I'm very confused as what to do. One thing I noticed is that I join the EdpNo to the fintEcometryId in my insert statement I posted. The data type for the EdpNo is a decimal while the fintEcometryId data type is an INT. Could that be causing for the index to be ignored or something because of different data types?
I really looked at the query and can't see how it can be written. The table I'm doing comparisions with has about 1 million records now down from 4+ millions rows after I purged that "staging" table.
Any further advice? Forgot to mention, yes, I did rebuild the indexes and updated the statistics. Sometimes the query will fetch the results in 3~14 seconds, sometimes it'll take 40+ minutes... the problem is when this runs for too long it puts exclusive locks on major tables bringing the whole website down... the job runs at 2 AM.. and I'm having it to monitor it at 2 AM now...
Thanks,
S
--
:hehe:
October 23, 2009 at 5:29 am
Yes, absolutely the difference in data types will prevent the indexes from getting used. It's an implicit conversion and they look like magic and therefore free, but it's just a CAST occurring under the covers and like any other function run against a column as part of a WHERE clause or a JOIN, it'll prevent the index from being used.
Are you possibly experiencing blocking on top of everything else? If you're hitting contention for resources it will slow things down.
"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 23, 2009 at 6:29 am
Yes, a whole lot of blocking. Blocking causes our web applications to stop connecting with the DB inturn causing the website to go down. I had to stop my procedures earlier this morning after 10 minutes as I noticed it just kept running in some kind of an infinite loop. I am going to correct this data type issue so both of them match.
Thanks for the advice.
--
:hehe:
October 23, 2009 at 3:33 pm
I don't know if this thread is still live, but I'll throw in a couple things I've run into in my brief SQL career.
I've run into problems in the past joining to views. Is vwRegularItems a view? If it is and returns a large dataset, it might be worth optimizing that query. We have a third party system that uses some poorly constructed views, and we've been able to increase performance by adjusting them.
Another thing that has worked for me in the past is using INNER joins to non correlated subqueries instead of using the IN operator. Or better still, if the sub query is fast and returns a large data set, selecting the subquery into a # temp table, indexing that, and then joining to it. Or even better still using EXISTS instead of IN. Since EXISTS returns true as soon as it finds a single match it can be quite fast.
WHERE EXISTS (SELECT ri.EdpNo
FROM [1-Database-1].[dbo].[vwRegularItems] ri
LEFT JOIN [2-Database-2].[dbo].[tblproductsizewidthcolor] pswc
ON ri.EdpNo=pswc.fintEcometryId
WHERE pswc.fstrLongSku IS NULL AND pswc.fintEcometryId IS NULL AND Level1Type = 'SZ'
AND Level2Type = 'WD' AND Level3Type = 'CO'
AND a.EdpNo = ri.EdpNo)
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply