Table maintenance - SQL Server Forums

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)

Table maintenance

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-15 : 08:39:18

Russell writes "Our organization uses OPTIX archiving software for capturing digital images of paper documents and converting them into digital media. It also has an SQL backend (in windows 2000 mssql 7) to assist in searching for specific data elements of each scanned image.
Optix uses a row id to identify each record in a given table. Each table in the Database has a row id that makes each row unique. When I import data into the table via MS SQL Enterprise manager it does not add this row id. I have to resequence the entire data table to give them number because I don’t have a better way at the moment. One of the data tables has 97000 records in the table.
When I run my SQL statement to sequence after importing it take an over 24 hours. The server does not seem to bottle necked… I need help to figure out a faster better way to sequence this table with the new row id.

declare @currrow char(19)
declare @counter int
declare @numrows int
declare @numrows2 char(19)

declare x cursor for select count(*) from AP_Dept1113
open x
fetch x into @numrows
close x

select @numrows2 = convert( char(19), @numrows )

declare stamper cursor for select o_rowid from AP_Dept1113 for update of o_rowid

select @counter = 1
open stamper

while (@numrows > 0)
begin

fetch stamper into @currrow

print @currrow

update AP_Dept1113 set o_rowid = convert ( char(10), @counter) where current of stamper

select @counter = @counter + 1

select @numrows - 1

end"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-09-15 : 10:40:04

Hi there,

You're doing far too much work here. No need for a cursor!! That's what's killing the performance.

A couple of questions first,

1) Do you resequence the entire table after every bulk insert? I am assuming that you insert your data in to a table that already contains image records?

A solution would be a to create a single column table which would hold the maximum ROWID that is being using in your table. We will call it ROWIDTABLE

As an example, consider this : You have a source table A and a target table B. Table B already contains 1000 records with ROWID 1-1000.
Your next insert should start at 1001.

Your single column table should contain the maximum ROWID from table B.


declare @MaxROWID int
set @MaxRowID = (select currentROWID from ROWIDTABLE)

select identity(int, 1, 1) as _ROWID, * into ##TableA
from TableA

insert into Table B(.....)
select _ROWID + MaxROWID, *
from ##TableA

update ROWIDTABLE
set currentROWID = (select max(ROWID) from TableB)

drop table ##TableA

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources