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 |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2003-03-18 : 13:28:39
|
| I have two views that extract data from a log table ...user_logins and user_logouts It gives the username and the datetime of each action ... now I thought something likeSELECT i.[User], i.[DateTime], (SELECT MIN(o.[DateTime]) FROM user_logouts AS o WHERE o.[User] = i.[User] AND o.[DateTime] > i.[DateTime]) FROM user_logins AS iwhich should give me the date the login was recorded and the very next logout event for that user ... this takes forever 20+ mins to run on 20k rows of event data that is converted into about 2k rows in each view ... can anyone help speed it up? |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-03-19 : 04:02:06
|
| Why not have a single view using the log table, a opposed to a join ?you are doing the select min(o.[datetime] once for each row in the user_logins view - so It seems like you'll do 20K select's!How about the following:select SELECT i.[User], i.[DateTime], MIN(o.[DateTime])FROM user_logouts AS o,user_logins AS i where o.[User] = i.[User]and o.[DateTime] > i.[DateTime]Think that might be better ...(check this - I can't test it in my environment).*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
vganesh76
Yak Posting Veteran
64 Posts |
Posted - 2003-03-19 : 06:04:55
|
| Try this, u need to have an aggregate function when using min()SELECT i.[User], i.[DateTime], MIN(o.[DateTime]) FROM user_logouts AS o, user_logins AS i where o.[User] = i.[User] and o.[DateTime] > i.[DateTime] group by i.[User], i.[DateTime regardsV.Ganeshvganesh@netassetmgmt.comvganesh76@rediffmail.comEnjoy working |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-03-22 : 06:31:22
|
| DOh !!! smacks head!!Yes, you obviously need the group by. Good catch Ganesh*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
User Login Duration