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 |
|
gwellington
Starting Member
2 Posts |
 Posted - 2006-07-17 : 10:27:37
|
| Wondering if anyone has any ideas on how to engineer this...So I have a 92 million row table with a clustered index on the sessionID. Contained in the table is the sessionID, URLId and page load duration(the amount of time the page took to load)What I need to do is find a take the 50th, 75 and 95th percentile of page load durations for each URLId (936 of them). Trying to just use the ntile function blows up hash matches and tables spools into the quadrillions for the execution plan. Anyone have any ideas as to how to break this down into something manageable? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-17 : 14:59:51
|
| SELECT TOP 50 PERCENT ???Peter LarssonHelsingborg, Sweden |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-07-17 : 16:07:11
|
| An index on URLId+Load duration might help big time.No idea how long it would take to build one.I suggest you experiment first.rockmoose |
|
|
|
gwellington
Starting Member
2 Posts |
Posted - 2006-07-17 : 20:47:56
|
| http://sqljunkies.com/WebLog/sqlbi/archive/2006/04/19/20482.aspxI found that link which rewrites an ntile call using rank over an order by. I tailored that and then did a cursor step through for each URL Id. Surprisingly only takes about 20 mintues doing it that way vs. 16+ hours with an ntile function. |
|
|
|
|
|
|
Query Design...