Performance Inserting Multiple Records -

Posted on 2008-11-06
Last Modified: 2012-06-27

I have a application that is reading information from a web service and inserting this information to another database using ODBC connection.

Right now, everything works great, however, I am wondering if I can increase performance on the Insert.  I have a bunch of records that I want to add at one time or quicker than doing one insert statement per record.

Here is a snippet of my code done by a developer.  Before I change this code to use prepared statements for simplicity, I want to see if there is a faster way of doing it.  This adds 1 record for each "drawer" in a system.  there may be 800 drawers so the insert process may take 8-10 seconds...can we reduce this amount of time by doing the insert a different way?

drw_username = username

        drw_Id = drawer.Id

        drw_name = drawer.Name

        drw_cab_Id = cab_Id

        drw_cab_Name = cab_Name

        odbcText = "insert into FHDRAWER (FH_USERNAME, FH_DRAWER_ID, FH_DRAWER_NAME, FH_CABINET_ID, FH_CABINET_NAME) values ('" & _


        drw_username.ToUpper & "'," & _

        drw_Id.ToUpper & ",'" & _

        drw_name.ToUpper & "','" & _

        drw_cab_Id.ToUpper & "','" & _

        drw_cab_Name.ToUpper & "')"

        odbcCommand = New OdbcCommand(odbcText, odbcconn)

        odbcCommand.CommandTimeout = 0



        Catch ex As Exception

            sharedFunctions.Display_Message(ex.Message & odbcText)

        End Try

Open in new window

Question by:posae
    LVL 4

    Expert Comment

    Keep the connection to the database open for starters - looks like you may be doing this?

    Secondly you can insert multiple records using this syntax in MS SQL. Slightly different syntax in other DBMS.
    INSERT INTO[TABLE] (column1,column2)
    select 'Value1','Fernando'
    select 'Value2','Carlos'
    select 'Value3','Vincent'

    There is a limit on the length of the sql you can pass so you may have to do eg 50 drawers at a time.
    LVL 27

    Accepted Solution

    Hello posae,

    I would create a data adaptor to the table in your database with a Where clause that would return no data and use it to fill a DataSEt
    I would then populate the DataSet with the data from the web service
    Finally, I would Update the DataSet, thereby letting .NET do what it knows to do best, which will end up inserting all of your data into the database in one go.



    Author Closing Comment

    Although the first answer did work, this solution was the most efficient...thanks!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
    A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now