Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2002
    Location
    UK
    Posts
    7

    Question Unanswered: How can I speed up my multiple INSERT's?

    I have a data gathering application written in MSVC++ 6 that uses ADO to insert large amounts of data into a table. Currently I have a stored procedure that inserts a single row at a time and I call it everytime I have more data to insert. However this can often fully load SQL Server - I often have 10's or 100's of inserts a second for short periods and load goes up to 100%...

    Does anyone know a way of making the inserts more effiicient without resorting to dynamic SQL?

    For example is there a way of batching up these inserts such as passing 10 at a time to the sp and inserting them all at once with an "insert into <table> select ..."?

    Or would modifying my C++ and wrapping a block of inserts to the single insert sp in a transaction help?

    A collegue suggested writing the data to a temporary text file then using bulk insert at regular intervals but that would then involve writing a file management system as well and seems to be a bit of a hack!

    Any help much appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Dynamic sql would likely make your inserts take longer and use more cpu because stored procedures run from a pre compiled execution plan put together by sql server to do the job in the most efficient way possible.

    One thing that could be slowing down your inserts are indexes on the table that were created for reporting purposes. I would check the indexes on the table and see if they are relevant and reasonable for any reporting that is taking place.

    I always reccomend seperating the decision support (reporting) databases from online transaction processing (data entry) databases and copying the data over as neccessary. If you find over indexing is the problem this may be the way to go.

    Barring this, you could insert into a staging table and then during off peak hours you could have a job that copies your staging table into the table you are having problems with.

    Why don't you post your insert sql and the data definition language for your table. There may be some issues with it.

    Have you looked at your execution plan in the query analyzer?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2002
    Location
    UK
    Posts
    7
    Thanks Thrasymachus,

    Scripts are :-

    CREATE TABLE [dbo].[VTLog] (
    [vTLogId] [int] IDENTITY (1, 1) NOT NULL ,
    [camId] [int] NOT NULL ,
    [timestampRx_Gmt] [datetime] NOT NULL ,
    [timestampLastTag_Gmt] [datetime] NOT NULL ,
    [tagCount] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[VTLog] WITH NOCHECK ADD
    CONSTRAINT [PK_VTLog] PRIMARY KEY CLUSTERED
    (
    [vTLogId]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[VTLog] ADD
    CONSTRAINT [FK_VTLog_Cams] FOREIGN KEY
    (
    [camId]
    ) REFERENCES [dbo].[Cams] (
    [camId]
    ) ON DELETE CASCADE ON UPDATE CASCADE
    GO

    CREATE procedure VTLogInsert
    @camId int,
    @timestampRx_Gmt datetime,
    @timestampLastTag_Gmt datetime,
    @tagCount int
    as
    set NOCOUNT on

    insert into VTLog
    (
    camId,
    timestampRx_Gmt,
    timestampLastTag_Gmt,
    tagCount
    )
    values (
    @camId,
    @timestampRx_Gmt,
    @timestampLastTag_Gmt,
    @tagCount
    )

    if (@@ERROR <> 0)
    goto OnError

    return 0 -- No error

    OnError:
    return -1 -- Error
    GO

    No, I haven't looked at the execution plan yet - I haven't done this before so I'll have a go!

    Would you recommend removing indexes (and hence keys / relationships from the online transaction processing (data entry) databases in this case (and hence also forgo integrity etc) to improve insert performance?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I suppose it depends on how much data you need to insert, and how quickly you need to insert it into SQL Server, but there's always BCP. BCP has an API interface that allows you to move data from a client to a server at 40-60 times faster than using INSERT statements.

    This is not for the faint of heart, but it can greatly ease the load your application puts on the server while the application is loading data.

    -PatP

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    While BCP and BULK INSERT are fast, I got the feeling he did'nt want to go the route of creating a text file to be imported from his original post.

    Dropping the indexes from the OLTP database, does'nt mean you have to give up your primary and foriegn key constraints, but yes dropping some indexes from your OLTP would increase performance on inserts and updates.

    Nothing jumps out at me from the code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Thrasymachus
    While BCP and BULK INSERT are fast, I got the feeling he did'nt want to go the route of creating a text file to be imported from his original post.
    Exactly. That's why I suggested using the API instead of resorting to using the BCP.EXE. The executable can only give about a 10x increase anyway, the API is much faster.

    -PatP

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    BCP/BULK INSERT are merely wrappers that call the same API. Where did you see that BCP is 4 to 6 times slower than an API call?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    BCP/BULK INSERT are merely wrappers that call the same API. Where did you see that BCP is 4 to 6 times slower than an API call?
    Yep. The difference was timed using live tests with real data.

    By the time you convert data to ASCII, write it to disk, fire up BCP.EXE, read the file back into memory, then convert it back to binary you've wasted a relatively large amount of time... Simply buffering the data, then calling the BCP API directly yielded an improvement of 4-6 times depending on the data being transferred (char was around 4x, float was more like 6x faster than using BCP).

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Have you tried BULK INSERT? It IS faster than BCP (according to MS), and uses EXACTLY the same API call as ImportData method of Table object.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that calling the BCP API directly on the client from the VC program will be faster than converting the data to text, writing it to a file, executing the BULK INSERT, which will read the data from the file, convert it back to binary, then call the same BCP API on the server.

    At this point I don't have time to test it, but getting rid of two complete sets of file I/O (writing the data to a file then reading it back, at least one of them probably via the network), conversion to text and back, and the other overhead associated with those steps seems like it would be a good idea to me.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think so, because client will have to do it over the network as well (1) vs. if it's already on the server (should be). And where did you come up with the idea that it gets converted to binary????
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    I don't think so, because client will have to do it over the network as well (1) vs. if it's already on the server (should be). And where did you come up with the idea that it gets converted to binary????
    I got the idea for the binary conversion from bcp_bind. The data needs to be in binary form to be bound, before you can use bcp_sendrow to send it to the SQL Server.

    If the data starts on the client, is has to pass over the network at least once to get to the SQL Server. Using the BCP API instead of writing to a file, then having either BULK INSERT or BCP.EXE read from that file gets a lot of moving pieces out of the way.

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think you need to perform your tests again. And from your reference site I see the only conversion mentioned:

    If the bound SQL Server column is wide character, no conversion is performed on bcp_sendrow. If the SQL Server column is an MBCS character type, wide character to multibyte character conversion is performed as the data is sent to the SQL Server.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jan 2002
    Location
    UK
    Posts
    7
    Thanks everyone for all your suggestions and lively debate!

    Quote Originally Posted by Thrasymachus
    Dropping the indexes from the OLTP database, does'nt mean you have to give up your primary and foriegn key constraints, but yes dropping some indexes from your OLTP would increase performance on inserts and updates.
    I don't think I have any scope to drop indexes from the VTLog table because the only index I have is on the primary key which I thought was enforced by SQL Server... Consequently, until I have finalised the reporting required on this table, having a staging / OTP table for data entry presumaeably won't help as it will be identical in structure to the reporting table.

    Quote Originally Posted by Pat Phelan
    BCP has an API interface that allows you to move data from a client to a server at 40-60 times faster than using INSERT statements.
    I would like to investigate the BCP API as I don't want to get involved with writing a file management system for the text files required by BULK INSERT et al. Please can you point me in the right direction for getting started with the BCP API - documentation / example code?

    BTW, Data is passed to the C++ app over a network via DCOM and then inserted into a local SQL Server db. I therefore don't have any network overhead when actaully passing data from the C++ app to the local SQL server db...

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just follow either of the links (to bcp_bind or bcp_sendrow) in my previous posting. You'll need to hunt about just a bit to get all of the details, but there are links to everything you should need on those two pages. Be especially careful to check out SQLSetConnectAttr, because if you don't enable BCP before you make the connection, you can burn many hours trying to figure out why nothing involving the BCP API works!

    I'm really cramped on time right now, so I can't offer you a lot of help, but if you have specific questions I'd be happy to answer them. The BCP API isn't as well documented as I'd like, especially for the newcommer. It sounds to me like you've got a good handle on the concepts, but there are lots of little holes that you can stick your foot into in the API, and you can run around in circles for a long time trying to figure out why you aren't making any progress, when the real problem is that you've got one foot anchored in a hole somewhere!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •