October 24, 2006 at 2:27 pm
Hey All.
I maintain a fairly large intranet web application that is written in asp and runs with a SQL Server 2000 database behind it.
I've optimised the application to use stored procedures where I can, except due to the nature of the application, i've had to use dynamic sql in one place.
The idea behind the application is that users get a record to deal with, it is locked (by way of an update) so noone else can get that record, they deal to it (call the customer or whatever) and then the data is updated, the record is unlocked and they are forwarded to a new page by means of a response.redirect (sends a http 302 which means the browser requests the new page)
The record selection was working on a random basis, meaning from the "pool" of available records, a random record would be returned to the user (this in itself was difficult to write in sql-server but it's working now).
However the client some time ago asked me to make it return rows sequentially based on a sort order..
I'm sure you all can see where i'm going with this now.
Here is the following scenario that is causing me a problem:
Users get a recordid from next record page, redirected to action page.
User deals to a record on action page
User sets update details, HTTP Posts to server.
Server updates record by means of a adodb.connection.execute
Server redirects user to "next record page"
User requests next record page
Server locates a new ID and redirects user to action page with that ID
In my scenario, the update is changing the position of the row in the sequential order, ie: for example, it is sorted by lasteditdate asc and the update is setting lasteditdate to getdate() (pushing the record to the bottom of the pile).
The problem I am getting is that when the user updates a record, and moves to the next record page, they get the same record back that they just had, ie: it hasn't moved down the sequence.
I've verified the sequence code is working, the problem is intermittant (yeah tell me about it, it sometimes works, sometimes doesnt) and everything else seems sweet.
I wonder if SQL server is prioritising selects over updates, ie: the update that moves the row down the order is processed after the select that gets the next row.
I don't think locking hints are going to help here because I'm hanging on a thread for performance here and I don't want to put unnessecary locks in place.
There are no locking hints being used currently.
In my testing, the only scenario I can see is that the update hasn't been actioned, the row hasn't been updated when the select takes place.
From what I remember, adodb.connection.execute calls are *not* asynchronous, which means sql server must be returning "ok" to the client (the web server) so it can move on.
When I have tested and introduced a few seconds delay between the save and the "next record request" it's worked flawlessly but when the users bounce from update to select, they get the same record again.
I've read up about locking and couldn't see an easy solution.
For now i'm returning 50 rows to the server and using a rnd()*50 in the asp page to grab a random id from the 50 possibles but this doesn't meet the sequential requirements.
Hope someone understands my question, it's clear in my mind! hah!
Gareth
October 25, 2006 at 1:12 am
What is your primary key?
Which column are you using to select a row? Can't you make sure your next select gets a different row? Using a date value seems like a bad idea, especially if it's a value you are updating, although if the date column is "date of last contact", I could see it making some sense. Then you might want to exclude recent contacts (eg. WHERE date_of_last_contact < getdate()-90 ) in your record selection process. Iterating through the table by an identity column should be easy:
SELECT TOP 1 *
FROM table
WHERE id > @id_of_record_last_completed_by_user
You might save that ID in the session when the record is updated.
Do you have a status column? I would create one, and set the record to "processed" once the user has done the work they need to do on it. List all your states and rotate the status through the states, eg. Created, Open, Called, Closed. You might also try creating separate datetime columns for each of these states.
As for the first, randomly selected record, if you have an identity value I would think you could just select the first row greater than or equal to a random number in the rand 1 .. Max(ID). But I got 3.3% duplicates when I tried that method (on a table with about 10% "empty keyspace" from deleted records, so I can see that could be a problem. It would be better to select an exact ID (=), then retry with a new random number if none were found.
drop
table rand_rows
GO
create
table rand_rows (id int)
GO
declare
@rows INT
SET
@rows=0
while
@rows < 100000
BEGIN
insert rand_rows(id)
select top 1 rec_id from [my_table]
where rec_id = CAST(rand()*1000000 AS INT)
SET @rows=@rows+1
END
GO
select
id, count(*) from rand_rows
group
by id
having
count(*)>1
...
(2885 row(s) affected)
Hmm, I still get 4.77%+ repeats. Maybe rand() isn't very good. (Given the poor quality of the hash function used by BINARY_CHECKSUM, I'm not surprised if so.)
select
(select 100.*sum(dups) from (
select
id, count(*)-1 dups from rand_rows
group
by id
having
count(*)>1 ) as x)
/ (select count(*) from rand_rows)
---------------------------------------
4.77025325534
Maybe it would be better to shuffle the rows first, then select from them sequentially, if this can work with your application. Eg. assign a random number to each row, then index by that number, then iterate through those values in sequential order, using another table to keep track of the key balue of the last row selected for processing. But as long as you update the state at the right time, you should not have a problem with collisions.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply