Query runs way slower than equivalent SP

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query runs way slower than equivalent SP

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2007-01-15 : 16:51:03

Hi,

I am having hard time figuring out why a query would run 10 if not 100 times slower when executed from a stored procedure. I have a query, which I run as a prepared statement. So, when I profile my SQL Server 2005 database I see the following sequence of call in the profiler:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
declare @p1 int
exec sp_prepare @p1 output, N'[param types]', N'[query definition]'
exec sp_execute @p1, params

According to the profiles sp_execute takes 4 seconds to complete. However, when I run the same query from the Query Analyzer it takes milliseconds. Moreover, if I run the same 4 statements as above in Query Analyzer it also takes millis to complete.

I can post the query if needed.

Thanks.

Alec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-15 : 17:18:55

Are you also calculating the time to create the execution plan?

Peter Larsson
Helsingborg, Sweden

lebedev
Posting Yak Master

126 Posts

Posted - 2007-01-15 : 18:04:21

I guess I was pretty vague in my original post.

First, I have a query that I am trying to execute as a prepared statement in Java. During this process, SQL Server Profiler indicates that the following actions were performed:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
declare @p1 int
exec sp_prepare @p1 output, N'[param types]', N'[query definition]'
exec sp_execute @p1, params

It also indicates that sp_execute took about 4 seconds to complete.

Second, I take the exact same 4 actions and execute them in Query Analyzer. The time it takes to do that is way less than a second.

My question is why is there such a discrepancy in duration?

Now, as I thought more about it I realized that maybe transferring the rows returned by the query to the client takes a long time. And maybe this time it takes to retrieve and display the rows is also counted towards sp_execute duration by SQL Server Profiler.

What does Duration column mean in SQL Server Profiler? Does it only include query execution time or also the time it took to process the result set?

Thanks.

Alec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-15 : 18:16:49

Have you calculated the overhead for java when connection to the database and parsing the command, and for the SQL server to run the stored procedures, and for java to wait for the result/error?

Peter Larsson
Helsingborg, Sweden

lebedev
Posting Yak Master

126 Posts

Posted - 2007-01-15 : 19:45:17

Establishing connection with the database and parsing the command on the Java side won't be a part of Duration measurement in SQL Server profiler. I guess it's just the overhead of delivering the query result to the Java client (app server).

Unless somebody else had a similar problem, I will close this thread.

Thanks for your help.

Alec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources