Clever Geek Handbook

SELECT one of several columns with an index

I have this table:

    CREATE TABLE [Person].[Address](
       [AddressID] [int] NOT NULL,
       [AddressLine1] [nvarchar](60) NOT NULL,
       [StateProvinceID] [int] NOT NULL,

    CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
    (
       [AddressID] ASC
    )
    ) 
    GO

With this optional nonclustered index.

    CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_StateProvinceID 
    ON   [Person].[Address]
    (
       [AddressLine1] ASC,
       [StateProvinceID] ASC,
    )
    GO

I am working with existing application code. The application will pass me two parameters, but one of them can be "-1". When this happens, the goal is to match all strings for that parameter.

The current request looks like this:

    Select AddressID, AddressLine1, StateProvinceID
    From Person.Address
    Where ((AddressID = @AddressID) or (@AddressID=-1))
    and ((StateProvinceID = @StateProvinceID) or (@AddressID=-1))

This returns the data needed if either @AddressID or @StateProvinceID is '-1', it essentially ignores (-1 = -1 returns TRUE)

The problem is indexes. It does a scan, not a search, when I do it.

This returns the search:

    Select AddressID, AddressLine1, StateProvinceID
    From Person.Address
    Where AddressID = @AddressID 
    and StateProvinceID = @StateProvinceID

... While the current request is scanning a LOT slower.

The second request is fast, but doesn't work the way I need it.

Is there a way to allow traversal of one parameter or the other when using an index?

+3


source to share


4 answers


It seems that branching logic in a single SQL statement will always return SCAN instead of SEEK.

What I ended up doing to fix this was to write explicit (and ugly) branching logic to fetch a SIMPLE query for the conditions I wanted.

There are two parameters in my example. The goal is that when the given parameter = -1 matches all records, essentially ignoring that parameter in the where clause.

With two parameters, there are 4 possible scenarios.

  • set parm1, parm2 -1 (ignore)
  • parm1 -1 (ignore), set parm2
  • parm1 kit, parm2 kit
  • parm1 -1 (ignore), parm2 -1 (ignore) <- returns ALL lines

So, I wrote the branching logic with 4 If statements.



If parm1 <> -1 and parm2 = -1

 - then do a simple query that only uses parm1

If parm1 = -1 and parm2 <> -1

 - then do a simple query that only uses parm2

etc...

This, while EXTREMELY is ugly, returns SEEK every time. Ugly but very fast code. The nicer "When, Then Else" SINGULAR always scans and kills performance.

0


source


Use CASE

, WHEN

, THEN

your team sql ( link ). Hope it helps.



0


source 


Select AddressID, AddressLine1, StateProvinceID
From Person.Address
Where AddressID = CASE WHEN @AddressID = -1 THEN AddressID ELSE @AddressID END AND
      StateProvinceID = CASE WHEN @StateProvinceID = -1 THEN StateProvinceID ELSE @StateProvinceID END
0


source


I think that if you update your index with columns: [AddressLine1] and [StateProvinceID] to [StateProvinceID] and [AddressLine1] (set StateProvinceID to the index first) the query should use that index and not a full table scan.

0


source






More articles:


All Articles