posae
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Although the first answer did work, this solution was the most efficient...thanks!
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.