CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Greg
Greg

Reputation: 648

SQL query taking too long - help for better performance

I am running a stored procedure in which i have the following query

SELECT TOP 1 @TopValue = t2.myDecimal

from table1 t1, table2 t2

where t1.ID = t2.table1ID

and CONVERT( VARCHART(8), t1.Created, 112 ) = @stringYYYYMMDD

and t1.Term = @Label"

TopRate is Decimal(8,6) stringYYYYMMDD is a string representing a date in the format YYYYMMDD Label is a simple varchar(8)

This query is called for every row of my data set, that can be from 10 to 5000. If I comment this query, the procedure execution time is under 2 seconds. With the query included, it just never ends.

I am using Microsoft SQL Server management studio 2008 R2

Thank you for your help

Upvotes: 0

Views: 494

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

First, you should use explicit join syntax. Second, it is suspicious whenever you have a top without an order by. So, your query as I see it is:

select TOP 1 @TopValue = t2.myDecimal
from table1 t1 join
     table2 t2
     on t1.ID = t2.table1ID
where CONVERT( VARCHART(8), t1.Created, 112 ) = @stringYYYYMMDD and t1.Term = @Label"

You can speed this up with some indexes. But, before doing that, you want to change the date comparison:

where t1.Created >= convert(datetime, @stringYYYYMMDD, 112) and
      t1.Created < convert(datetime, @stringYYYYMMDD, 112) + 1 and
      t1.Term = @Label

Moving the function from the column to the constant makes the comparison "sargable", meaning that indexes can be used for it.

Next, create the indexes table1(Term, Created, Id) and table2(table1Id). These indexes should boost performance.

Upvotes: 5

Related Questions