x

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],
 [CustomerID],
 [StartDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
more ▼

asked Oct 11 '12 at 09:16 PM in Default

EuniceRH gravatar image

EuniceRH
150 4 4 7

The query appears to be fairly straight forward. How many rows does it return? Can you post the execution plan?
Oct 12 '12 at 03:01 AM JohnM
How big is the table?
Oct 12 '12 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 '12 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 '12 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 '12 at 05:22 AM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

(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 '12 at 07:40 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(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 '12 at 07:41 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x562

asked: Oct 11 '12 at 09:16 PM

Seen: 784 times

Last Updated: Oct 13 '12 at 10:45 AM