Hi SQL Gurus,

I have a table with 28.5 million rows that is being searched against
(and joined to 4 other tables).

The query plan shows that for this table it does an index scan against
the relevant columns and does a key lookup to retrieve the other needed
columns from this table (ones is a VARCHAR(2000) column). It then does a
nested loop to bring the results together.

Hoping to get ride of the nested loop and key lookup, which have a cost
27% and 7% respectively for the entire query plan, I've gone and used
the "Included Columns" feature to add the columns that are gathered with
the key lookup into the appropriate index.

This has the opposite effect I expected, it does remove the loop and key
lookup, but it now takes the query 12 seconds to execute verse the 9
seconds previously. Also the new query plan has a cost of 455 instead of
the previous 380.

Can anyone explain this to me? I'm researching methods to increase the
performance on this particular query, I really need it to be faster,
previously people have told me that 9 seconds for 28.5 million records
is good, but it seems this should be performing much better.

Thank you.

Oh here is what the table looks like, since this is where the query
spends the lions share of it's time:

CREATE TABLE [dbo].[tbl_test](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[FK] [bigint] NOT NULL,
[Col1] [varchar](50) NULL,
[Col2] [varchar](50) NULL,
[Col3] [varchar](150) NULL,
[Col4] [varchar](50) NULL,
[Col5] [varchar](50) NULL,
[Num1] [int] NULL,
[BigColumn] [varchar](2000) NULL,
[Col6] [varchar](50) NULL,
[Col7] [varchar](50) NULL)

Bryan Smith

MS Sql Server LazyDBA home page