Re: procedure tuning
From: Uri Dimant (urid_at_iscar.co.il)
Date: 02/14/05
- Next message: Al Blake: "problems joing data that contains quotes....."
- Previous message: Uri Dimant: "Re: how can i mapped the sql data to the ms access db?"
- In reply to: trespasser: "procedure tuning"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 14 Feb 2005 07:26:52 +0200
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@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: Al Blake: "problems joing data that contains quotes....."
- Previous message: Uri Dimant: "Re: how can i mapped the sql data to the ms access db?"
- In reply to: trespasser: "procedure tuning"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
