CODEWITHSUNDEEP
sql-server
Martin
Martin

Reputation: 3

Finetuning the SQL Query Performance

The following query takes about 3000ms to execute. I could not fine-tune this query to give a considerable performance edge.

declare @EndDate datetime;
declare @FromDate datetime;
set @EndDate = getdate();
set @FromDate = DATEADD(year,-1, @EndDate);

    SELECT [twc].ColumnId, [twc].ColumnName
    FROM [table1] twcs with(nolock)
        INNER JOIN [table2] twc with(nolock) ON [twc].ColumnId = [twcs].ColumnId 
        WHERE [twcs].[ColumnName] = 1 AND [twc].[CreateDate] between @fromdate and @enddate;

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

This is your query:

SELECT twc.ColumnId, twc.ColumnName
FROM table1 twcs with (nolock) INNER JOIN
     table2 twc with (nolock)
     ON twc.ColumnId = twcs.ColumnId 
WHERE twcs.ColumnName = 1 AND twc.CreateDate between @fromdate and @enddate;

I would suggest the following indexes such as the following:

  • table2(CreateDate, ColumnId)
  • table2(ColumnId, CreateDate)
  • table1(ColumnName, ColumnId)
  • table1(ColumnId, ColumnName)

Let the optimizer choose which to use.

Upvotes: 2

Lancejt
Lancejt

Reputation: 26

With a query as simple and straight forward as that, you're likely looking at an issue where the tables need a new index to speed the return rather than script optimization.

Upvotes: 1

Related Questions