CODEWITHSUNDEEP
sql-servert-sql
Shah
Shah

Reputation: 1654

Improving SQL Server query performance

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

Upvotes: 1

Views: 109

Answers (2)

Tonci Korsano
Tonci Korsano

Reputation: 26

this for surely help:

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

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

Upvotes: 0

jenson-button-event
jenson-button-event

Reputation: 18941

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

Upvotes: 3

Related Questions