Query plan not using PK, but unclustered index ... why ?

Basic SOURCE Table defns:

table_L (bint bigint PRIMARY KEY, lookup_val1 int, lookup_val2 int)

lkup (id int identity(1,1) PRIMARY KEY, catid int, abbr varchar(20), descr nvarchar(40)) UNIQUE NONCLUSTERED INDEX IX_1 on lookup(catid, abbr) UNIQUE NONCLUSTERED INDEX IX_2 on lookup(catid, descr)

(tried it without "unique" nonclustered indices, doesn't help)

select L.bint, cast(LU1.abbr as int), cast(LU2.descr as int)
from table_L L
        inner join lkup LU1 on L.lookup_val1 = LU1.ID
        inner join lkup LU2 on L.lookup_val2 = LU2.ID

QUERY plan says that it will use indexes: IX_1 and IX_2 (Index Scan) to fullfil the query.

Since "catid" is not even mentioned in the query, why has it chosen a scan of an index rather than the direct route to the actual record? Why not a "Clustered Index SEEK" ?

Use of the non-clustered indexes causes an error (Conversion failed when converting the nvarchar value "some string" to data type int). Of course, if it had used the PK, there would be no conversion issue.

Adding the resultant query plan:

|--Hash Match(Inner Join, HASH:([LU1].[id])=([L].[lookup_val1]), RESIDUAL:([table_L].[lookup_val1] as [L].[lookup_val1]=[lkup].[id] as [LU1].[id]))
   |--Compute Scalar(DEFINE:([Expr1007]=CONVERT(int,[lkup].[descr] as [LU1].[descr],0)))
   |    |--Index Scan(OBJECT:([lkup].[IX_2] AS [LU1]))
   |--Hash Match(Inner Join, HASH:([LU2].[id])=([L].[look_val2]), RESIDUAL:([table_L].[lookup_val2] as [L].[lookup_val2]=[lkup].[id] as [LU2].[id]))
        |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(int,[lkup].[abbr] as [LU2].[abbr],0)))
        |    |--Index Scan(OBJECT:([lkup].[IX_1] AS [LU2]))
        |--Clustered Index Scan(OBJECT:([table_L].[PK_table_L] AS [L]))

more ▼

asked Oct 20, 2009 at 05:42 PM in Default

avatar image

15 1 1 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

The Execution Plan in Microsoft SQL Server is cost based. It is cheaper for the Storage Engine to scan a table (sequential reads) than seek a table (random reads). In your case, it depends on what more columns are fetched from the query. The Storage Engine will avoid to do Key Lookups if possible.

Also, since there is no SARG-able filter clause in your query, a SEEK can never happen since you basically told the Storage Engine to fetch all records.

more ▼

answered Oct 20, 2009 at 07:42 PM

avatar image

1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

  1. Saints preserve us -- It's a MUCK (EAV) !!!! I'm going to bet that this query returns unanticipated extra rows someday because it doesn't take CATID into consideration.

  2. Because there is no WHERE condition, the entire primary table is being scanned. As volumes increase, at some point doing a hash merge of two scans will be less work than looking up values one at a time by ID.

  3. The query joins to the LKUP table twice. This means LKUP is actually treated as two separate tables. Each of the joins is handled separately, and the optimizer will seek the most efficient way to perform each of the joins.

  4. Because the indexes begin with the CATID column, which isn't part of the join, and the primary table is indexed on BINT, which also isn't part of the join, the query would have to do an entire table scan to look up even one description or abbreviation. Using a hash merge of two scans is certainly going to be more efficient.

  5. As Grant mentioned, each of the indexes probably contains fewer pages than the primary table (because each index row is narrower, by virtue of omitting one of the two largest columns in the table). So, for each join, there is an index which will take less work to scan than the primary table.

more ▼

answered Oct 21, 2009 at 12:07 AM

avatar image

Bob Hovious
1.6k 5 9 13

(comments are locked)
10|1200 characters needed characters left

The conversion error is not caused by the use of the index. It's your cast that's the issue there. Why are you even bothering to cast the values in the SELECT statement? If they are numeric in nature, though stored as strings (why), they'll return just fine.

Since it's doing a scan, it probably sees fewer pages in each of those indexes so it's trying to save time by scanning them rather than scanning the table/clustered index which probably has more pages in it.

more ▼

answered Oct 20, 2009 at 10:46 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

Also, regardless of index strategy, you're asking for a varchar and a nvarchar to be CASTed as int, so why are you surprised that you get a conversion error?

more ▼

answered Oct 20, 2009 at 08:01 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 20, 2009 at 05:42 PM

Seen: 3094 times

Last Updated: Oct 21, 2009 at 10:09 AM

Copyright 2018 Redgate Software. Privacy Policy