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 |
|
iridescence
Starting Member
3 Posts |
 Posted - 2002-06-13 : 07:30:20
|
| hi, i have a database that logs details of telephone calls to 4 premium rate phone numbers.i would like to work out how many people call once, twice a week, etc. i just can't seem to work out what to do. i have the query below, but when i run it it takes about 5 minutes to complete, which chews the cpu for all that time. i'm sure i'm doing it wrong, can some1 pls, pls help... :o)here is my table design...intID int 4strCallID varchar 32datCallStart datetime 8datCallEnd datetime 8strDNIS varchar 8strCLID varchar 15intLoadPercentage int 4strOtherCallInfo1 varchar 15strOtherCallInfo2 varchar 15and here is the sql i managed to write...select count(strCLID) as intCLIDCount, strCLID from tblCallLog where datCallStart > dateadd (week,-1,getdate()) and strCLID in (select strCLID from tblCallLog) group by strCLID having count(strCLID) >= @intNumberOfCallsthnx in advance... |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-13 : 08:08:24
|
Aside from the fact that your column names referenced in your query do not match those you presented in your psuedo-ddl and all your column name and table names fall victom to the old Fortran habbit of typed prefixes .....quote:
What you are doing is trying to expose the physical storage choices in your logical data model all over again. And you are making your code hard to read. Try to read "Paris in the Spring," "nounParis prepIn artThe nounSpring" and see if the prefixes make it easier to understand; now imagine that was a 20 word sentence with subclauses.-Celko
....Aside from these things, your query looks pretty good. I don't see the need for you second condition in the where clause (strCLID in (select...)), although without proper ddl, its impossible for anyone but you to know what strCLID is. Usually, a correlated subquery will give you a slightly better subtree cost, so where exists (select 1 from tblCallLog where strCLID = OuterTable.strCLID) . . .Your performance problems are most likely largely index issues. What is the clustered index on the table? What are your nonclustered indexes?<O> |
 |
|
|
iridescence
Starting Member
3 Posts |
Posted - 2002-06-13 : 09:21:42
|
| i don't use indexes because i havent needed them int he past and because of the performance issues as created with re-indexing the table as there is so much data going in2 it all the time.as for the data type abbreviations i learned that of an old school programmer, n it seemed logical, guess its just habit. what u say makes sense thou. does no1 do that then?i have started doing it using a stored proc that fills a new table every day, with the phone numbers (strCLID) of every1 that has called in the past week and how many times they have called and am going 2 set it 2 run at about 3am (with agent). then i can query this table, and it should take a lot of the load off.DateStamp (the day)strCLID (the number)CountOfCallls (the count) u think this should work better???thnx... |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-13 : 09:35:55
|
I get a kick out of the fact that you have time to prefix your column and table names, but abbreviate and with 'n', into with 'in2', you with 'u', no one with 'no1', everyone with 'every1', to with '2' . . . it is like you are on a quest to make things hard to read ....  ....quote:
u think this should work better???
Well, no. I think you should index your table properly. How many page splits/sec do you get with the indexes that cause you to believe that the overhead of maintaning them is not worth the cost? What have you set the fillfactor too... Do you really think indexing is gonna take more resources than creating these new tables daily?I dunno, if this is the only SELECT statement you are ever going to run against the table then maybe you could create a covering index before the query and drop it afterwards. That may take less than 5 minutes. However, if this is only information you ever want to extract, I am curious why you don't just store it as such?<O> |
|
|
|
|
|
|
|
|
sql problem. must be m.better way of doin this