RE: Where Statements
From: Phil (Phil_at_discussions.microsoft.com)
Date: 02/08/05
- Next message: Ronan: "select count (distinct X, Y)"
- Previous message: Hugo Kornelis: "Re: Where Statements"
- In reply to: Phil: "Where Statements"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Feb 2005 01:05:03 -0800
Hi Hugo,
Just a quick thanks for all the help, it was much appreciated and I will try
out what you have suggested.
Thanks again, Phil
"Phil" wrote:
> 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: Ronan: "select count (distinct X, Y)"
- Previous message: Hugo Kornelis: "Re: Where Statements"
- In reply to: Phil: "Where Statements"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
