Timeout Updating Index Field

[rjbalicki]

I have a table that stores userid, sessionid, and logintime. I have indexes (neither clustered) on userid and sessionid.

The query I'm running is to clear out expired sessions (through a loop in a program):

update logins set sessionid = null, logintime = null where userid = 9999

The query times out. I took the query and ran it in SQL Analyzer and let it run for 6 minutes before killing it.

Do you think the issue is that I'm updating an index field, and that's why it's taking so long? It is a very dynamic field - constantly beign updated throughout the day.

Any suggestions would be helpful, thanks!

 

[hmckillop]

How many rows has the table?

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[rjbalicki]

There are roughly 60,000 records total.

 

[ESquared]

Drop the indexes and see if it makes a difference! If it does, you might consider dropping and recreating the indexes through script, before and after doing the update.

Also, since your data is so dynamic, consider not using the indexes at all. Indexes are double-edged: they speed up access, but they slow down updates.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[rjbalicki]

Ok, I figured it out. I did some watching of the processes when this situation occurs and found that the select statement that I was using to determine which records to update was actually blocking the update statement.

Does a select statement really lock records?

In any event, I changed my code to retrieve all records from the select into an array and I process the loop like that now.

 

[hmckillop]

If there in the same transaction or proc the locks taken out in the select statement should be held on the same spid and objects thus allowing the update statement to update the rows.
If you are selecting rows to update and then manually processing could you not do it all in one go using joins in the Update statement, or the where clause?

UPDATE logins 
FROM MyOthertable
WHERE MyOthertable.userid = logins.userid 
AND --Other conditions used in the select

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[netw3rker]

sounds like you have this figured out, but .. out of curiosity, why are you storing the session id in the DB? and if the reason for the storing the sessionid is valid, why are you indexing against it, and even more so, why are you storing it in the table that has all the non logged in users?

under the assumption that you are storing/indexing the sessionid to allow your app to link users to sessions for the purpose of doing something to the extent of "x users are online" or using it to bridge 2 users together, wouldnt it be better to isolate that portion into another smaller table and work with that?

for example:

usersTable(userid, username, etc) (60,000 rows)

usersLoggedInTable (userid, sessionid, otherInfo) (600 rows)

that would make the seek & modify times signifigantly smaller since you are only working with a much smaller set of data...

my other thought would be if possible dont store the sessionID's in the db at all, and just use the various language's session factory.. i just recently found out about Cold Fusions's which is largely undocumented, and i'm pretty sure that various languages like PHP and ASP have the same things, and can probably help speed things up even more..


just a thought or two for ya.
-Chris

 

[rjbalicki]

Thanks for all the great feedback!

I suppose I could do what I need to do with the data and then, at the end of it all, run a batch update. I may just do that.

This code is, for the most part, inherited. It's written in ASP and, to my knowledge, ASP sessions require cookies (not that I'd be controlling them at all, they're automatic). I was explicitly told that they don't use and won't be willing to use cookies when I inherited this project.