| Author |
Topic |
|
kooljava
Starting Member
1 Post |
 Posted - 2008-08-20 : 13:13:38
|
| Hello,I am new to SQL server. I am running this query against my DB and its taking > 4 min to complete. I did research on web and apart for creating index I don't see better solution. I want to know if I can improve this query.DECLARE @todaysDate CHAR(10), @today_2 CHAR(10)select @todaysDate = convert(CHAR(10), GETDATE(), 101) , @today_2 = convert(varchar, (GETDATE()-1), 101)select DATENAME(hour,t1.Created_Date) "Hour", name , count(*) , SUM(CASE WHEN CODE > 0 THEN 1 ELSE 0 END), ROUND(avg(cast(datediff(millisecond, tl.column3, tl.column4))/1000.0,2) "Average in seconds"from table1 tl WITH(NOLOCK), table2 t2 WITH(NOLOCK) where t1.column1 = t2.column1and t1.Created_Date >= @today_2 and t1.Created_Date <= @todaysDateand t1.column6 = 'name'group by DATENAME(hour,t1.Created_Date),nameThank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kooljava20
Starting Member
3 Posts |
Posted - 2008-08-20 : 14:25:46
|
| - table1 has around 1 million rows- table2 has around 200K rows- the result returned 38205 rows- both the table have clustered index on the primary key |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-20 : 14:28:35
|
| Look like you need non-clustered index on t1.created_date. Look high cost in execution plan. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kooljava20
Starting Member
3 Posts |
Posted - 2008-08-20 : 15:22:05
|
| I will be running this query just once daily. Is it a good idea to create index for this.Thank you for your response. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-20 : 15:31:55
|
| Yes! You definitely need an index on the joining column. If one is a PK and the other is a FK, then add an index on the FK as you don't automatically get one like you do with PKs. You also need an index on Created_Date.If you could provide the DDL for both tables and the actual query, we'd be able to tell you exactly what to index and even the queries to add them.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
kooljava20
Starting Member
3 Posts |
Posted - 2008-08-20 : 16:42:28
|
| Hello Tara. here is what you requested |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
query efficiency