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