sql 2008 primary key, index performance

When I build an index that looks exactly like my primary key, select performance increases dramatically. Why is this?

The table has 5 primary key fields, A B C D and LineNum. I want all the records for a given A, B, C and D. My select includes WHERE A= aaa AND B=bbb AND C=ccc AND D=ddd, and has ORDER BY A, B, C, D. The table has 4.1 million records in it. A fill takes about 25 seconds. When I create an index that also has A B C D and LineNum, the fill takes about 2 seconds. An index with just A B C and D takes about 2 seconds. When I delete the index, the query time goes back to about 25 seconds.

Any ideas why adding an index that looks like the primary key speeds up the query?
MikeBroderickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
I believe the primary key index is only used if all columns in the primary key index are used.

When you don't pass in LineNum it forces a scan.

By creating another index with just the 4 columns you have a non-clustered index which allows the data to come back quicker.
0
MikeBroderickAuthor Commented:
But the other index had 5 columns too.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
The order of the columns can matter too.  

http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys


Do you have them in the same order if you do a script index / script table as?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott PletcherSenior DBACommented:
Note: I'm assuming the PK is also the clustering key for the table in this case.

It depends on the width of the table; that is, how many bytes are in each row of the main table.

SQL will do a seek for as many leading columns in the clustering key as it can.  But once it finds the first value, it will do a sequential scan of the table until it hits the last value.  If the table is very wide, many more pages will be need to be scanned when using the table itself versus using an nonclustered index, whose pages contain only the key values.
0
MikeBroderickAuthor Commented:
The fields are in the correct order.

The primary key is the clustering key. A B C and D are varchar 10; LineNum is int. There are 3 other columns in the table, packed 6, packed 6 and varchar 256.

The matching record set is small, less than 100.
0
Scott PletcherSenior DBACommented:
Given that, the clustered index alone should work just fine, unless there's HUGE fragmentation in the main table.  Other than that, I can't imagine why a nonclustered index is so much better.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Can you check the execution plan for the original case and check which index it is using, if its using a different index,  try to forcing sql to use the CI

SELECT A,B,C,D
FROM yourTable c
WITH (INDEX(PK_abcd))
0
MikeBroderickAuthor Commented:
How do you check an execution plan?
0
MikeBroderickAuthor Commented:
I found execution plan documentation. Sorry. I will do it.
0
Scott PletcherSenior DBACommented:
If you want to actually run the query, you can use:

SET STATISTICS XML ON

before running the query.

If you only want to gen a plan, and not execute the query, you can do this:

SET SHOWPLAN_XML ON
GO
--query code goes here
0
Scott PletcherSenior DBACommented:
I suggest also checking the table fragmentation:

SELECT *
FROM sys.dm_db_index_physical_stats ( DB_ID('your_database_name'), OBJECT_ID('your_table_name'), 1, NULL, 'LIMITED' )
0
MikeBroderickAuthor Commented:
The code running the query is a Windows .Net forms application. The select clause has parameters (A=@A) in it. I copied the info into the sql window as follows:

USE [myTable]
GO
SELECT * FROM BCMLines
WHERE (SysName='yyyy' AND LibName='SPOR40' AND FileName='BSOSRC' AND MbrName='BSOAX1')
 ORDER BY SysName, LibName, FileName, MbrName, LineNum
GO

The results came back immediately (as I would expect and the plan shows using the primary key's index
0
Kyle AbrahamsSenior .Net DeveloperCommented:
issue connecting to the database perhaps?  Are you only observing the 25 seconds from .Net or from SQL server?
0
MikeBroderickAuthor Commented:
I do not think it is a connection issue. I can have the app up one screen and SQL mgt stdo on another. I can click an app button to test, add the index, click, remove the index, click. I think a connection problem would show slow times for all entries.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Now after running the query in SSMS, did you try it from  the application ? if its faster then it could be parameter sniffing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeBroderickAuthor Commented:
Yes, it is much quicker now.

I tried other columns (the A B C and D) values are sorted in ListViews so I can easily try different areas of the table. They run fast as well. Parameter sniffing stores the values in A B C and D, so I doubt it is parameter sniffing, but something changed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.