|
Home > Archive > MS SQL Server > November 2006 > Select vs. Stored Proc containing Select
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Select vs. Stored Proc containing Select
|
|
| cbrichards via SQLMonster.com 2006-11-14, 7:14 pm |
| Any idea why...
I run DBCC DropCleanBuffers and DBCC FreeProcCache.
I pull a select statement out of a stored procedure (which contains about 16
joins) and in executing the select statement on its own, the reads are
approximately 300,000 with a duration of about 4.5 seconds.
I run the stored procedure that contained the select statement, and the reads
are 42,464 and the duration is 1.38 seconds.
If I run the DBCC's (mentioned above) and reverse the order (stored procedure
first, select statement second), I get the same results.
In addition to this, the stored procedure contains not only the select
statement (that was run on its own), but two update statements, and another
select. Yet, the stored procedure still outperforms the select statement run
on its own.
I am running SQL Server 2000, SP4, on Windows 2003.
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200611/1
| |
|
| Can you post the query and the procedure's code?
Adi
cbrichards via webservertalk.com wrote:
> Any idea why...
>
> I run DBCC DropCleanBuffers and DBCC FreeProcCache.
>
> I pull a select statement out of a stored procedure (which contains about 16
> joins) and in executing the select statement on its own, the reads are
> approximately 300,000 with a duration of about 4.5 seconds.
>
> I run the stored procedure that contained the select statement, and the reads
> are 42,464 and the duration is 1.38 seconds.
>
> If I run the DBCC's (mentioned above) and reverse the order (stored procedure
> first, select statement second), I get the same results.
>
> In addition to this, the stored procedure contains not only the select
> statement (that was run on its own), but two update statements, and another
> select. Yet, the stored procedure still outperforms the select statement run
> on its own.
>
> I am running SQL Server 2000, SP4, on Windows 2003.
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200611/1
| |
| cbrichards via SQLMonster.com 2006-11-14, 7:14 pm |
| Unfortunately, I cannot. The select statement has approximately 16 joins and
reveals quite a bit about our schema. I know this limits your ability to
analyze. Any blind possibilities as to why this is occurring would be
appreciated.
Adi wrote:[color=darkred
]
>Can you post the query and the procedure's code?
>
>Adi
>[quoted text clipped - 20 lines]
--
Message posted via http://www.webservertalk.com
| |
| Tracy McKibben 2006-11-14, 7:14 pm |
| cbrichards via webservertalk.com wrote:
> Any idea why...
>
> I run DBCC DropCleanBuffers and DBCC FreeProcCache.
>
> I pull a select statement out of a stored procedure (which contains about 16
> joins) and in executing the select statement on its own, the reads are
> approximately 300,000 with a duration of about 4.5 seconds.
>
> I run the stored procedure that contained the select statement, and the reads
> are 42,464 and the duration is 1.38 seconds.
>
> If I run the DBCC's (mentioned above) and reverse the order (stored procedure
> first, select statement second), I get the same results.
>
> In addition to this, the stored procedure contains not only the select
> statement (that was run on its own), but two update statements, and another
> select. Yet, the stored procedure still outperforms the select statement run
> on its own.
>
> I am running SQL Server 2000, SP4, on Windows 2003.
>
Have you compared the execution plans to see what they're doing
differently? That might give a clue...
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
| |
| Uri Dimant 2006-11-15, 5:13 am |
| Hi
Does the SP/SELECT accept a parameter/s? Number of reads is a huge
difference , so I think that SQL Server generates somehow a bad execution
plan for the adhoc query
"cbrichards via webservertalk.com" <u3288@uwe> wrote in message
news:6949d949ab711@u
we...
> Any idea why...
>
> I run DBCC DropCleanBuffers and DBCC FreeProcCache.
>
> I pull a select statement out of a stored procedure (which contains about
> 16
> joins) and in executing the select statement on its own, the reads are
> approximately 300,000 with a duration of about 4.5 seconds.
>
> I run the stored procedure that contained the select statement, and the
> reads
> are 42,464 and the duration is 1.38 seconds.
>
> If I run the DBCC's (mentioned above) and reverse the order (stored
> procedure
> first, select statement second), I get the same results.
>
> In addition to this, the stored procedure contains not only the select
> statement (that was run on its own), but two update statements, and
> another
> select. Yet, the stored procedure still outperforms the select statement
> run
> on its own.
>
> I am running SQL Server 2000, SP4, on Windows 2003.
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200611/1
>
| |
|
| If you work with parameters, then maybe you have different types of
parameters in the procedure and the select statement. This could cause
table scan instead of index seek.
Adi
cbrichards via webservertalk.com wrote:
> Unfortunately, I cannot. The select statement has approximately 16 joins and
> reveals quite a bit about our schema. I know this limits your ability to
> analyze. Any blind possibilities as to why this is occurring would be
> appreciated.
>
> Adi wrote:
>
> --
> Message posted via http://www.webservertalk.com
|
|
|
|
|