Improving SQL Server query performance

Question

I have a written a query in which I create a string and take distinct values from the table based on conditions.

The table has some 5000 rows. This query takes almost 20 second to execute.

I believe that the string comparisons have made the query so slow. But I wonder what are my alternatives.

Query:

select distinct
   Convert(nvarchar, p1.trafficSerial) +' ('+  p1.sourceTraffic + ' - ' + p1.sinkTraffic + ' )' as traffic
from 
   portList as p1
inner join 
   portList as p2 ON p1.pmId = p2.sinkId
                  AND P1.trafficSerial IS NOT NULL 
                  AND (p1.trafficSerial = p2.trafficSerial) 
                  AND (P1.sourceTraffic = P2.sourceTraffic) 
                  AND (P1.sinkTraffic = P2.sinkTraffic)
where 
   p1.siteCodeID = @SiteId

What is the structure of your table and what indexes are defined? — Cristian Lupascu
– Cristian Lupascu, Commented Nov 6, 2012 at 7:45
Execution Plan is, select 0% Distinct 43%, nested loops cost 1%, clustered index scan 13% clustered index seek cost 43% — Shah
– Shah, Commented Nov 6, 2012 at 7:53
The definition of @SiteId is not included in the example script, but make sure that there isn't any implicit data type conversion happening too. Sometimes this can cause significant performance hits. — Alexander
– Alexander, Commented Nov 6, 2012 at 7:54

jenson-button-event · Accepted Answer · 2012-11-06 08:22:34Z

3

One option is to create a computed column and create an index on that

This article discusses it http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

ALTER TABLE dbo.portList ADD
traffic AS Convert(nvarchar,trafficSerial) +' ('+  sourceTraffic + ' - ' + sinkTraffic + ' )' PERSISTED
GO

CREATE NONCLUSTERED INDEX IX_portList_traffic
ON dbo.portList  (traffic)
GO

select distinct traffic from dbo.portList

You should also make sure each of the columns in your join relationships have indexes on them:

p1.trafficSerial & p2.trafficSerial
P1.sourceTraffic & P2.sourceTraffic
P1.sinkTraffic & P2.sinkTraffic

and the column for your filter: p1.siteCodeID

edited Nov 6, 2012 at 8:22

answered Nov 6, 2012 at 8:11

jenson-button-event

19k13 gold badges99 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tonci Korsano · Accepted Answer · 2012-11-08 15:16:09Z

this for surely help:

if you can remove distinct from your select statement this query will speed up quite a bit. many times, i handle distinct values over the client or web part of the system, like visual basic, php, c#, etc. please, remove distinct keyword and time again your query after executing it at least twice. however, if you cannot remove distinct then simply leave it there.
this is important: convert clustered index scan to clustered index seek or only index seek. that will speed up your query quite a bit. you acquire an index seek from an index scan by modifying the index. typically, a clustered index scan comes from a comparison on a column with a clustered index, which many times is a primary key. i suspect this column is portside.sitecodeid

best regards,

tonci korsano

Collectives™ on Stack Overflow

Improving SQL Server query performance

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related