Most Efficient Approach

Question

Most Efficient Approach

Vishal Singh

SSCertifiable

Points: 5830
More actions
May 25, 2010 at 6:41 am

#387010

I have some flat file with around 1118546 lines of INSERT command.
This INSERT will be for 4 column in a table.
I can not do this with BCP as it is not just a plane value and doing it through any other way causing SQL.Memory timeout issue.
I am running with 4 GB RAM on SQL Server Enterprise 64-bit.
Would like to know what else could be the efficient approach for doing this.
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

Vishal in my experience, a text filefilled with INSERT INTO....commands is much much slower than using BULK INSERT or bcp to bring in a text doucment filled with raw data;

for example, Importing 40K lines of zip code data takes a second or two as raw data and BULK INSERT,, but runs for 3 minutes as INSERT INTO; a million line doc would probably hit the tipping point of the server and never complete if it was all INSERT INTO statments;

can you re-export your file to get it in some sort of delimited format? you can use the import wizard if it is quote delimited or something, or use a format file; but most definitely you can use BULK INSERT or bcp;

abandont he idea of using INSERT INTO statements for million row tables...it's not worth it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 2

Lowell (5/25/2010)
Vishal in my experience, a text filefilled with INSERT INTO....commands is much much slower than using BULK INSERT or bcp to bring in a text doucment filled with raw data;
for example, Importing 40K lines of zip code data takes a second or two as raw data and BULK INSERT,, but runs for 3 minutes as INSERT INTO; a million line doc would probably hit the tipping point of the server and never complete if it was all INSERT INTO statments;
can you re-export your file to get it in some sort of delimited format? you can use the import wizard if it is quote delimited or something, or use a format file; but most definitely you can use BULK INSERT or bcp;
abandont he idea of using INSERT INTO statements for million row tables...it's not worth it.

I completely agree with you since I had the similar experience. but the pain is that this was just a one time export file provided ad in order to have raw data, some manual work will be required which will be time consuming.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Lowell SSC Guru Points: 323504 More actions · Answer 3

ugg; one-time imports are sometimes the most time consuming...can we see a row or two of sample data? enough to make a format file so you could test a bcp script?

with a decent text editor, you could do a massive find-and-replace to the file with INSERT INTO statements, inserting GO statements every 100 lines or so, that would allow the file to be processed in batches to avoid the time out issue.

---edit----

actually, using editplus or notepad++ as an example, you'd create a macro to page down 5 or six times, jump to the end of line, insert a CrLf ,GO and another CrLf;

then you 'd script that macro to run, say 50K times;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 4

Lowell (5/25/2010)
ugg; one-time imports are sometimes the most time consuming...can we see a row or two of sample data? enough to make a format file so you could test a bcp script?
with a decent text editor, you could do a massive find-and-replace to the file with INSERT INTO statements, inserting GO statements every 100 lines or so, that would allow the file to be processed in batches to avoid the time out issue.

Hi Lowell, yes, if nothing will do then, I will have to do a massive find and replace. I am changing it to raw data the same way you suggested but all I was trying to avoid is this massive and manual thing.

This is how the sample data looks like:

INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,78628,4,1)

INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89059,4,1)

INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89230,4,1)

INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89235,4,1)

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Lowell SSC Guru Points: 323504 More actions · Answer 5

Vishal Singh (5/25/2010)
This is how the sample data looks like:
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,78628,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89059,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89230,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89235,4,1)

looks very easy to re-format; all four values appear to be integers, so no format file required

with Editplus Find and Replace INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES( and then find and repalce the ending close-parenthesis;

save the file, and use a bulk insert command like this:

BULK INSERT [tbl1] -- four columns inferred, right?([PrimaryID],[RelatedID],[RelationID],[PosID])

FROM 'c:\Export_o.txt' --filename?

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '',

FIRSTROW = 1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 6

yup did it after changing to Raw data and then directly importing it through import/export wizard. so far running so good.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 7

Finally I was able to import the file. Though I had to change that file manually but now its over. Thanks.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]