March 20, 2009 at 5:04 am
Hi,
I've got an odd problem with a query.
I have a fairly simple query, with a lot of inner joins. It is encapsulated in an SP.
I noticed it was slow and when I took it out of the SP, I was able to identify an unindexed column that I was sorting on. I removed the ORDER BY and the query went from 30s to 1s.
I created a brand new SP with this query, expecting it to take 1s. It still takes 30s!
To summarise:
- the query on its own takes 1s.
- an SP that encapsulates the query takes 30s.
I have summarised the query below:
CREATE PROCEDURE myProc
@param int
AS
BEGIN
SELECT
table_b.*,
table_f.field2,
table_d.field2,
table_e.field2,
dbo.function_g(
table_a.field4,
table_a.field5,
table_a.field6,
table_a.field7,
table_e.field2,
GETDATE()
) AS string_from_parameters_above
FROM
table_a
INNER JOIN table_b
ON table_b.field1 = table_a.field1
AND @param = table_a.field1
INNER JOIN view_c
ON view_c.field1 = table_a.field1
INNER JOIN table_d
ON table_d.field1 = table_a.field2
INNER JOIN table_e
ON table_e.field1 = table_b.field2
INNER JOIN table_f
ON table_f.field1 = table_a.field3
END
GO
--
-- CALLING THIS AS A PROCEDURE TAKES 30 seconds
--
EXEC myProc @param=1000
GO
--
-- CALLING THIS AS A DIRECT T-SQL STATEMENT TAKES 1 second
--
DECLARE@param int
SET @param = 1000
SELECT
table_b.*,
table_f.field2,
table_d.field2,
table_e.field2,
dbo.function_g(
table_a.field4,
table_a.field5,
table_a.field6,
table_a.field7,
table_e.field2,
GETDATE()
) AS string_from_parameters_above
FROM
table_a
INNER JOIN table_b
ON table_b.field1 = table_a.field1
AND @param = table_a.field1
INNER JOIN view_c
ON view_c.field1 = table_a.field1
INNER JOIN table_d
ON table_d.field1 = table_a.field2
INNER JOIN table_e
ON table_e.field1 = table_b.field2
INNER JOIN table_f
ON table_f.field1 = table_a.field3
GO
I'm not sure why there is a difference for what seems to be exactly the same statement.
Can anyone suggest what I could do to investigate this, please?
Many Thanks,
Ali
March 20, 2009 at 6:04 am
It sure sounds like a classic case of parameter sniffing. You can do a search for that phrase and see all kinds of different solutions. I'd suggest using a variable inside the query, initialized to 1000 and then change it to the value passed in as a parameter. But if you do the search, you'll see all kinds of other possible solutions. I'd suggest staying away from the extreme ones like WITH RECOMPILE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2009 at 6:06 am
Thanks Grant, I'll search for that and see what I come up with.
Very much appreciate the reply...
March 21, 2009 at 8:34 am
Great suggestion Grant. That's what I'd do - simply default the value in the proc to something and then bypass it with the value sent. Param sniffing has been greatly reduced by the way in SQL Server 2008...
April 29, 2009 at 4:10 am
As a follow up - I know it's weeks after the original, but I did what Grant suggested and it seems that param sniffing was killing the query. Happy, fast query. Thanks for the advice!
April 29, 2009 at 4:25 am
Thanks for posting the answer. It's always good to know when I got something right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply