wood burning stoves 2.0*
The moose likes JDBC and the fly likes Expert knowledge needed: loops in sql queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Expert knowledge needed: loops in sql queries" Watch "Expert knowledge needed: loops in sql queries" New topic
Author

Expert knowledge needed: loops in sql queries

Kilia Verdon
Greenhorn

Joined: Jan 20, 2008
Posts: 13
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

Joined: May 26, 2003
Posts: 30598
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Kilia Verdon
Greenhorn

Joined: Jan 20, 2008
Posts: 13
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

Joined: May 26, 2003
Posts: 30598
    
154

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.
 
GeeCON Prague 2014
 
subject: Expert knowledge needed: loops in sql queries