asked on

DataGridView Display

I’m currently facing a trouble when executing the following procedure :

    Private Sub PosHistory()
        Dim DateFrom As Date = CDate(tsFrom.Text & " 00:00:00")
        Dim DateTo As Date = CDate(tsTo.Text & " 23:59:59")

        Dim dtList As DataTable
        dtList = GetDataTable(ConnString, CommandType.StoredProcedure, "Pos_History", New SqlParameter("@DateFrom", DateFrom), New SqlParameter("@DateTo", DateTo))
        With DataGridViewList
            .DataSource = Nothing
            .Columns.Clear()
            .DataSource = dtList
        End With
    End Sub

Open in new window

DataTable contains approx. 1000 records.
When executing it for the first time, datagridview values are showed instantly.
At the next execution, after having change a parameter value, we must wait for approx. 1 minute to have the result (same amount of records)
Waiting time is during the execution of the line : « .DataSource = dtList »
Does someone have an idea on how to optimize the code so it’s taking the same time as if it was run for the first time ?

Thanks for your help !

Scott McDaniel (EE MVE )

I don't think you need to call Columns.Clear. Setting the Datasource to Nothing will do what you need on that.

What does GetDataTable look like?

Shaun Kline

Do you need the .Columns.Clear line? If you are filling the DataGridViewList with data that has the same columns, this line would seem to be not needed. Some of the delay you are seeing may be the grid rebuilding the columns.

Also, if you have the columns set to auto-resize, there will be some delay there as the grid needs to fill before it can determine how big each column needs to be.

mmorelle

ASKER

I try without "columns.clear".
Maybe some seconds better, but it take always a long time...
Thanks

mmorelle

ASKER

It's not a problem of "auto-resize" because we have the problem only after the first execution...
Thanks

mmorelle

ASKER

What does GetDataTable look like? :
GetDataTable make a call to a DLL
This DLL make the connection with SQL Server, send the parameters and the name of the stored-procedure, and receive the datatable.

Scott McDaniel (EE MVE )

If removing the .Columns.Clear method doesn't help, then perhaps you could temporarily bypass the call to the DLL, and create your own Connection and Command instead, just for testing purposes:

Using con As New SQLConnection(ConnString)
con.Open
Using cmd As New SQLCommand
cmd.Connection = con
'/ set your CommandText, Parameters, etc here
Using dt As New Database
dt.Load(cmd.ExecuteReader)
DataGridViewList.Datasource = dt
End Using
End Using
End Using

If you have the same performance issues with this method, then I'd assume the trouble lies in the connection to the server, or perhaps in the Stored Procedure. You can use various methods to determine how SQL Server executes that query, but you'd be best to get that information from a SQL Server Expert.

ASKER CERTIFIED SOLUTION

mmorelle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shaun Kline

In your PosHistory method, you are using a WITH statement for DataGridViewList, but you are passing dgList into InitializeDG. Shouldn't those objects be the same?

mmorelle

ASKER

The problem was in an other place!