StoredProcedures - which is fastest and why

[alwinSCH]

CREATE PROCEDURE sp_Get_Borderel_4d_data @MaatschappijID numeric, @VervalDatum datetime, @LanguageID char(1) AS DECLARE @MaatschappijIDLoc numeric DECLARE @VervalDatumLoc datetime DECLARE @LanguageIDLoc char(1) SET @MaatschappijIDLoc = @MaatschappijID SET @VervalDatumLoc = @VervalDatum SET @LanguageIDLoc = @LanguageID SELECT Agent.AgentID, Agent.Naam AS AgentNaam, ProRataDetail.InternPolisNummer, (sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie, sum(ProRataNettoPremie) As ProRataNettoPremie, sum(ProRataTaksen) As ProRataTaksen, sum(ProRataBrutoPremie) As ProRataBrutoPremie, sum(ProRataCommissie) As ProRataCommissie, sum(ProRataBeheerskosten) As ProRataBeheerskosten FROM fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijIDLoc, @VervalDatumLoc, @LanguageIDLoc) as ProRataDetail LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID WHERE Polis.Demo <> 1 GROUP BY Agent.AgentID, Agent.Naam, ProRataDetail.InternPolisNummer ORDER BY Agent.Naam, ProRataDetail.InternPolisNummer GO ---------------------------------------------------------- CREATE PROCEDURE sp_Get_Borderel_4d_data @MaatschappijID numeric, @VervalDatum datetime, @LanguageID char(1) AS SELECT Agent.AgentID, Agent.Naam AS AgentNaam, ProRataDetail.InternPolisNummer, (sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie, sum(ProRataNettoPremie) As ProRataNettoPremie, sum(ProRataTaksen) As ProRataTaksen, sum(ProRataBrutoPremie) As ProRataBrutoPremie, sum(ProRataCommissie) As ProRataCommissie, sum(ProRataBeheerskosten) As ProRataBeheerskosten FROM fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijID, @VervalDatum, @LanguageID) as ProRataDetail LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID WHERE Polis.Demo <> 1 GROUP BY Agent.AgentID, Agent.Naam, ProRataDetail.InternPolisNummer ORDER BY Agent.Naam, ProRataDetail.InternPolisNummer GO -------------------------------------------------------------------------- Why is the first procedure, faster than the second? The ony difference are the extra local variables! thanks, A.

[Mycroft Holmes]

The question is why are you using 3 extra variables, that takes 3 extra cycles and therefore it is slower ;P

Never underestimate the power of human stupidity RAH

[alwinSCH]

I can run it a 1000 times! the difference is: the first one with local variables: I get a result in 5 seconds. the second one without local variables: I get a result in 20 minutes!!!! Thank you

[WoutL]

This looks like a problem caused by Parameter sniffing. Just google on it and you'll see what I mean.

Wout Louwers

[alwinSCH]

thank you all, it has something to do with Parameter Sniffing! It explains a lot! Is this the best practice? to disable parameter sniffing? Thank you

[WoutL]

Well, You are using the parameters in the call to fn_Get_ProRataDetail. You could try what happens ig you do the local var trick in that function. That way other sp's may benefit too. But the local var trick is the only one I know besides using With Recompile.

Wout Louwers

[Mycroft Holmes]

See thats the reason I spend time here, never heard of parameter sniffing, sounds disgusting. There are some excellent articles out there, another tool in the ongoing fight for performance. Thanks Wout.

Never underestimate the power of human stupidity RAH