Re: procedure tuning
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 02/15/05
- Next message: Louis Davidson: "Re: Update string too long?"
- Previous message: siaj: "Re: DetachDB problems - DMO"
- In reply to: Lee Tudor: "Re: procedure tuning"
- Next in thread: Uri Dimant: "Re: procedure tuning"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 14 Feb 2005 22:31:23 -0500
I was actually talking about how hard it will be to optimize because each
set of parms may require tuning, where a static stored procedure will in
fact only have a single plan to optimize. And if you only have to optimize
a few different where clauses, it is much easier.
Either way, it will be interesting to find out how it turns out.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Blog - http://spaces.msn.com/members/drsql/ Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Lee Tudor" <mr_tea@ntlworld.com> wrote in message news:Np9Qd.503$Se3.77@newsfe5-win.ntli.net... >I agree, > The permissions aspect and the compile at run time make it difficult to > verify and maintain larger dynamic SQL statements, but it seems in this > case, the performance benefits outweigh the drawbacks. however ... > > It maybe the case that the procedure is only called in 2 (n) different > modes, each with its own set of applicable parameters. In this case it > could well be better and simpler to split the functionality into 2 (n) > seperate sub-procedures each optimised to deal with the appropriate > variation. > > Mr Tea > > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:O8P4qwrEFHA.1564@TK2MSFTNGP09.phx.gbl... >> Yeah, this would probably be my suggestion, but after you build this it >> will be even more difficult to tune because there are many more variables >> to consider. There may be something else at issue here as well, so that >> was the purpose of asking for more information. >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - drsql@hotmail.com >> SQL Server MVP >> >> Compass Technology Management - www.compass.net >> Pro SQL Server 2000 Database Design - >> http://www.apress.com/book/bookDisplay.html?bID=266 >> Blog - http://spaces.msn.com/members/drsql/ >> Note: Please reply to the newsgroups only unless you are interested in >> consulting services. All other replies may be ignored :) >> "Lee Tudor" <mr_tea@ntlworld.com> wrote in message >> news:JIRPd.1759$ma4.727@newsfe2-gui.ntli.net... >>> Indexing on tables will not do much good with few sargable components in >>> your selection clauses (the best you could hope for is seeking the >>> tblFDVSourceSurvey on sourceID=0 and loop joining from there). WITH >>> RECOMPILE will reduce performance even further, each recompile will >>> produce an execution plan just as bad as the last one. >>> >>> I agree that the 'null' assignments to the variables to indicate a match >>> all condition would be better as NULL but going down that avenue has no >>> significant gains in it either. >>> >>> my advice would be to build up your query as a unicode string including >>> each element of the where clause only when applicable and then use >>> sp_executesql to run it. >>> >>> set @strsql = N'SELECT * >>> INTO #levelTable >>> FROM dbo.fnLevelTable(@sourceID, @sectorID, @manufacturerID, >>> @rangeID, >>> >>> @bodyID, @transmissionID, >>> @fuelID, @doors, @derivativeID, @vehicleTypeId, >>> >>> @levelName) >>> >>> CREATE INDEX tmpind ON #leveltable (surveyid, code, description) >>> >>> >>> SELECT #levelTable.code, >>> #levelTable.[description], >>> tblFDVClientSurvey.sale_edition AS edition, >>> AVG(tblFDVClientSurvey.sale_value) / >>> AVG(tblFDVSourceSurvey.trade_rv) * 100.0 AS value, >>> COUNT(tblFDVClientSurvey.sale_value) AS [count] >>> FROM tblFDVClientSurvey >>> INNER JOIN tblVRM >>> ON tblVRM.reg_plate = tblFDVClientSurvey.reg_plate >>> INNER JOIN #levelTable >>> ON #levelTable.surveyID = tblVRM.surveyID >>> INNER JOIN tblFDVSourceSurvey >>> ON tblFDVSourceSurvey.reg_plate = tblVRM.reg_plate >>> AND tblFDVSourceSurvey.clientID = tblVRM.clientID >>> AND tblFDVSourceSurvey.sale_edition = tblFDVClientSurvey.sale_edition >>> LEFT JOIN tblAuctionLocation >>> ON tblAuctionLocation.auction_location = >>> tblFDVClientSurvey.auction_location >>> WHERE tblFDVSourceSurvey.sourceID = 0 ' >>> >>> IF @mileageBand<>0 >>> SET @strsql=@strsql +N' AND(tblFDVClientSurvey.mileage_band = >>> @mileageBand) ' >>> >>> IF @disposalRoute<>'null' >>> SET @strsql=@strsql +N' AND(tblFDVClientSurvey.disposal_method = >>> @disposalRoute)' >>> >>> EXEC sp_executesql @strsql, N'@mileageband varchar(4), @disposalroute >>> varchar(4), .... ', @mileageband, @disposalroute, .... >>> >>> >>> There's a fair bit more work to do here but it should give you a good >>> idea. >>> >>> Mr Tea >>> 'Hey. Look! Look at that mother move.' - Damon Killian >>> >>> >>> "trespasser" <harlequintp@blazemail.com> wrote in message >>> news:5d4eccf94476aef4ed2a6362244b92ff@localhost.talkaboutsoftware.com... >>>> Hi everyone, >>>> >>>> I am having problems re-writting a stored procedure to run quicker, it >>>> looks something like this >>>> @clientID INT, >>>> @sourceID tinyint, >>>> @vehicleTypeId varchar(4), >>>> @analysisMonth INT, >>>> >>>> @companyId VARCHAR(6), >>>> >>>> @mileageBand varchar(4), >>>> @startMonth varchar(6), >>>> >>>> @disposalRoute varchar(4), >>>> @airCon VARCHAR(4), >>>> @documentation VARCHAR(4), >>>> @alloy VARCHAR(4), >>>> @warranty VARCHAR(4), >>>> @satNav VARCHAR(4), >>>> @paintOrigin VARCHAR(4), >>>> @solidPaint VARCHAR(4), >>>> @servicePack VARCHAR(4), >>>> @tailLift VARCHAR(4), >>>> >>>> AS >>>> >>>> IF @seats = 'null' >>>> SET @seats = '0' >>>> >>>> IF @doors = 'null' >>>> SET @doors = '0' >>>> >>>> IF @mileageBand = 'null' >>>> SET @mileageBand = '0' >>>> >>>> IF @derivativeID = 'null' >>>> SET @derivativeID = '0' >>>> >>>> SELECT * >>>> INTO #levelTable >>>> FROM dbo.fnLevelTable(@sourceID, @sectorID, @manufacturerID, >>>> @rangeID, >>>> >>>> @bodyID, @transmissionID, >>>> @fuelID, @doors, @derivativeID, >>>> @vehicleTypeId, >>>> >>>> @levelName) >>>> >>>> CREATE INDEX tmpind ON #leveltable (surveyid, code, description) >>>> >>>> >>>> SELECT #levelTable.code, >>>> #levelTable.[description], >>>> tblFDVClientSurvey.sale_edition AS edition, >>>> AVG(tblFDVClientSurvey.sale_value) / >>>> AVG(tblFDVSourceSurvey.trade_rv) * 100.0 AS value, >>>> COUNT(tblFDVClientSurvey.sale_value) AS [count] >>>> FROM tblFDVClientSurvey >>>> INNER JOIN tblVRM >>>> ON tblVRM.reg_plate = tblFDVClientSurvey.reg_plate >>>> INNER JOIN #levelTable >>>> ON #levelTable.surveyID = tblVRM.surveyID >>>> INNER JOIN tblFDVSourceSurvey >>>> ON tblFDVSourceSurvey.reg_plate = tblVRM.reg_plate >>>> AND tblFDVSourceSurvey.clientID = tblVRM.clientID >>>> AND tblFDVSourceSurvey.sale_edition = >>>> tblFDVClientSurvey.sale_edition >>>> AND tblFDVSourceSurvey.sourceID = 0 >>>> LEFT JOIN tblAuctionLocation >>>> ON tblAuctionLocation.auction_location = >>>> tblFDVClientSurvey.auction_location >>>> WHERE (tblFDVClientSurvey.mileage_band = @mileageBand OR @mileageBand = >>>> 0) >>>> AND (tblFDVClientSurvey.disposal_method = @disposalRoute OR >>>> @disposalRoute >>>> = >>>> 'null') >>>> AND (tblFDVClientSurvey.air_conditioning = @airCon OR @airCon = 'null') >>>> AND (tblFDVClientSurvey.documentation = @documentation OR >>>> @documentation >>>> = >>>> 'null') >>>> AND (tblFDVClientSurvey.alloy_wheels = @alloy OR @alloy = 'null') >>>> AND (tblFDVClientSurvey.warranty = @warranty OR @warranty = 'null') >>>> AND (tblFDVClientSurvey.sat_nav = @satNav OR @satNav = 'null') >>>> AND (tblFDVClientSurvey.paint_origin = @paintOrigin OR @paintOrigin = >>>> 'null') >>>> AND (tblFDVClientSurvey.solid_paint = @solidPaint OR @solidPaint = >>>> 'null') >>>> AND (tblFDVClientSurvey.service_pack = @servicePack OR @servicePack = >>>> 'null') >>>> AND (@auctionLocation = 'null' OR >>>> tblAuctionLocation.auction_location = @auctionLocation OR >>>> tblAuctionLocation.auction_house = @auctionLocation) >>>> GROUP BY #levelTable.code, >>>> #levelTable.[description], >>>> tblFDVClientSurvey.sale_edition >>>> >>>> I am trying to speed up the above procedure but after looking through >>>> various messages boards I am having very little luck, I have now done >>>> all >>>> the usual things like making sure that all the index's are on the >>>> necessary tables and that they are of the same data type as well as now >>>> changing the process of creating the temp table, this is now done with >>>> a >>>> create statement rather than from the function, tha main part of this >>>> sp >>>> that slows things down are the multiple where statements, I have tried >>>> a >>>> process of passing the main part through as a variable and then passing >>>> each part of the where statement through as seperate variables based >>>> upon >>>> whether there is a variable declared but either I have set this out >>>> in-correctly but I am not getting the results that I was exspecting, >>>> does >>>> anyone else now how better to write this above statement as it is >>>> driving >>>> me nuts. >>>> >>>> Thanks in advance to anyone that can help. >>>> >>>> Regards >>>> Philip >>>> >>> >>> >> >> > >
- Next message: Louis Davidson: "Re: Update string too long?"
- Previous message: siaj: "Re: DetachDB problems - DMO"
- In reply to: Lee Tudor: "Re: procedure tuning"
- Next in thread: Uri Dimant: "Re: procedure tuning"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
