get faster execution of query in database RRS feed

  • Question

  • Hi members

    before it was fast so fast

      For i = 0 To DataGridView1.Rows.Count - 1
                    Dim kk As New SqlCommand("select cbMarq,AR_Ref from F_DOCLIGNE where AR_Ref like '%" & DataGridView1.Rows(i).Cells(1).Value & "%' and DO_Piece='" & DataGridView1.Rows(i).Cells(0).Value & "'", cn)
                    Dim qq As New SqlDataAdapter(kk)
                    MsgBox("Numero cbMarq =" & qqs.Rows(i).Item("cbMarq") & " numero darticle " & qqs.Rows(i).Item(1))
                Catch ex As Exception
                End Try
                Using cmd1 As New SqlCommand("insert into DetailReceptionFrs(Annee,Numero,Codearticle,Numseq,Designation,Qte,PrixUnitaire,PrixRevient,Remise,ReferenceFournisseur,NumeroLigneCommandeFrs,NumeroEnsemble,Marge,Coefficient1,Coefficient2)values ('0',@Numero,@Codearticle,@Numseq,@Designation,@Qte,@PrixUnitaire,@PrixRevient,@Remise,@ReferenceFournisseur,@NumeroLigneCommandeFrs,@NumeroEnsemble,@Marge,@Coefficient1,@Coefficient2)", cn)
                        With cmd1.Parameters
                            .Add("@Numero", SqlDbType.NVarChar).Value = (TextBox1.Text)
                        .Add("@Codearticle", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(1).Value)
                        MsgBox("Numero cbMarq =" & qqs.Rows(i).Item("cbMarq") & " numero darticle " & qqs.Rows(i).Item(1))
                        .Add("@Numseq", SqlDbType.NVarChar).Value = qqs.Rows(i).Item("cbMarq")
                        .Add("@Designation", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(2).Value)
                            .Add("@Qte", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(3).Value)
                            .Add("@PrixUnitaire", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(4).Value)
                            .Add("@PrixRevient", SqlDbType.NVarChar).Value = ("0.00")
                            .Add("@Remise", SqlDbType.NVarChar).Value = ("0.00")
                            .Add("@ReferenceFournisseur", SqlDbType.NVarChar).Value = (TextBox3.Text)
                            .Add("@NumeroLigneCommandeFrs", SqlDbType.Int).Value = 0
                            .Add("@NumeroEnsemble", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(7).Value)
                            .Add("@Marge", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(8).Value)
                            .Add("@Coefficient1", SqlDbType.NVarChar).Value = ("0.000")
                            .Add("@Coefficient2", SqlDbType.NVarChar).Value = ("0.000")
                        End With

    was just an id, and when i changed to take value from another table it get very slower

    because i want used it to manipulate data from this table with other table so i forced to make this manipulation

    if need any idea how i think of this i will add it

    just for now i have a select query wich will be inserted to another table that being so slow

    Tuesday, June 25, 2019 10:20 AM

All replies

  • Hello,

    I would recommend

    • Make sure there are proper indices on each table you are working with. Using SSMS (SQL-Server Management Studio) which has tools to review your data and see if you may benefit from new or additional indexes.
    • There is no reason to create a SqlDataAdapter and SqlCommand for each iteration, instead create them once then inside the for next use them. Also when creating the SqlCommand create the parameters using Add like you are currently and set values on each iteration.
    • Since you are using SqlClient objects it would be prudent to populate your DataGridView with a DataTable and iterate the DataTable rather than the DataGridView rows and columns, this will provide slight improvement and is better as you are touching raw data rather than the cells of the DataGridView.

    To get at rows Dim dt As DataTable = CType(DataGridView1.DataSource,DataTable)

    To get at values dt.Rows(i).Field(Of String)("SomeTextField") dt.Rows(i).Field(Of Decimal)("SomeDecimalField")

    If the current code was fast and now is slow the first thing to check is bullet 1 and the rest will help also.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Tuesday, June 25, 2019 11:04 AM
  • thank you for your help and i still need help
    Tuesday, June 25, 2019 2:10 PM
  • thank you for your help and i still need help

    I would say that if you take the T-SQL and execute it in SSMS, it runs quickly.

    But on the other hand., maybe you will understand where problem with slowness is at.

    The datatable slows things down, which I stopped using many years ago.

    You should consider using List(of T) using the DTO  pattern with DTO using auto properties and see if you get better performance.

    What do you have to loose in trying it.

    dim dtos = new List(Of DTO)

    In each iteration of the reader loop

    dim dto = new Dto()

    dto.Name = reader("Name")

    populat rest of dto


    Tuesday, June 25, 2019 4:50 PM
  • after filling a datatable from database

    can i make a requet to that datable to filter result

    that maybe make it faster

      For i = 0 To DT.Rows.Count - 1
                        Dim cmd As New SqlCommand("select AR_Design from F_ARTICLE where AR_Ref=@AR_Ref", cn)
                        cmd.Parameters.AddWithValue("@AR_Ref", DT.Rows(i).Item("Article"))
                        Using EXTReader As SqlDataReader = cmd.ExecuteReader
                            While EXTReader.Read()
                                DT.Rows(i).Item("Designation") = EXTReader("AR_Design")
                            End While
                        End Using

    i want make a select for all the table in place

    after i want filter the datatable in place filtering the database

    • Edited by Houssem12 Tuesday, September 24, 2019 10:25 AM
    Tuesday, September 24, 2019 10:12 AM
  • Maybe you have endless exceptions, but by using the modern version of "resume on error" it really does not go quicker. 

    Modern version of resume on error

           End Try


    Tuesday, September 24, 2019 2:32 PM