Post reply

Big Data Load using INSERT INTO

  • September 11, 2009 at 3:46 pm

    #209860

    Supposed to get / load approximately 17GB of data every day.

    I am doing test loading and it does not load more than 2.8 MB/minute.

    Unfortunately the input comes using INSERT INTO Statements and i have

    no control over it.

    I tried osql and benchmarked it

    osql -n -iSQLFILENAME.sql -E

    Takes 45 seconds to load 25000 rows (approx 2.MB of data).

    Is there any better / faster way to load? This is FULL recovery with Replication enabled

    running every 5 minutes and with One Primary key , Two indexes. I am not disabling the

    indexes since i cannot afford to do it in the Tera byte database in production. This job

    is supposed to run every one hour.

    With sqlcmd -iSQLFILENAME.sql takes 28 seconds to load the same 25000 rows. But this one errors out

    when i try to load a 180MB size of sql with 8+million rows.

    HResult 0xE9, Level 16, State 1

    Shared Memory Provider: No process is on the other end of the pipe.

    SQLcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

    The same error happens if i use Powershell too.

    Any useful suggestions?

    Thanks in advance.

  • September 11, 2009 at 5:18 pm

    #1052616

    hi,

    give us more information - what feature do you use to import data ?

    what SQLFILENAME.sql contains ?

  • September 11, 2009 at 8:01 pm

    #1052621

    Why aren't you using Bulk Insert? there's lots of performance examples on the web about people loading gigs of data; I've inserted a 13 gig raw text file into a table myself in a bit over half an hour on a dev server.

    bulk insert / bcp is at least an order of magnitude faster than parsing INSERT INTO scripts.

    I'm sure if you switch to bulk insert you'll be much happier.

    you might need to write a program that, ironically, converts your insert into statements into a comma delimited file, and then import THAT via Bulk Insert to achieve a decent throughput.

    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!

  • September 14, 2009 at 5:56 am

    #1053114

    Check FAST BCP...

    http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=108

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

Viewing 4 posts - 1 through 4 (of 4 total)

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