Help! UPDATE Statement takes too long to execute!

[andysk]

Dear All,

Usualy i execute an UPDATE Statement (quite complex one) it takes around no more than 1 minutes, but these two days it takes near 1 hour to finish. Is that any tools that i can see what exactly SQL Server working on, so i can identify what is the problem? or anybody ever experience the same thing?

I run MS SQL Server 7.0 on NT4. The file of database size is 1.2 GB. My free space still around 5 GB.

 

[ptheriault]

you can run a trace with SQL profiler. SQL profiler is a tool that is installed when you install sql. It can be a resource hog so try to limit your trace to only the login that is running the update.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[andysk]

Hi Paul,

As far as i know, SQL Profiler is only tell you what statement are being executed, what statement has been executed. Am i wrong?

In my case, i only have 1 statement, UPDATE statement.

Regards,
Andy

 

[SQLDenis]

try updating your statistics first and also check for blocking

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[andysk]

This problem has been solved. I separated the query into 2 statements and use temporary table, now it runs even faster than before. Thanks alot.

 

[ptheriault]

You may be able to speed it up even more if you use a table variable instead of a temp table. I most cases (not all) I get better performance with table variables.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[andysk]

Hi ptheriault, i am curious about table variable, can you give some examples? does it function exactly like a table or temporary table? does it always created with store procedures?

My program tell SQL Server to execute statement by statement (SQL Pass-Through), can it be apply with the same pattern?

 

[ptheriault]

Yes, instead of declaring a temp table like...

CREATE TABLE #tmp ...

You declare a table variable like...

DECLARE @table TABLE( col1 int,
... )

Besides possible increased performance there is the added benefit of not having to drop the table variable. It last only as long as the process. (just like normal variables.)





- Paul

- If at first you don't succeed, find out if the loser gets anything.