Performance Inserting Multiple Records - VB.net

Hello,

I have a vb.net 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?

Eric
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
        Try
            odbcCommand.ExecuteNonQuery()
        Catch ex As Exception
            sharedFunctions.Display_Message(ex.Message & odbcText)
        End Try

Open in new window

posaeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

breneCommented:
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'
UNION ALL
select 'Value2','Carlos'
UNION ALL
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.
0
DabasCommented:
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.

Regards,

Dabas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
posaeAuthor Commented:
Although the first answer did work, this solution was the most efficient...thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.