| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-01-15 : 07:21:34
|
| SELECT ModuleSequence, ModuleTitle, Cast(avg(minutes) as decimal(7,2)) as [Average Minutes] FROM CourseVisitations CV INNER JOIN Courses C ON C.CourseID = CV.CourseID WHERE CV.CourseID = @CourseID group by ModuleSequence, ModuleTitle order by ModuleSequenceResults (showing time spent viewing each chapter)---------1 Chapter1 3.22 Chapter2 4.43 Chapter3 2.6This is an 'expensive' query, tracking through URL strings calculating differences to come up with average time spent per chapter in a course.I've examined CUBE, ROLLUP and COMPUTE but I haven't come up with a way to add a row that'll be a grand total at the bottom. It seems I'll have to UNION ALL with an identical query to get it. Will that double the overhead?Sam |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-15 : 07:31:08
|
Sam, here is an example of how you might use a ROLLUP with a multi-column group by. The trick is to throw out all the records where the grouping is not on all the columns.select case when grouping(zip) = 1 then 'Grand Total' else state end as state, case when grouping(zip) = 1 then '' else zip end as zip, count(*) as totalfrom pubs.dbo.authorsgroup by state, zip with rolluphaving (grouping(state) = 1 and grouping(zip) = 1) or (grouping(zip) = 0) If you include grouping(zip) and grouping(state) in your select list its easier to see what is going on.Jay White{0} |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-15 : 07:42:23
|
| Thanks Jay, (you're up early. What happened to late rising programmers?)Your proposition should solve it. I'd never seen GROUPING before.Here's another brain squeezer. Any comment you might have on an observation I made this morning...When running the above query at 6AM, it took almost a minute to run. No employees were taking the course then. Now, at 7:30 employees are taking the course, and adding urls at the rate of about 1 every 5 seconds. The query has groaned to a crawl. I'm wondering if the query is restarting every time a new URL is added to the table?If that's the case, this is a statistical total. It doesn't need to be exact. I'd like it to operate on a snapshot of the urls and return aresult.Sam |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-15 : 09:12:03
|
I'm at the client site by 6:30am every day ... And who are you calling a "programmer"?  I'm not sure I follow you meaning "the query is restarting every time a new URL is added to the table"? Of course, I don't know exactly how the optimizer is coded, but I would doubt that an insert would cause it to "throw away and start over". It is quite possible that the two SPIDS are competing for locks and other resources.If you don't need real-time data, I think reporting out of a seperate table is a good idea. Or you could look at some optimizer hints for you FROM clause. Most likely, the same performance hit your reporting query is seeing is proportional to the performance hit your insert processes are seeing.Jay White{0} |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-01-15 : 10:11:02
|
quote:
I'm wondering if the query is restarting every time a new URL is added to the table?
No, transactions are processed in an isolated manner. Inserting a row into a page that your query needs to read from will perhaps block it for short periods of time, but it will never "start over".Jonathan{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-15 : 12:00:09
|
| Here's a funky non-UNION non-ROLLUP way to get grand totals:select B + State as State, B + Zip as ZIP, count(*) as TotalFROM pubs.dbo.authorsCROSS JOIN (Select Null as B union select '') Agroup by B + State, B + Zip, BThis takes advantage of the fact that Null + 'abc' = Null. Could be useful if you don't use a SQL version that includes rollups.It actually doesn't perform much worse than the rollup, according to the query analyzer. On a different table in my database with 75,000 rows, it actually performed a little better!- Jeff |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-15 : 14:32:47
|
I just got back. Sorry for the late reply and thank you everyone for your comments.Jay - so I don't refer to your occupation incorrectly again, what do you like to be known as? (what a perfect setup to name your own title - I hope you appreciate this...)  as for the query performance problem, there are about 250,000 records (today) which should grow to about 2,000,000 records. Real time inserts (at the end, nothing complicated) slowing down the query - most likely for semaphore competition.This is an administrative query, (read - not executed often), would it be faster to copy the necessary data to a temp table and operate on it there?Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-15 : 14:41:50
|
| Jeff,I'm still trying to figure out your query. Give me a sec.Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-02-19 : 09:45:01
|
| Hi Jeff,I ran accross this thread again while I was looking for an old post. I had forgotten to get back to your suggestion. It's a great trick to derive a subtotal. I'll give it a try when I get a chance. It may save some coding steps for me and could result in performance gain. Either would be welcome.Sam |
|
|
|
|
What's the best way to Grand Total?