CODEWITHSUNDEEP
sqlsql-server
M Kenyon II
M Kenyon II

Reputation: 4254

Faster SQL query for over 2 million rows

I have a bunch of tables I'm joining to do a search query. This query allows several optional parameters. But the main table that it is searching (T_AS400_BLRPIN00) has over 2 million rows.

When I run the execution plan, most of the time spent is on a nonclustered index scan.

The table (T_AS400_BLRPIN00) has 38 columns, I won't list them all here (unless you feel it is necessary) but some of the key fields are: 

INSPASER (int, null) -- Part of the old key  
INSPIND (varchar(1), null) -- The other part of the old key  
T_AS400_BLRPIN00_ID (PK, int, not null) -- The new key

The Index that is used looks like this: 

CREATE NONCLUSTERED INDEX [IX01_T_AS400_BLRPIN00] 
ON [dbo].[T_AS400_BLRPIN00] 
(
    [INSPASER] ASC,
    [INSPIND] ASC
)

My stored procedure / query searches the table like this:

FROM
    dbo.T_AS400_BLRPIN00 AS Insp
LEFT JOIN 
    dbo.T_AS400_BLRPEQP00 AS Eqp ON Insp.T_AS400_BLRPEQP00_ID = Eqp.T_AS400_BLRPEQP00_ID
WHERE
    (@pint_INSPASER=0 OR Insp.INSPASER = @pint_INSPASER)
    AND 
    (ISNULL(INSPIND,'') like '%' + @pstr_INSPIND + '%')

I don't like searching such a large table with a varchar, but for now we need that ability. Sometimes @pstr_INSPIND is an empty string ('') because they are not searching by that, sometimes it is a single char().

The execution plan shows the index scan (nonclustered) to take 63%, and then the Nested Loop (Inner Join) to the Key Lookup takes 36%.

Is there a better way I can engineer this?

Upvotes: 0

Views: 1317

Answers (3)

SQLburn
SQLburn

Reputation: 129

The "like" is bad. Also, what is in that variable (@pstr_INSPIND)? Parameter sniffing?

What are your statistics like? Estimated vs actual rows returned?

Not sure how are you populating @pstr_INSPIND but you might be open to sqlinjection.

Upvotes: 0

Sparky
Sparky

Reputation: 15085

Try changing this

  WHERE

(@pint_INSPASER=0 OR Insp.INSPASER = @pint_INSPASER)
AND 
(ISNULL(INSPIND,'') like '%' + @pstr_INSPIND + '%')

to this

  WHERE
    (@pint_INSPASER=0 OR Insp.INSPASER = @pint_INSPASER)
    AND 
    (INSPIND = @pstr_INSPIND or INSPIND is NULL)

Upvotes: 1

Luaan
Luaan

Reputation: 63722

Doing like '%abc%' is deadly - you're not using the index at all.

So you'll likely get a better performance by removing it from the composite index altogether.

Also, if possible, try reducing the search so that you're doing like 'abc%' instead - that can in fact use the index. Also, why are you using % at all on a varchar(1) column?

Upvotes: 0

Related Questions