Which procedure is the fastest?

Question

Which procedure is the fastest?

Scheerens Alwin

SSC Enthusiast

Points: 149
More actions
March 18, 2010 at 2:41 am

#212542

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

run it 5 times each, and see if there is really a one second difference...each time..

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Scheerens Alwin SSC Enthusiast Points: 149 More actions · Answer 2

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

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 3

Could it be down to parameter sniffing, I know the optimizer behaves differently with local variables.

Can you see difference in the actual execution plans?

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vidya_pande SSCertifiable Points: 5275 More actions · Answer 5

Its issue of parameter sniffing

You can explore with RECOMPILE option.

Scheerens Alwin SSC Enthusiast Points: 149 More actions · Answer 6

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

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 7

Scheerens Alwin (3/18/2010)
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

I would have a read of this

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing

It's an excellent way to understand what's going. Helped me out.

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/