Where Statements
From: Phil (Phil_at_discussions.microsoft.com)
Date: 02/07/05
- Next message: Kuido K?lm via SQLMonster.com: "LIKE or CHARINDEX algoritms"
- Previous message: Andy: "Re: Difference between = and IN"
- Next in thread: Hugo Kornelis: "Re: Where Statements"
- Reply: Hugo Kornelis: "Re: Where Statements"
- Reply: Phil: "RE: Where Statements"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 7 Feb 2005 08:57:05 -0800
Hi all,
I have some code that looks like below and after a few suggestions from a
previous post I have tried passing it all through as variables but with no
luck, has anyone else got any other ideas how I can speed this stored
procedure up, all necessary tables have indexs????
Any help would be great, thanks!
Phil
@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
- Next message: Kuido K?lm via SQLMonster.com: "LIKE or CHARINDEX algoritms"
- Previous message: Andy: "Re: Difference between = and IN"
- Next in thread: Hugo Kornelis: "Re: Where Statements"
- Reply: Hugo Kornelis: "Re: Where Statements"
- Reply: Phil: "RE: Where Statements"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
