MySqlDataAdapter.fill of a datatable very slow!

[vejita866]

Hello guys,
i have an issue with the code above.
When i make the DataAdapter.Fill(table) of a very little select of about 30 row with about 10 colums, it gets very slow.
any suggestion?

Dim table As New DataTable()
Try


Dim mycmd As New MySqlCommand(querymysql, conn)
Dim DataAdapter As New MySqlDataAdapter(mycmd)
Dim commandBuilder As New MySqlCommandBuilder(DataAdapter)



DataAdapter.Fill(table)
Catch sqlExc As MySqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _ MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
datagrid1.datasource = table

 

[jmcilhinney]

What does the query look like? How many rows and columns are in the database table? If not all, how many rows and columns does the query return? How long does the query take to execute?

 

[vejita866]

here you are the query:
SELECT col.nome AS Collane, tes.id AS IDtestata, tes.Titolo AS Titolo_Testata, edi.nome AS Editore, rep.nome_reparto AS Reparto, Volumi.ID AS IDvolume, Volumi.Idtestata, Volumi.Titolo AS Titolo_fumetto, Volumi.sottotitolo AS Sottotitolo_Fumetto, Volumi.numero AS Numero, Volumi.prezzo AS Prezzo, Volumi.Esaurito as Esaurito " _
& "FROM Volumi " _
& "LEFT JOIN Testate AS tes ON Volumi.IDtestata = tes.id " _
& "LEFT JOIN Editori AS edi ON tes.ideditore = edi.id " _
& "LEFT JOIN Collane AS col ON tes.idcollana = col.id " _
& "LEFT JOIN Reparto AS rep ON tes.idreparto = rep.id LIMIT 0 , 30

in the db there are about 50.000 rows and 13 colums, but i dont think is important cause the query take to execute about 0,083 sec.

 

[jmcilhinney]

Sorry, are you saying that it takes 83 milliseconds to execute the query? I meant how long does it take in your application. I assume that it takes longer than that because that is not slow.

 

[vejita866]

Yeah, the query takes 0.083 second to execute the query, but the procedure takes about 4-5 seconds for each.
I suppose i have made a mistake, cause it takes too long.

 

[vejita866]

can anyone help me?

 

[dmarron]

Hi Vejita886

I am having the same problem as you have you had a resolution to your issue?
Perhaps you can help me.

Thanks.

 

[vejita866]

no, i didn't find the answer anywhere.
I tried using MS SQL with the same code and using the builtin function.
The result is the 66% less of time to fill the datagrid.

 

[Sparrow]

The problem is not the code, its the connection to the server and the server hardware and how the server was setup. My previous project checked for unbilled calls on a VOIP Sql database and on the main sever where all the calls are it returns about 40 000 rows in 7 columns and that takes about 3min from starting the connection to filling a data grid view.

 

[vejita866]

The problem is not the code, its the connection to the server and the server hardware and how the server was setup. My previous project checked for unbilled calls on a VOIP Sql database and on the main sever where all the calls are it returns about 40 000 rows in 7 columns and that takes about 3min from starting the connection to filling a data grid view.

The server hardware is a core i5 2500k and i left the default config (mysql and Mssql).
What do you mean in a problem with server connection? the application and the database are on localhost.

 

[Herman]

You still have not posted your procedure, I'm not sure how you expect us to guess what's wrong with it.

 

[vejita866]

correct! here my code...

Query = "SELECT TOP 100 col.nome AS Collane, tes.id AS IDtestata, tes.Titolo AS Titolo_Testata, edi.nome AS Editore, rep.nome_reparto AS Reparto, Volumi.ID AS IDvolume, Volumi.Idtestata, Volumi.Titolo AS Titolo_fumetto, Volumi.sottotitolo AS Sottotitolo_Fumetto, Volumi.numero AS Numero, Volumi.prezzo AS Prezzo, Volumi.Esaurito as Esaurito " _
& "FROM Volumi " _
& "LEFT JOIN Testate AS tes ON Volumi.IDtestata = tes.id " _
& "LEFT JOIN Editori AS edi ON tes.ideditore = edi.id " _
& "LEFT JOIN Collane AS col ON tes.idcollana = col.id " _
& "LEFT JOIN Reparto AS rep ON tes.idreparto = rep.id " _
'& "LIMIT 0 , 30 "
data_visualizzatestate = Inizializza_Componenti.RiempiDataGrid(Query)
Fumetti1.volumi_datagrid_ricercafumetti.DataSource = data_visualizzatestate
Fumetti1.volumi_datagrid_ricercafumetti.Columns("IDtestata").Visible = False
Fumetti1.volumi_datagrid_ricercafumetti.Columns("idvolume").Visible = False
Fumetti1.volumi_datagrid_ricercafumetti.Columns("idtestata1").Visible = False
Query = "SELECT Editori.ID, Editori.Nome FROM Editori Order By Editori.Nome "
dataeditore = Inizializza_Componenti.RiempiDataGrid(Query)
Query = "SELECT Collane.ID, Collane.Nome FROM Collane ORDER By Collane.Nome"
datacollana = Inizializza_Componenti.RiempiDataGrid(Query)
Query = "SELECT Reparto.ID, Reparto.Nome_reparto FROM Reparto Order By Reparto.Nome_Reparto ASC"
datareparto = Inizializza_Componenti.RiempiDataGrid(Query)
Try
Fumetti1.volumi_ricerca_editore.DisplayMember = "Nome"
Fumetti1.volumi_ricerca_editore.ValueMember = "ID"
Fumetti1.volumi_ricerca_collana.DisplayMember = "Nome"
Fumetti1.volumi_ricerca_collana.ValueMember = "ID"
Fumetti1.volumi_ricerca_reparto.DisplayMember = "Nome_Reparto"
Fumetti1.volumi_ricerca_reparto.ValueMember = "ID"
Fumetti1.volumi_ricerca_collana.SelectedValue = 0
Fumetti1.volumi_ricerca_editore.SelectedValue = 0
Fumetti1.volumi_ricerca_reparto.SelectedValue = 0
Fumetti1.volumi_ricerca_reparto.DataSource = datareparto
Fumetti1.volumi_ricerca_editore.DataSource = dataeditore
Fumetti1.volumi_ricerca_collana.DataSource = datacollana


Function RiempiDataGrid(ByVal querysql As String)

Dim table As New DataTable
Try
' The SqlConnection class allows you to communicate
' with SQL Server and DataTable.

' A SqlCommand object is used to execute the SQL commands.
Dim mycmd As New SqlCommand(querysql, con)
Dim DataAdapter As New SqlDataAdapter(mycmd)
Dim commandBuilder As New SqlCommandBuilder(DataAdapter)
' The SqlDataAdapter is responsible for using
' a SqlCommand object to fill a DataSet.


DataAdapter.Fill(table)

' Set the DataGrid caption, bind it to the DataSet,
' and then make it Visible


Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return table
End Function

 

[vejita866]

i can upload my whole project if it could help.

 

[Herman]

It probably would, if you can also spare a copy of the database in question (or at least the tables in question...).

 

[vejita866]

good, here you are the link

Ando.zip

later i will poste the tables