[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DataAdapter problem (VB.Net,MySQL)

Posted on 2007-07-22
14
Medium Priority
?
805 Views
Last Modified: 2013-11-26
HI guys..

Need some help here, plz.. I can't solve this.. :(

My batch insert is extremely slow.. :( So ther must be something wrong with my code

It says that it dosen't suport updatebatchsize.
If i remove updatebatchsize it works ,but it takes over  6,5 secounds to insert 8000 rows,
If I create 8000 querys with a loop it take about 3,5 secounds to insert them all.

So there is something that dosen't work as it should..


Here is my code............

 Public Sub BatchUpdate(ByVal dt As DataTable)

        Dim MyConString As String = _
                   "Persist Security Info=False;" & _
                   "DATABASE=test;" & _
                   "PORT=3307;" & _
                   "SERVER=127.0.0.1;" & _
                   "Connect Timeout=30;" & _
                   "user id=test;" & _
                   "pwd=test;"

        'Create a SQLConnection, passing in the proper connection string
        Dim myConnection As New MySqlConnection()
        myConnection.ConnectionString = MyConString

        'Create a SQLDataAdapter
        Dim da As New MySqlDataAdapter()

        'Create and configure a SQLCommand for the DataAdapter
        da.InsertCommand = New MySqlCommand()
        With da.InsertCommand

            'Set the connection
            .Connection = myConnection

            'Must be set to none
            .UpdatedRowSource = UpdateRowSource.None

            'Set the CommandType to Text
            .CommandType = CommandType.Text

            'Set the SQL Text, including parameters
            .CommandText = _
                "INSERT INTO testTbl" & _
                "(ImportDate, FileDate, FileType) " & _
                "VALUES (?ImportDate, ?FileDate, ?FileType) " & _
                "ON DUPLICATE KEY UPDATE DuplicateInsert = 1;"

            'Add the parameters.  
            '    Make sure to map the source colum in our table to the parameter
            With .Parameters
                .Add("?ImportDate", MySqlDbType.Date, 0, "ImportDate")
                .Add("?FileDate", MySqlDbType.Date, 0, "FileDate")
                .Add("?FileType", MySqlDbType.VarChar, 15, "FileType")
            End With

        End With

        'Setting the UpdateBatchSize to 0
        '    will submit all the rows in a single batch.
        da.UpdateBatchSize = 0

        'Perform the insert to the SQL table.
        da.Update(dt)
    End Sub
0
Comment
Question by:AWestEng
  • 9
  • 5
14 Comments
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 2000 total points
ID: 19545202
Why do you use a data-adapter ? Do you need to return something ?
You can try it with executeNonQuery (it's faster then the update... -> Executes a Transact-SQL statement against the connection and returns the number of rows affected. )

strSQL = "INSERT INTO testTbl" & _
                "(ImportDate, FileDate, FileType) " & _
                "VALUES (?ImportDate, ?FileDate, ?FileType) " & _
                "ON DUPLICATE KEY UPDATE DuplicateInsert = 1;"
Dim command As New MySqlCommand(strSQL, conenction)
MySqlCommand.executeNonQuery
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19547239
But I have a datatable with 8000 rows(input to the functions), can I insert them all at the same ttime,

I don't need something back I just want to insert all data. so maybe your solution is better. :)
So how do I insert all data from the datatable with you code?

Thx for helping me.. :)
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19551011
I'm trying to understan how the code you gave me should be implemented in my code, :)

Can you give me an example..

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:AWestEng
ID: 19551093
What I can't figureout is how I get the data from the table to the insert query or how i bind the datatable with the command
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19551427
Ihave this code now.. but it takes even more time to execute 7 sec

  Public Sub SQLUpdate(ByVal dt As DataTable)
        Try
            Dim MyConString As String = _
                        "Persist Security Info=False;" & _
                        "DATABASE=test;" & _
                        "PORT=3308;" & _
                        "SERVER=127.0.0.1;" & _
                        "Connect Timeout=30;" & _
                        "user id=test;" & _
                        "pwd=ttest;"

            'Create a SQLConnection, passing in the proper connection string
            Dim myConnection As New MySqlConnection(MyConString)
            myConnection.Open()

            'Create new command
            Dim cmd As New MySqlCommand
 
            With cmd
                'Set the connection
                .Connection = myConnection

                'Must be set to none
                .UpdatedRowSource = UpdateRowSource.None

                'Set the CommandType to Text
                .CommandType = CommandType.Text

                ''Set the SQL Text, including parameters
                .CommandText = _
                    "INSERT INTO resa " & _
                    "(ImportDate, FileDate, FileType) " & _
                    "VALUES (?ImportDate, ?FileDate, ?FileType) " & _
                    "ON DUPLICATE KEY UPDATE DuplicateInsert = 1"
            End With

            Dim row As DataRow
            For Each row In dt.Rows
                With cmd.Parameters
                    .AddWithValue("?ImportDate", row("ImportDate"))
                    .AddWithValue("?FileDate", row("FileDate"))
                    .AddWithValue("?FileType", row("FileType"))
                End With

                'Perform the insert to the SQL table.
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            Next

        Catch ex As Exception
        End Try
    End Sub
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 19553216
I wasn't sure that the code with the loop you've written above, would be quicker (apparantly not according to your tests...). So I've got 2 other questions for you.
How do you load your recordset, from where does your data come  ? Do you need to update all the records in your database ?
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19556552
This is the flow I working with

1: The data comes from a CSV file
2: Amost all the data are new, 99.9% of the 8000 rows
3:; Some of the data has a stange format in the CSV file  0.0 can be .0 (dot zero) or sometimes even empty string, I also have .00 (dot zero zero) that should be 0.00
4: This data has to be fixed before I insert it
5: I read all data to the datatable
6: I fix the values
7: I insert them
 
If I can solve the problem in a bertter way it would be great. but can't  figure out any other methods
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 19562975
Do you have many indexes on the table you import ? Try to disable them, do the insert and put the indexes back. Sometimes that's helpful for quick loading a lot of data.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 19562981
By the way, very slow is relative...
With update-batch: 705 insert's each second
With loop: 1715 insert's each second

I don't think that is very slow, but that's my opinion.
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19565989
Oki. how look you code for a batch-update/insert

I have 8000 rows. Batch-update: 6,5 sec: that's 1203 rec / sec
                               Loop: 3,5 sec:: thats 2285

It's not the problem that it's slow. the confusion is that a loop is much faster the a batch insert .
That's way im intrested in how other do there batch insert, and see if I can change my code to the better
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19566056
And here is the result if I send more the once.

1          3,5 sec
500:     1,3 sec
1500:   1,2 sec
2500:   1,14 sec
6000:   1,09 sec

So I thought a batch insert was even faster the that

But a question regarding turning the indexes off, is some of the records already exists in the database, is it still ok to turn them off?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 19572108
>> But a question regarding turning the indexes off, is some of the records already exists in the database, is it still ok to turn them off?

For your primary key it's not a good idea, but all the other indexes (used for performance reasons in other query's) there is no problem.

Another quesion pops my mind: if you're inserting through a loop and one record fails because of duplicate index, what are you doing with all the records that were successfull or with the following records ?
If you perform it through a batch-update, is it successfull then or not ?
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19574084
I use ON DUPLICATE KEY UPDATE so thats ok.
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19578264
and it works on a batch-update to, i have ON DUPLICATE KEY UPDATE  there to,,

But what I can understand now is that the fastest way to insert data is looping, except the LOAD FILE function but thats not a option here because I need to alter the data befor I insert it.


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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses
Course of the Month19 days, left to enroll

834 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