Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-21 : 07:00:19
|
| Robert writes "THE STORYI create a large Store Procedure. At the beginning of it I created a variable, called @spStart, with a getdate() timestamp. At the end I created variable, called @spEnd, the gets populated with getdate(). Both of these variables get written to a table call "check time". This allows me to see how long it takes the query to run.THE PROBLEMThe difference in the start and stop time in 2 seconds. The actual run in the application is 17 seconds. Is there a way to speed this up?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-21 : 07:01:54
|
| There is still the overhead of the network traffic to deliver the results to the client app, and if it is a web application, time for the results to be rendered on screen. If you want to see how long it takes for the APPLICATION to run, you have to put your timing code into the APP, not the stored procedure. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-21 : 09:18:52
|
Rob's right, but the difference between 17 seconds and 2 seconds is a lot. It's little more work to narrow down the problem. Assuming the clocks on the web server and the sql server are different, careful work will let you narrow down the differences and where the time is lost.Pass a timestamp as a parameter from the web to the procedure. The difference in the web clock and the server clock at the moment of entry (DT1) should be quite small. Record both times.At exit, note the server time, pass that and DT1 back to the caller. The web can take a new timestamp, compare DT2 to DT1. If DT1 is large, then the web and sql server clocks are different. You can probably assume DT1 should be near zero and subtract DT1 from DT2 to get an estimate of the return trip time.If you don't see a big chunk of the 17 seconds at this point, the remainder of the time must be post processing of the recordset results. You can chop up the post processing and timestamp the steps for measurement.You might want to look up ADO RS.GETROWS. It has a reputation for better performance than RS.MOVENEXTSam |
|
|
|
|
|
|
|
|
Time of Return on SP