asked on

Open DataTable vb.net

I've got this function that returns a DataTable for using as the datasource of my DataGridview or comboboxes.
The strange thing is, it seems like my function request the data twice from the database resulting in waiting 2 times longer.
I time it and it took eg 10 seconds on the line with comment '111111 and again 10 seconds on the line with comment '222222

Any idea how I can prevent this?

Imports  System.Data.SqlClient

Public Class Form1

  Friend m_db As SqlConnection

  Friend Function OpenDataTable(ByVal sQry As String) As DataTable
    Dim commandMain As SqlCommand
    Dim readerMain As SqlDataReader
    Dim dataTable As New DataTable
    commandMain = New SqlCommand(sQry, m_db)
    commandMain.CommandTimeout = 0
    readerMain = commandMain.ExecuteReader '1111111
    dataTable.Load(readerMain) '222222
    readerMain.Close()
    OpenDataTable = dataTable
    readerMain = Nothing
    commandMain = Nothing
  End Function

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    DataGridView1.DataSource = OpenDataTable("Select * from Assets;")
  End Sub
End Class


[code]
Imports System.Data.SqlClient

Public Class Form1

  Friend m_db As SqlConnection

  Friend Function OpenDataTable(ByVal sQry As String) As DataTable
    Dim commandMain As SqlCommand
    Dim readerMain As SqlDataReader
    Dim dataTable As New DataTable
    commandMain = New SqlCommand(sQry, m_db)
    commandMain.CommandTimeout = 0
    readerMain = commandMain.ExecuteReader '1111111
    dataTable.Load(readerMain) '222222
    readerMain.Close()
    OpenDataTable = dataTable
    readerMain = Nothing
    commandMain = Nothing
  End Function

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    DataGridView1.DataSource = OpenDataTable("Select * from Assets;")
  End Sub
End Class

Open in new window

ASKER CERTIFIED SOLUTION

Jorge Paulino

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

YZlat

membership

This solution is only available to members.

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

Start Free Trial

koossa

ASKER

The readerMain = commandMain.ExecuteReader took 4.52 seconds and the dataTable.Load(readerMain) took 18.84 seconds to execute
Total of 23.36 seconds to execute

The DataAdapter.Fill(DataSet) took on the same query 25.61 seconds

Jorge Paulino

Maybe you can improve your query ... you should never use "Select * " because you cannot benefit from indexing.

Specify all fields you need (only the ones you need) and check if you have indexes for that table.

koossa

ASKER

Yes, I know, I don't actually use "select *", I just used it for this example.

Jorge Paulino

Can you show your query? Do you use Stored Procedures?

koossa

ASKER

No, I don't use Stored Procedures.

Here is one of my queries, the query is generated depending on what the user select from filters and enter to search.

SELECT top 100 Division.Name as DivName, Locations.Name AS LocName, Case When [Assets].[Select]=0 or [Assets].[Select] is null then 0 Else 1 End as [Select], [Asset Barcode] AS [Asset Barcode], Assets.Name, Assets.Description, Assets.Category, Assets.[Division ID], [Status History].Status, [Status History].[Status Date], [Status History].[Location Barcode], Assets.[Purchase Date], Assets.[Purchase Price], Assets.[Expiry Date], Assets.[Expiry value], [Status History].[History ID], Assets.[Notes],[Custom1],[Custom2],[Custom3],[Custom4],[Custom5],Case When Photo_Thumb Is Null Then 0 Else 1 End as PhotoExists FROM Assets LEFT OUTER JOIN Division ON Assets.[Division ID] = Division.ID LEFT JOIN (Locations RIGHT JOIN [Status History] ON Locations.[Location Barcode] = [Status History].[Location Barcode]) ON Assets.[Asset Barcode] = [Status History].[Asset ID] Where ( (Division.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date]
)=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or ([Asset Barcode] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Description] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Notes] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Ba
rcode]))) or (Assets.[Category] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or ([Status] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or ([Locations].[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode])))) or ( (Division.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Asset Barcode] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) 
Is Null)) or (Assets.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Description] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Notes] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Category] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Status] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Locations].[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)));

Open in new window

Jorge Paulino

You should use SP and indexes to speed up the queries ... you could have nice improvements.

You also should use parameters instead of string concatenation (if you use it).

koossa

ASKER

Thank you!
My tables are thoroughly indexed.
Will Stored procedures be worth the effort on speed improvement, I don't want to work a day on it and only win a couple of milliseconds?

SOLUTION

Jorge Paulino

membership

This solution is only available to members.

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

Start Free Trial