Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-12-22 : 08:30:24
|
| Parimal writes "I have a table with 650000+ rows having detailed rows, header table contains 55000+ row, when I try to execute a query wherein i want to get the sum(some_value_field) group by certail fields and using INNER JOINS of almost 5 tables, it gives me a "TimeOut expired" errorHope, you must already have the solution to the above.Given below is the query used:select b.sp, a.cocode, d.coname, a.cd, sum(a.itval) from adtsbl a INNER JOIN adtmbl b on a.billno = b.billno INNER JOIN admtco d ON a.cocode = d.cocode where b.billdt between convert(datetime,'01/04/2003',103) andconvert(datetime,'30/11/2003',103)group by a.sp, a.cocode, d.coname, a.cdorder by a.sp, a.cocode, d.coname, a.cd" |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-12-22 : 08:53:12
|
My guess is your CONVERTs are taking up too much time - casts across datatypes are always expensive. You can use the yyyymmdd format for dates, they are always unambiguous. Try this:WHERE b.billdt BETWEEN 20030401 AND 20031103 OwaisWe make a living out of what we get, but we make a life out of what we give. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-22 : 12:01:40
|
| actually, that shouldn't be a problem. the convert is done once, not for each row, so it should be OK.Do you have an index on the billdt column? My guess is that you don't. How about all the other columns you are joining on? (i.e., billno and cocode)? Make sure all of those columns are indexed and you should have no trouble with 650,000 rows ...- Jeff |
|
|
|
|
|
|
|
|
"SQL queries time out expired"