CODEWITHSUNDEEP
c#t-sqlquery-optimization
user996277
user996277

Reputation:

Optimize complicated query

I have a query that merge table by some id.

SELECT ROW_NUMBER() OVER (ORDER BY PagedOptionData1.ExternalId ASC,
                                   PagedOptionData1.RunDateTime DESC
                          ) AS RowNumber,
       PagedOptionData1.*,
       PagedOptionData2.*
FROM   PagedOptionData AS PagedOptionData1
       LEFT OUTER JOIN PagedOptionData AS PagedOptionData2
         ON PagedOptionData1.ExternalId = PagedOptionData2.ExternalId
            AND PagedOptionData2.rn = 2
WHERE  PagedOptionData1.rn = 1

Where PagedOptionData is a temp table with various join .

Can this query can be optimized ?

Upvotes: 3

Views: 82

Answers (2)

gbn
gbn

Reputation: 432261

You need an index on (ExternalId ASC, RunDateTime DESC) whether you use your original form or my suggestion

One thing stands out: how will you remove ambiguity in column names when you use SELECT * twice on the same table?

To avoid ordinal column access and simplify the query, I'd consider re-writing:

SELECT TOP 2
   *
FROM
   PagedOptionData
ORDER BY
   ExternalId ASC, RunDateTime DESC

This simplifies matters considerably at the expense of more logic in the client code

Upvotes: 4

Anders Abel
Anders Abel

Reputation: 69260

The query doesn't look very complicated, so if it is running slow the optimization should be done by adding indexes. Sql Server can suggest indexes to add.

  1. Paste the query into a new Sql Management Studio Window.
  2. Click the "Include Actual Execution Plan" button in the toolbar.
  3. Run the query.
  4. Check the returned Execution plan for an index suggestion (shown in green text right below the query).
  5. Create the index and retry.

Upvotes: 2

Related Questions