improve query average duration

The average duration for this query is 5 seconds, it's the highest average duration on my database. I'm wondering how to improve performance on this query. I need the data as is, I'm just wondering how to improve the index. SHould I: -remove ContractID from the INCLUDE in the Index? -make this Index the Clustered index for this table?

query text:

 SELECT "tblContracts"."CustomerID", "tblContracts"."StartDate" 
 FROM "dbo"."tblContracts" "tblContracts" 
 ORDER BY "tblContracts"."PublicId" DESC 

explain plan shows Index Scan (NonClustered) tblContracts.tblContracts$PublicID Cost 100%

the index is:

 CREATE UNIQUE NONCLUSTERED INDEX [tblContracts$PublicId] ON [dbo].[tblContracts]
  [PublicId] ASC
 INCLUDE ([ContractID],
more ▼

asked Oct 11, 2012 at 09:16 PM in Default

avatar image

200 4 7 12

The query appears to be fairly straight forward. How many rows does it return? Can you post the execution plan?

Oct 12, 2012 at 03:01 AM JohnM

How big is the table?

Oct 12, 2012 at 04:58 PM ThomasRushton ♦♦

23,000 rows

 name         rows    reserved   data     index_size  unused  
 tblContracts 22959   7944 KB    4304 KB  2960 KB     680 KB
Oct 12, 2012 at 05:27 PM EuniceRH

to answer Usman, http://www.quest.com/whitepapers/SQLServer_SQLTuning_final.pdf states: Since SQL Server 2000 now supports forward and backward scrolling through indexes with equal performance, you may see ORDERED FORWARD or ORDERED BACKWARD in the query plan. This merely tells you which direction the table or index was read. You can even manipulate this behavior by using the ASC and DESC keywords in your ORDER BY clauses.

Oct 12, 2012 at 05:33 PM EuniceRH
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You are pulling all the rows from tbl_contracts, there is no filter or where clause in the query, so SQL Server will always do index scan on [tblContracts$PublicId]. The performance of the query depends on the number of rows it return. Also, check for the fragmentation level of the index, fragmentation results in additional page reads and increases I/O.

more ▼

answered Oct 12, 2012 at 05:22 AM

avatar image

10.8k 37 57 51

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

I would be careful of changing the index based on just this query. You are right in noting that ContractId is not necessary to fulfill the query - removing it from the index will reduce I/O usage for this query. However, there is a possibility that this index is being used by another query that may well use the column ContractId. Removing that column could then cause that query to be slow.

I don't know what edition of SQL Server you are running, but if you have Enterprise Edition you could take a look at row or page compression to further reduce the I/O load for this. Again, this is not a silver bullet and needs to be fully considered before being implemented.

more ▼

answered Oct 12, 2012 at 07:40 AM

avatar image

26.2k 18 37 48

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

I agree with Anuj that returning all the rows would take some time depending upon the number of rows in the table. There would always be an index scan in that case. But the problem I see is the sorting ORDER of the index. You are ordering the data in DESCending order in your query but the index made is in ascending order. Change the sorting order of the index and see the difference.

As far as removing some column OR changing the clustered index is highly dependent upon your data access pattern and design. Without knowing much it is hard to recommend anything.

more ▼

answered Oct 12, 2012 at 07:41 AM

avatar image

Usman Butt
14.7k 6 13 21

(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 11, 2012 at 09:16 PM

Seen: 1272 times

Last Updated: Oct 13, 2012 at 10:45 AM

Copyright 2018 Redgate Software. Privacy Policy