Inserting Records into SQL Server - is there a faster interface than ADO

Discussion in 'Python' started by geskerrett@hotmail.com, Nov 11, 2005.

  1. Guest

    I have a program that reads records from a binary file and loads them
    into an MS-SQL Server database. It is using a stored proc, passing the
    parameters.

    I am using pywin32 to create a connection object. Once the connection
    is open I simple pass the SQL formatted commands using
    cnx.Execute(sqlstring).

    My test examples;

    20,000 records using the ADO connection: 0:04:45:45

    If I setup the program to not send the record to the database - so all
    other variables and processes are constant, it simply just skips the
    cnx.Execute(sqlstring) step, then it takes only 0:00:25:78 to process
    thru the same number of trx.

    Obviously the times in my test are that , but I have a client that woud
    like to use this and has several million transactions to content with.

    So my questions is ....
    Is there a "faster" method I can use to connect to the SQL server ?
    Or does anyone have any "optimization" tips the can offer ?
     
    , Nov 11, 2005
    #1
    1. Advertising

  2. Jarek Zgoda Guest

    Re: Inserting Records into SQL Server - is there a faster interfacethan ADO

    napisa³(a):

    > Is there a "faster" method I can use to connect to the SQL server ?
    > Or does anyone have any "optimization" tips the can offer ?


    This has nothing with python, but the fastest way to load large amount
    of data to MS SQL Server database is DTS import from flat file.

    To spped up the things a bit, do not commit transaction after each row
    inserted -- commit whole batch.

    --
    Jarek Zgoda
    http://jpa.berlios.de/
     
    Jarek Zgoda, Nov 11, 2005
    #2
    1. Advertising

  3. Alan Kennedy Guest

    Re: Inserting Records into SQL Server - is there a faster interfacethan ADO

    []
    > I have a program that reads records from a binary file and loads them
    > into an MS-SQL Server database. It is using a stored proc, passing the
    > parameters.


    [snip]

    > So my questions is ....
    > Is there a "faster" method I can use to connect to the SQL server ?
    > Or does anyone have any "optimization" tips the can offer ?


    Is there a reason why you need to use a stored procedure?

    Do you need to process the data in some way in order to maintain
    referential integrity of the database?

    If the answer to both these questions is "no", then you can use the
    "bcp" (Bulk CoPy) utility to transfer data into SQLServer *very* quickly.

    http://msdn.microsoft.com/library/en-us/coprompt/cp_bcp_61et.asp
    http://www.sql-server-performance.com/bcp.asp

    thought-it-was-worth-mentioning-ly y'rs,

    --
    alan kennedy
    ------------------------------------------------------
    email alan: http://xhaus.com/contact/alan
     
    Alan Kennedy, Nov 11, 2005
    #3
  4. Re: Inserting Records into SQL Server - is there a faster interfacethanADO

    Alan Kennedy wrote:
    > []
    >
    >> I have a program that reads records from a binary file and loads them
    >> into an MS-SQL Server database. It is using a stored proc, passing the
    >> parameters.

    >
    >> So my questions is ....
    >> Is there a "faster" method I can use to connect to the SQL server ?
    >> Or does anyone have any "optimization" tips the can offer ?

    >
    > Is there a reason why you need to use a stored procedure?
    >
    > Do you need to process the data in some way in order to maintain
    > referential integrity of the database?
    >
    > If the answer to both these questions is "no", then you can use the
    > "bcp" (Bulk CoPy) utility to transfer data into SQLServer *very* quickly.
    >
    > http://msdn.microsoft.com/library/en-us/coprompt/cp_bcp_61et.asp
    > http://www.sql-server-performance.com/bcp.asp
    >
    > thought-it-was-worth-mentioning-ly y'rs,
    >

    If the answer to some of the earlier questions is "yes," I have
    found "bcp" can be a great tool to fill up a new table of data
    "on its way in." SQL can then move it to where it should really
    go with nice transaction-protected SQL, proper index-building
    and so on. After distributing the data, you can drop the table
    of pending data.

    I agree this is off-topic, but it is too close to my experience.

    --Scott David Daniels
     
    Scott David Daniels, Nov 11, 2005
    #4
  5. Guest

    The utility is designed to run in the background and maintain/update a
    parallel copy of a production system database. We are using the
    stored procedure to do a If Exist, update, else Insert processing for
    each record.

    The originating database is a series of keyed ISAM files. So we need
    to read each record, perform some simple data conversions and then
    update the SQL database. We are using Python to read the originating
    database and perform the record conversion and then posting the results
    back to SQL Server.

    We designed our utility to run a night so that the SQL server is up to
    date the next day and ready for reporting.

    Thanks for your tips on BCP. I will investigate further as it looks
    like it might be useful for the initial loading of the data and perhaps
    some changes to the our utility program to minimize the amount of data
    that needs to be read/processed.

    Geoff.
     
    , Nov 14, 2005
    #5
  6. Oren Tirosh Guest

    > We are using the stored procedure to do a If Exist, update, else Insert processing for
    > each record.


    Consider loading the data in batches into a temporary table and then
    use a single insert statement to insert new records and a single update
    statement to update existing ones. This way, you are not forcing the
    database to do it one by one and give it a chance to aggressively
    optimize your queries and update the indexes in bulk. You'd be
    surprized at the difference this can make!
     
    Oren Tirosh, Nov 14, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chumley the Walrus
    Replies:
    1
    Views:
    379
    Mark Rae
    Aug 12, 2004
  2. =?Utf-8?B?UCBvZiBEaHVtcA==?=

    Problem (Inserting records into sql server 2000 using asp.net)

    =?Utf-8?B?UCBvZiBEaHVtcA==?=, Aug 18, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    637
    =?Utf-8?B?UCBvZiBEaHVtcA==?=
    Aug 18, 2005
  3. steve
    Replies:
    17
    Views:
    701
    Mike Smith
    Sep 13, 2004
  4. Replies:
    0
    Views:
    323
  5. Kiran
    Replies:
    4
    Views:
    183
    Kiran
    Oct 14, 2003
Loading...

Share This Page