Manipulating large tables - 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

General SQL Server Forums

Database Design and Application Architecture

Manipulating large tables

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-15 : 16:24:26

I'm in the midst of a long file conversion job. Today I found that one of the tables (converted from csv) to be 6.7 million records. My sql script which I use to reconfigure the weird original date format, into something the rest of the planet uses, times out due to the size.

Does anyone please know of a file utility to automagically split sql server 2005 tables for later re-combining once my scripts have successfully completed their task on the smaller tables?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-15 : 17:29:12

No. If you give us some more information, perhaps we can write something for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-16 : 06:36:15

Are there any indices on the table? Could you post your sql script?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-16 : 07:47:46

6.7 million is not that big. it should be possible to write a query that does the update in a reasonable time unless you are running this on ancient hardware.

elsasoft.org

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-16 : 09:03:45

The table contains no indices.

The script I'm using was kindly provided by jhocutt, from this forum, who responded to a previous thread I started regarding iterating through columns:



DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'business_table'
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ''19'' + ' + QUOTENAME(@col) + '
END

ALTER TABLE dbo.' + @tbl + ' 
ALTER COLUMN ' + QUOTENAME{@col) + ' datetime IS  NULL
'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

I also tried to get the script to change the column type to datetime once it had performed the re-formatting of the data (if any), but without success so far. Not sure why just now.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-16 : 10:58:32

aha. that is not a DML query, so indexes won't help much. you are actually altering the schema of the table, which requires page splits if the datatype you are altering the column to is wider than the original type.

i don't see why this would timeout though. if you run it in query analyzer or SSMS, it will just go until it completes. there is no timeout for queries you run from there.

also, you have a syntax error in one spot. QUOTENAME{@col) should be QUOTENAME(@col)

elsasoft.org

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-16 : 11:06:54

Thanks for the typo heads up Jezemine.

mdgryn
Starting Member

6 Posts

Posted - 2008-02-22 : 00:16:40

The simple reason is that dml will work on the query timeout configured on your server which might be very lesser you make it 0 that is default that never times out .
the other thing you can do is you can start a transaction and commit for every iteration which is correct and you can avoid the time out problem (since the batch of statements is not getting timed out it is that your dml is getting timed out if you commit every time it will never time out but try the first option and try the second option that never fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources