Best Method To "Check Out" Records For A User

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best Method To "Check Out" Records For A User

Author

Topic

jshood
Starting Member

2 Posts

Posted - 2002-06-18 : 21:11:08

I am currently designing a database to manage a data mining operation. I'm building a SQL 7.0 DB with an MS Access 2000 front end. The database holds about 200,000 records at a time. The function of the database is to provide a front end where about 15 people can manually review the data in each record.

I need to build a set of queries that handle the process of handing out 500 records at a time to each user for them to review and modify in an access form. I've done this by creating a field called RecordCheckedOutBy in the data table and using the following queries:

First To Checkout the records to a user

UPDATE PARTS
SET RecordCheckedOutBy = Currentuser()
WHERE RecordNumberIn
(SELECT TOP 500 RecordNumber
FROM PARTS
WHERE RecordCheckedOutBy IS NULL
AND RecordReviewed IS NULL
)

Then To View the Records I Run A Simple Query Like

SELECT *
FROM Parts
WHERE RecordCheckedOutBy = CurrentUser()

This system does work well at making sure no two people are working on the same data. However as you can imagine it's incredibly slow as the DB server has to select all 200,000 records in the database in order to select the 500 to mark as checked out.

Any suggestions on how to improve this design would be greatly appreciated. I'm wondering if it would work better to have a table that tracks which records are currently checked out?

Also since this is my first post please let me know if I could have provided more / less information.

Thanks for any replies.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-18 : 21:26:54

jshood,

You have provided enough information except maybe the indexes you have on this table. After all it is speed that is the issue..

Perhaps you could use the "set rowcount" option.. Look it up on BOL

eg


SET ROWCOUNT 500
UPDATE PARTS SET RecordCheckedOutBy = Currentuser()
WHERE RecordCheckedOutBy IS NULL 
AND RecordReviewed IS NULL

DavidM

"SQL-3 is an abomination.."

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-19 : 07:07:11

SELECT *
FROM Parts
WHERE RecordCheckedOutBy = CurrentUser()

Do you really need ALL fields returned?

Also an index on RecordCheckedOutBy should help.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-19 : 13:51:10

quote:

Also an index on RecordCheckedOutBy should help.

Actually it probably won't since the RecordCheckedOutBy column shouldn't be very selective ( usually most of the rows will have a NULL RecordCheckedOutBy in a situation like this ).

500 rows is a lot to reserve at once. If business rules permit, you'd likely find much better speed with the following construct which reserves one row at a time:

declare @partID int
begin transaction
set @partID = (
select top 1 partID
from parts (updlock)
where RecordCheckedOutBy is null and RecordReviewed is null)

update parts
set recordsheckedoutby = currentuser()
where partID = @partID
commit

select *
from parts
where partID = @partID

setBasedIsTheTruepath
<O>

Edited by - setbasedisthetruepath on 06/19/2002 13:52:55

jshood
Starting Member

2 Posts

Posted - 2002-06-19 : 14:18:50

quote:

ColinM
Do you really need ALL fields returned?

Sorry I was getting lazy in my post, I only pull a few needed fields

quote:

500 rows is a lot to reserve at once. If business rules permit, you'd likely find much better speed with the following construct which reserves one row at a time:

Thanks for the idea. However it's really important for the process that a user can view a good screenfull of information.

I've also looked into using SET ROWCOUNT N instead of SELECT TOP N. I've found this microsoft article [url]http://www.microsoft.com/sql/techinfo/tips/development/May21.asp[/url] that leads me to believe it will not increase my performance much.

Thanks for all your continued ideas

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-19 : 17:01:38

With the ROWCOUNT vs TOP, remember this:

This works:
SET ROWCOUNT @RowsToReturn

This Doesn't:
SELECT TOP @RowsToReturn

If you have a static number to return, use TOP. If not, you must use ROWCOUNT.

Another good question is what are they doing with these 500 records once they modify them? After the record is modifed in Access, an UPDATE querie fires to the SQL Server, or are you doing some sort of batch update?

Michael

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-19 : 17:48:36

jshood,

What you read, and what actual work are 2 different things....

Benchmark them.. my pick is the SET ROWCOUNT option will be faster....

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources