Does it have a clustered index?
What does the query look like? Are you doing any order by's?
PAYCHEX
Christopher Bellizzi
Information Technology * Database Administration
675 Basket Road * Webster, New York 14580
(585) 216-0670 * [Email address protected]
-----Original Message-----
From: Bryan Smith
[mailto:mssqldba-ezmlmshield-x87812332.[Email address protected]
Sent: Friday, December 28, 2007 12:35 PM
To: LazyDBA Discussion
Subject: Including non-key columns in an index.
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
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
The information contained in this message may be privileged,
confidential, and protected from disclosure. If the reader of this
message is not the intended recipient, or any employee or agent
responsible for delivering this message to the intended recipient,
you are hereby notified that any dissemination, distribution, or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately
by replying to the message and deleting it from your computer.
Thank you. Paychex, Inc.
MS Sql Server LazyDBA home page