We help IT Professionals succeed at work.

sql 2008 primary key, index performance

55 Views
Last Modified: 2016-04-19
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?
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
But the other index had 5 columns too.
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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))

Author

Commented:
How do you check an execution plan?

Author

Commented:
I found execution plan documentation. Sorry. I will do it.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
issue connecting to the database perhaps?  Are you only observing the 25 seconds from .Net or from SQL server?

Author

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.
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.