[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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

0
posae
Asked:
posae
1 Solution
 
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
 
posaeAuthor Commented:
Although the first answer did work, this solution was the most efficient...thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now