• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Expert knowledge needed: loops in sql queries

 
Kilia Verdon
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello everybody,

I have a problem concerning "loops in sql queries" and would like to explain it on a short simplified example.

there is one table containing website clicks with the following columns (Microsoft SQL):
- UserID for every user
- Kind of ressource (information, ...)
- Click-Time (Exact timestamp concerning the click, in seconds)




i am interested in clickstreams per "user" AND "per kind of ressource".
i want to perform an sql query for every userid AND (combined) kind of ressource. first, i want to identify for every the "beginning" (code 1), the inside (code 2) and the end (code 3) of a clickstream. a clickstream begins if the user didn't click (in the specific kind of ressource) for more than 5 minutes.
Second, i want to give a unique number for every clickstream.

final table should look like this:



the pseudocode looks probably like this:



i wrote the code in java with two loops - the algorithm inside the two loops is not as easy and needs a lot of comparisons, but the program works as it should. BUT because the table has more than 120 000 entities, it took about 8 hours to perform the task. this is much more time than i will have for such an operation in future). If i apply queries directly in ms sql server it takes only seconds for complicated operations).

Thus my question -
1. how should such an operation be performed?
2. Are there any possibilites to do it directly in ms sql server (two loops, one nested in the other one)?
3. Are there any tools?

I can't imagine that my solution is the fastest one (there are millions or rather billions of comparisons).


(The example is not exactly as my project is, thus don't ask about the logic of the table structure...)


it would be very nice if someone could help me....
Thanks a lot
kilia
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kilia,
Are you allowed to use a database specific feature? If so, a user defined function could help. That would let you use joins for the loops and the user defined function for the specific algorithm part.
 
Kilia Verdon
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello Jeanne

thanks for your answer.
yes i am allowed to do any operation on the server because it is running on my computer

could you please explain me in more detail what you mean by doing joins as a first step. how can i simulate the for-each loop by joins?


thanks
kilia
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kilia,
I missed the part where you said everything was in one table. So you don't need the joins, but the user defined function still applies.

select user_id, resource, to_time(clicktime), xxx(user_id, resource), yyy(user_id, resource)


You would replace to_time with the appropriate conversion function for SQL Server (which I haven't used) and xxx/yyy with your user defined functions.

Note that this may or may not be faster than your original approach. Especially since the database is on your computer and therefore you don't have to worry about network traffic.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic