SQL Server Performance Forum – Threads Archive
Table design for faster import from a text file
If you were supposed to import from a text file into a temp table, would you design the temp table as below OR a table with all columns fixed CHAR columns? Which one the import process would be faster?[tblTEMP]
ID INT
Col1 VarChar(12)
Col2 VarChar(50)
Col3 Float
Col4 Int
Col5 VarChar(20)
Col6 Float
CanadaDBA
I guess: fixed lenght CHAR columns will be faster, but only a testing can prove it. Deepak Kumar –An eye for an eye and everyone shall be blind
I can’t help myself, but i think you won’t notice any large difference here. The only effect might be some additional storage space (some extra bytes per row). I haven’t got Inside SQL Server at hand right now, but IIRC if you don’t explicitely create a table in the order fixed column length, variable column lengths (not nullable), variable column length (nullable), the storage engine needs these additional bytes to keep track of the orignal order, because it reorders internally the column in this order anyway.
So, a table like
Col3 Float
Col6 Float
Col4 Int
Col1 VarChar(12)
Col2 VarChar(50)
Col5 VarChar(20) might help save some space, but I don’t think it will be significantly faster. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
So how can I boost the import? It takes about 1 hour and 30 or even 50 minutes for 300,000 rows to import from text file into the table. CanadaDBA
??? Are you serious? Have you tried dropping all the indexes? What about bcp? When you’re doing the import, what do the physical disk/write queue length counters look like? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hmm – maybe the logging of the operations is hurting…
Perhaps, depending on the use of the database and the table, you might consider doing a backup, set recovery model to simple, do import, the do another backup, the set recovery model to full. Panic, Chaos, Disorder … my work here is done –unknown
How do you import the text file? INSERTs, bcp, DTS or BULK INSERT.
See if this interesting case study helps:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
How do you import the text file?
The developer has used DTS for imports.
CanadaDBA
I didn’t know the SQL Server logs the DTS import. Do you think your suggestion can improve the import?
quote:Originally posted by SQL_Guess
Hmm – maybe the logging of the operations is hurting…
Perhaps, depending on the use of the database and the table, you might consider doing a backup, set recovery model to simple, do import, the do another backup, the set recovery model to full.
Perhaps, depending on the use of the database and the table, you might consider doing a backup, set recovery model to simple, do import, the do another backup, the set recovery model to full.
CanadaDBA
I haven’t tried the bcp. Actually, I didn’t know it is different than DTS. The import kicks off when there is no user connected. It happens very early in the morning. There is no index on the table except a PK and the DTS truncates the table and drops the PK before import and adds it after import is complete.
quote:Originally posted by derrickleggett
??? Are you serious? Have you tried dropping all the indexes? What about bcp? When you’re doing the import, what do the physical disk/write queue length counters look like?
CanadaDBA
]]>