Setting a bit field for a large number of records

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

Transact-SQL (2000)

Setting a bit field for a large number of records

Author

Topic

despy
Starting Member

9 Posts

Posted - 2004-03-10 : 06:48:02

Hi,

I'd appreciate hearing if anybody here has had to do a similar thing and how you've got about it.

Basically, I've got a table with around 1 million rows in. Each row has an integer primary key with a clustered index on that field. It also has a bit field to mark its current status. So the table looks like:

   ItemID (PK Clustered)  ItemDescription (text)  Marked (bit)

At some point in my application I need to go through and set the bit field for certain records, so for example rows 1, 3, 7, 10, 15 and 20 all need to have the bit field set. This can be up to as many as 10,000 individual rows.

The problem I'm having is getting any stored procedure fast enough to do this. On any reasonable number of rows its take an age. I'm happy with a stored procedure which either does this individually or accepts a number of ItemIDs at once.

Any suggestions?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-10 : 07:02:57

This should be fairly trivial unless you are using cursors

. What's your existing code like? Post your code here and we might be able to suggest something.

OS

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-10 : 07:06:53

Well, what does your update code look like?

I can't help myself, but I think having BIT columns is a waste of storage. One BIT column takes, well who guessed, one bit leaving the remaining 7 bits unused in that byte.
I'd rather choose the TINYINT datatype instead.
But that's just me...

--Frank
http://www.insidesql.de

despy
Starting Member

9 Posts

Posted - 2004-03-10 : 08:17:55

I've tried a couple of different approaches. The first was a single stored procedure which accepted an ItemID and did it individually. This was called many times over from the application (VB.NET)


CREATE PROCEDURE ItemReIndexSave
	@ItemID		int,
	@ReIndex	bit

AS
	UPDATE	Item
	SET	ReIndex = @ReIndex
	WHERE	ItemID = @ItemID

For 100 items this takes around 20 seconds.

The other way I went for was passing in all of the ItemIDs in a comma seperated string and then adding that into a dynamic SQL string. I haven't got the code handy for this one but it would be similar to


CREATE PROCEDURE ItemReindex
    @ItemIDs     varchar(7000)
AS

    DECLARE      @SQLString(8000)

    SELECT       @SQLString = 'UPDATE    Item
                               SET       ReIndex = 1
                               WHERE     ItemID IN
                                         ( ' + 
                                         @ItemIDs + 
                                         ')'

    EXEC        (@SQLString)

Again this was taking a little too long. Like I say, I can do plenty in the VB.NET code to manipulate the parameters into a format that's going to make the SQL speedy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-10 : 08:26:52

Take a look at:

http://www.sqlteam.com/searchresults.asp?SearchTerms=csv

Pass the IDs as a CSV, use of those techniques to parse them into a temp table, then join the temp table to the original table. That way you won't need any dynamic SQL.

despy
Starting Member

9 Posts

Posted - 2004-03-10 : 10:14:05

Thanks for the pointer - I went for this route in the end (http://www.sqlteam.com/item.asp?ItemID=11499) of adding a CSV function and then changing the SP to update the rows where IN the results of the function. Works amazingly quick now. I've also been able to use it elsewhere to speed up other code.

Thanks for your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources