May 12, 2005 at 9:01 am
Hi everyone,
My question may have a very obvious answer. I have a stored procedure that does some deduping. When I run it using the exec command in Query Analyzer it takes about 10 minutes to complete, but when I execute the individual queries one by one they each take about 3 seconds.
These are the queries executed in my sproc:
1. Insert original company records to OrigCompany Table
2. Insert Duplicate company records to DupCompany Table
3. Delete duplicate records from Company table
4. Delete all records from OrigCompany table
These step are repeated 6 times with different search conditions.
Why the big performance difference? Ideas anyone?
May 12, 2005 at 9:05 am
I would check for deadlock issues, maybe you have transactions that are held too long for nothing and that is causing other process to have to wait for the lock releases.
May 12, 2005 at 10:35 am
The other thing that may cause this is that the proc may have been compiled and stored with bad query execution plans. The SQL you execute dynamically will get the most current statistics and probably optimize correctly. Dropping and re-adding the proc or doing sp_recompile on the tables might give you better results.
May 12, 2005 at 2:37 pm
I've tried dropping anf re-entering and sp_recompile. Didn't make a difference.
I've beed playing around with different isolation levels but they all give me the same results. 10 minutes execution time.
Should I use specific locks on my SELECT statements instead? If so, which lock is best to use if I'm doing many INSERTS and DELETES?
Thanks for your help.
Sandra
May 13, 2005 at 2:50 pm
OK, instead of guessing, let's isolate the problem.
Get the execution plan for the proc and for each of the component queries.
Put some prints in the proc to get the wall clock times for each query within the proc.
The points where the plans differ may give us a clue and the timing will tell us whether the pieces are generally slow or whether there is a specific portion of the proc taking up the most time. It might also point out if there are other components of the proc that are taking up the time and we're looking in the wrong spot for performance degradation.
May 16, 2005 at 1:02 pm
sorry if this seems like a silly question but how can I print the wall clock time in the sproc?
May 16, 2005 at 1:05 pm
print GetDate()
May 16, 2005 at 1:21 pm
Duh, thanks...
May 16, 2005 at 2:32 pm
Ok, so I checked the execution plans of each individual query in the sproc and compared them to the entire sproc - the plans are the same.
After running the sproc with getdate() after each query I found there is a 10 minute gap at the same point every time. It seems this one query is what takes so long to run, but I have the same UPDATE query happening before and after this one without any time delay what so ever...
I'm not sure I'm getting the correct results here, is there a better way (more detailed way) to measure the elapsed time?
May 16, 2005 at 2:40 pm
set statistics time on --timers
set statistics io on --disk reads write
May 16, 2005 at 3:02 pm
Run Profiler to see what going on there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 17, 2005 at 9:36 am
I run profiler. I run TSQL duration, Tuning and TSQL SPs templates and they all told me the same thing ( which I already knew) that one query was slowing everything down.
I examined the differences again and realized the reason it was slow was do to comparisons it was doing on a bigint type! I was mad at myself for not noticing eariler...it's just one of those silly things...
Thanks everyone for your help.
Sandra
May 17, 2005 at 9:37 am
What do you mean exactly?
May 17, 2005 at 10:27 am
The query that was slowing the sproc was an UPDATE statement and it's condition was comparing an attribute that was of bigint type. I changed the attribute to varchar type and the sproc now runs in about a minute.
SQLProfilerTSQL_SPs was the best trace template and helped me discover that issue, it displayed all the extra processing that was being done and helped me realize why.
It's a great little trace.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply