Re: Where Statements
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/07/05
- Next message: Phil: "RE: Where Statements"
- Previous message: Hugo Kornelis: "Re: Difference between = and IN"
- In reply to: Phil: "Where Statements"
- Next in thread: Phil: "RE: Where Statements"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 07 Feb 2005 21:28:57 +0100
On Mon, 7 Feb 2005 08:57:05 -0800, Phil wrote:
>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????
Hi Phil,
Check out the execution plan to see if they are actually being used. There
are many cases where SQL Server decides that a table scan (or clustered
index scan - essentially the same) is quicker than using an index - and
more often than not, this conclusion is correct.
Also not that with so many optional search parameters, it's very hard to
find a set of indexes that will allways be useful!
>SELECT *
> INTO #levelTable
> FROM dbo.fnLevelTable(@sourceID, @sectorID, @manufacturerID, @rangeID,
>@bodyID, @transmissionID,
> @fuelID, @doors, @derivativeID, @vehicleTypeId,
>@levelName)
You didn't post then code of the function, so I can't comment on that.
>CREATE INDEX tmpind ON #leveltable (surveyid, code, description)
Try making this a clustered index. Another thing to try is to create the
table and the (clustered) index BEFORE calling the function to fill the
table:
CREATE TABLE #levelTable (....)
CREATE CLUSTERED INDEX tmpind ON #levelTable (...)
INSERT INTO #levelTable (...)
SELECT ...
FROM dbo.fnLevelTable (...)
And yet another (completely different) approach to try is to omit the
table completely and use the function in the query instead (you will have
to use an alias in order to refer to the columns in the table). This might
be especially useful of the function is an "inline table-valued UDF".
>WHERE (tblFDVClientSurvey.mileage_band = @mileageBand OR @mileageBand = 0)
(and similar for seats, doors and derivateID, I assume - though they are
apparently only used in the UDF)
Change this - make sure that the column and the variable have the same
datatype, to reduce the number of implicit conversions. Some conversions
will even inhibit the use of any index!
You'll probably hate me for what I'm about to write, but I'll write it
anyway - if you really want the best performance, make seperate stored
procedures for each allowed combination of search arguments, use one
central stored procedure that examines which arguments apply and call the
correct procedure for the actual query. That is the only way to ensure
that each distinct set of search argumentst gets it's own execution plan
that's optimal for that specific set.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Phil: "RE: Where Statements"
- Previous message: Hugo Kornelis: "Re: Difference between = and IN"
- In reply to: Phil: "Where Statements"
- Next in thread: Phil: "RE: Where Statements"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
