procedure tuning

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"trespasser" <harlequintp@.blazemail.com> wrote in message
news:5d4eccf94476aef4ed2a6362244b92ff@.lo
calhost.talkaboutsoftware.com...
> Hi everyone,
<snip>

> 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
trespasser,
May I ask why the string 'null' is being stored in so many columns?
Or should those condition tests have been <@.variable_name IS NULL>?
Sincerely,
Chris O.|||First some information. Have you ran the query checking the plan? That is
step one. You need to find out which parts of the procedure are costing you
the most. Then how much time do you expect it to take, and how long is it
taking.
How big are the tables you are dealing with? Are there some parameters that
execute faster than others? How often is the proc called? 10 times a
second, an hour, a day?
You may have to optimize certain cases. The first thing I might try is to
add WITH COMPILE to your proceudure:
create procedure procedureName
(
<@.parms ...?>
)
WITH RECOMPILE
AS
This might help some, if you are getting varied results for different
parameter choices.
One other really big thing. You have

> AND (tblFDVClientSurvey.alloy_wheels = @.alloy OR @.alloy = 'null')
Null is not a string value. This should be
AND (tblFDVClientSurvey.alloy_wheels = @.alloy OR @.alloy is NULL)
Note also that this type of construct works pretty well, but can be bad for
performance if you have complex sets (as you seem to.) But first more
information is needed.
----
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 :)
"trespasser" <harlequintp@.blazemail.com> wrote in message
news:5d4eccf94476aef4ed2a6362244b92ff@.lo
calhost.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
>|||Indexing on tables will not do much good with few sargable components in
your selection clauses (the best you could hope for is sing 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@.lo
calhost.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
>|||Hi
I addition you may want to check out this very useful article
http://www.sommarskog.se/dyn-search.html
"trespasser" <harlequintp@.blazemail.com> wrote in message
news:5d4eccf94476aef4ed2a6362244b92ff@.lo
calhost.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
>|||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 sing 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@.lo
calhost.talkaboutsoftware.com...
>|||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...
>|||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...
>