CODEWITHSUNDEEP
sqlsql-serverperformance
Masoud
Masoud

Reputation: 8211

Different execution time for same query - SQL Server 2008 R2

I run this query in SqlServer 2008 R2, it take about 6 seconds and return around 8000 records. OrderItemView is a view and DocumentStationHistory is a table.

SELECT  o.Number, dsh.DateSend AS Expr1
FROM    OrderItemView AS o INNER JOIN
        DocumentStationHistory AS dsh ON dsh.DocumentStationHistoryId =
            (SELECT TOP (1) DocumentStationHistoryId
             FROM         DocumentStationHistory AS  dsh2
             WHERE     (o.DocumentStationId = ToStationId) AND 
             (DocumentId =   o.id)
             ORDER BY DateSend DESC)
WHERE     (o.DocumentStationId = 10)

But when I run the same query with o.DocumentStationId = 8 where clause, it return around 200 records but take about 90 seconds!

Is there any idea that where is the problem?

Upvotes: 0

Views: 1606

Answers (3)

Masoud
Masoud

Reputation: 8211

I rebuilt the index on o.DocumentStationId and the problem solved.

Upvotes: 1

Rohit Chaudhari
Rohit Chaudhari

Reputation: 757

Try the following query. Also check if there is any index on DocumentStationId, ToStationId and DocumentId. if not create them 

SELECT o.Number, dsh.DateSend AS Expr1
FROM OrderItemView AS o 
OUTER APPLY
    (SELECT TOP (1) DateSend 
        FROM DocumentStationHistory
        WHERE (o.DocumentStationId = ToStationId) AND (DocumentId = o.id) 
        ORDER BY DateSend DESC) AS dsh
WHERE (o.DocumentStationId = 10)

Upvotes: 0

Naveen Babu
Naveen Babu

Reputation: 1584

I suppose the index is the issue, But not for o.DocumentStationId but all the fields that are joined using the field o.DocumentStationId.

try to see how your inner query is working by checking the execution plan. that would need some performance tuning.

Also, try using index for ToStationId and DateSend. also see if you can modify inner query.

Other than these i dont see any suggestions.

Also post you execution plan

Upvotes: 1

Related Questions