Problem with stored procedure
-
Hi All,
Could you point me out where is problem where I should start search for solution :S
I have stored procedure with execution takes about 5 minutes when I execute it like this: exec storedname 1, 2, 3, 4
But when I execute this queries not as stored procedure but as queries from query analyzer it takes only 2 secononds.
Procedure looks like that:
create procedure xxx
@a int, @b-2 int, @C int, @d int
as
create table #temp (id int primary key)
insert into #temp
exec otherprocedure @a
select col1, col2, col3
from table1 a left join (select col1, col2 from table2 where col3=@b) b
on a.col1 = b.col1
join #temp t
on t.id = b.col2
drop table #temp
-
You will have to provide more information.
Your query will not run as Col1 is ambiguous.
Why do you left join when the join to #temp will take out any nulls in effect making the join between a and b an inner join?
-
Usually when you see the difference in performance it's one of two things - bad query plan is cached, or its the different in the "set" settings.
-
Hi it was only example, my query exactly looks like this:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE GetRawAUniverse
@AttributeID int,
@AttributeDateID int,
@DateID int,
@CountryID int
AS
CREATE TABLE #ACountry ( AssetID int PRIMARY KEY )
INSERT INTO #ACountry
EXEC GetACountryUniverse @CountryID
SELECT RA.AssetID, RA.Data AS Data1, PA.Data AS Data2
FROM
(SELECT AssetID, Data
FROM RawAttributes
WHERE AttributeID = @AttributeID
AND DateID = @AttributeDateID ) RA
LEFT OUTER JOIN
(SELECT AssetID, Data
FROM ProcessedAttributes
WHERE AttributeID = @AttributeID
AND DateID = @DateID ) PA
ON RA.AssetID = PA.AssetID
INNER JOIN
#ACountry AC
ON RA.AssetID = AC.AssetID
ORDER BY RA.Data
DROP TABLE #ACountry
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
I've tried with recompile option but result is the same :S
How it is possible that it is so huge difference between executing some code in sp and the same code without sp ?
-
It could be any number of issues.
It may be a data type issue.
ie If AttributeID and/or DateID are not INTs (ie they could be tinyints or smallints) then they
will automatically cast to INTs and any indexes will not be used. (ie You should make sure that the
data types of the parameters and of the columns are the same.)
-
Hi, parameters are ok.
But finally I've found why it took so much time. The problem was with wrong choose join type by query optimizer (nested loop instead of hash join), I've added hint and now everything is ok, ufff. Thanks for your help.
Rafal
-
just a litle thought...
wouldn't this be easier to get a first glimp of what the last query is doing ?
SELECT
RA.AssetID, RA.Data AS Data1, PA.Data AS Data2
FROM RawAttributes RA
LEFT OUTER JOIN ProcessedAttributes PA
ON PA.AttributeID = RA.AttributeID
AND PA.DateID = @DateID
and PA.AssetID = RA.AssetID
INNER JOIN #ACountry AC
ON RA.AssetID = AC.AssetID
WHERE RA.AttributeID = @AttributeID
AND RA.DateID = @AttributeDateID
ORDER
BY RA.Data
Check the execution plan, maybe the optimizer gets it now and the hint may nolonger be needed.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi, I've checked this and in this case optimizer gets wrong join type once again. I'll stay with that hint
Regards,
Rafal
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply