"Parameter Sniffing" and Inner Join Questions
From: alien2_51 (anonymous_at_discussions.microsoft.com)
Date: 09/23/04
- Next message: DavidM: "Re: Poor VB Programming"
- Previous message: Hugo Kornelis: "Re: Rowset solution is sought. Please help"
- In reply to: Stephen Hendricks: ""Parameter Sniffing" and Inner Join Questions"
- Next in thread: Stephen Hendricks: "Re: "Parameter Sniffing" and Inner Join Questions"
- Reply: Stephen Hendricks: "Re: "Parameter Sniffing" and Inner Join Questions"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Sep 2004 16:07:45 -0700
What version of SQL Server are you running..? Are you
using any of your incoming parameters in the where clause
of any of your queries..?
CREATE PROCEDURE proc1 @p1 int
AS
SELECT * FROM table1 WHERE col1 = @p1
GO
Look at KB320208
>-----Original Message-----
>I have two questions regarding a situation that we ran
into (and solved)
>recently that have got me wondering what's really
happening.
>
>1) We ran into a stored procedure that seemed to be
exhibiting a
>parameter sniffing issue (sorta). The stored procedure
was performing
>poorly in that it took about twenty minutes to execute.
We took the
>bulk of the code out and ran it as a script and it took
about one minute
>to execute. These results were consistent no matter
which executed
>first.
>
>We looked at estimated execution plan but saw nothing
that would suggest
>poor use of indexes, etc.
>
>Seemed to me that this could be a parameter sniffing
issue BUT the
>stored procedure performs poorly even on the first
execution (when the
>plan should be tailored to the data passed in). Our
first attempt was
>to add the WITH RECOMPILE option but no improvement. Now
it seems like
>it's not parameter sniffing. Several futile attempts
later we tried to
>simply copy the parameters into local variables and use
the locals in
>the body of the procedure. This fixed the problem.
>
>Question: Can anyone explain the difference in execution
time between
>the procedure and the script? Why does the use of local
variables help?
>
>
>2) In the course of working through this issue, my
colleague tried
>replacing the original join syntax:
>
> TableA a JOIN TableB b on a.MyKey = b.MyKey
>
>with this:
>
> TableA a INNER JOIN TableB b on a.MyKey = b.MyKey
>
>and reports a noticeable increase in execution speed. I
don't have
>metrics on this and have not confirmed his findings.
>
>Question: Since the "JOIN" syntax is equivalent to "INNER
JOIN", can
>anyone shed light on why this would be happening.
>
>TIA
>
>=======================================
>Everyone here speaks SQL; some are more fluent, others
less. When
>describing your SQL object (table, etc.), do so in the
language that we
>all understand - SQL, not English. It makes it easier to
understand
>your issue and makes it more likely that you will get the
assistance
>that you are asking for.
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
- Next message: DavidM: "Re: Poor VB Programming"
- Previous message: Hugo Kornelis: "Re: Rowset solution is sought. Please help"
- In reply to: Stephen Hendricks: ""Parameter Sniffing" and Inner Join Questions"
- Next in thread: Stephen Hendricks: "Re: "Parameter Sniffing" and Inner Join Questions"
- Reply: Stephen Hendricks: "Re: "Parameter Sniffing" and Inner Join Questions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
