locked
single query vs multiple RRS feed

  • Question

  • I am going to get a TCP request to send data values to a client.  The request is going to contain a list of the sensor ids to send.  There could be 1-1000 sensors requested.  Before sending the data i want to verify that these are valid ids by comparing them against a database table.  If there are invalid ids then a message has to be build ("SENSORS|INVALID|..all the invalid ids). There were 2 ways I ultimately thought to do this but I wasnt sure if it made a difference one way or the other.

    1.   query and store the whole data table locally within a variable and run through it with nested for loops
    Dim sensors() as String '  this contains the sensors requested

    Dim tbl as DataTable = FillTable("Select * from sensors")
    For each id as string in sensor
    For each dr as DataRow in tbl.Rows
    If id = dr.Item("ID") Then

    End if
    Next
    Next

    2.   use a data reader to query the database individually to check for each sensor id

    Is one method more efficient than the other? 
    Wednesday, April 1, 2009 5:16 PM

Answers

  • I would suggest something similar to the first method.

    If the values in the database are rather static (eg they do not change often if at all), then load the datatable and KEEP a reference to it.  That way you issue the Fill() ONCE per application instance.

    Also, since you are looking for a "sensor id" it is logical that these values are unique and so should be a Key on the DataTable.  As such, you can simply use "If tbl.Rows.Contains("ID") Then" to determine if the sensor ID is in the data table.

    Even if you want to refill the datatable on each TCP request, this would still be the most efficient way to have the application check a list of ids against the database.

    Of course, this all assumes a database server - if you just have an Access database file, then do what you like because I don't think there will be much difference.  Just note that typical ADO.Net data usage involves caching a useful amount of data in a DataSet.DataTable, doing all the data manipulation in memory using the DataTable, and then writing all changes back to the database.
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Marked as answer by meriano Friday, April 3, 2009 5:53 PM
    Thursday, April 2, 2009 2:49 PM
    Moderator
  • you can use FillSchema pror to the Fill method that will pull the datatable schema from the select command

    daMyData = New SqlClient.SqlDataAdapter(cmdMyData)

    daMyData.FillSchema(MyDataTable, SchemaType.Source)
    daMyData.Fill(MyDataTable)


    or set it on the DataTable in code

    MyDataTable.PrimaryKey = New DataColumn() {MyDataTable.Columns("Id")}

    • Marked as answer by meriano Friday, April 3, 2009 5:52 PM
    Friday, April 3, 2009 5:45 PM

All replies

  • Is FillTable using DataAdapter or TableAdapter?  If so, adapters use DataReader behind the scenes, only one SQL query will be issued, and there will be similar performance characteristics.

    The thing to watch out for is using a separate SqlCommand for each ID to be checked.  You don't want to issue thousands of separate SELECT queries.
    Thursday, April 2, 2009 12:12 AM
  • I would suggest something similar to the first method.

    If the values in the database are rather static (eg they do not change often if at all), then load the datatable and KEEP a reference to it.  That way you issue the Fill() ONCE per application instance.

    Also, since you are looking for a "sensor id" it is logical that these values are unique and so should be a Key on the DataTable.  As such, you can simply use "If tbl.Rows.Contains("ID") Then" to determine if the sensor ID is in the data table.

    Even if you want to refill the datatable on each TCP request, this would still be the most efficient way to have the application check a list of ids against the database.

    Of course, this all assumes a database server - if you just have an Access database file, then do what you like because I don't think there will be much difference.  Just note that typical ADO.Net data usage involves caching a useful amount of data in a DataSet.DataTable, doing all the data manipulation in memory using the DataTable, and then writing all changes back to the database.
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Marked as answer by meriano Friday, April 3, 2009 5:53 PM
    Thursday, April 2, 2009 2:49 PM
    Moderator
  • If the sensor Id’s don’t change between the app starting then I’d do a local cache into Datatable once on startup.

    If the sensor Id’s do periodically change I would still use a DataTable and add a timer that queried for a new list of Id’s at whatever interval was needed;  every 10minutes, hourly, twice a day or whatever.

    Then as Sensors were sent verify them locally against the Datatable. You could loop or step through each row to test them, but you may want to look at LINQ to run your test.

    Here is an Example of using LINQ

    Public Class Form1
        Private SensorTable As DataTable
        Private SensorsToSend() As String = {"SN1", "SN2", "SN5", "SN11", "SN3", "SN20", "SN12"}
    
        Private Sub RunExample()
            Dim ListToFill As ListBox
    
            'Test for Valid Sensors
            Dim SensorResult As SensorResult = CheckSendors(SensorsToSend)
    
            'Fluff for Example
            ListToFill = CType(Me.Controls("lstGood"), ListBox)
    
            With ListToFill
                .BeginUpdate()
                .Items.Clear()
                .Items.AddRange(SensorResult.GoodSensors.ToArray)
                .EndUpdate()
            End With
    
            ListToFill = CType(Me.Controls("lstBad"), ListBox)
    
            With ListToFill
                .BeginUpdate()
                .Items.Clear()
                .Items.AddRange(SensorResult.BadSensors.ToArray)
                .EndUpdate()
            End With
    
        End Sub
    
        Private Function CheckSendors(ByVal SensosToCheck() As String) As SensorResult
            Dim retSesnorResult As New SensorResult
    
            'Query all Sesnors and validate
            Dim qAllSensors = From CheckSensor In SensosToCheck _
                               Group Join drSensor In SensorTable.AsEnumerable _
                               On CheckSensor Equals drSensor("Sensor") _
                               Into gSensorCount = Count() _
                               Select Sensor = CheckSensor, _
                               IsGood = (gSensorCount = 1)
    
            'Query for Good ones
            Dim qGoodSensors = From CurrentSensor In qAllSensors _
                               Where CurrentSensor.IsGood = True _
                               Select Sensor = CurrentSensor.Sensor
    
            'Query for Bad one
            Dim qBadSensors = From CurrentSensor In qAllSensors _
                              Where CurrentSensor.IsGood = False _
                               Select Sensor = CurrentSensor.Sensor
    
            'Fill Result with the Good and bad
            With retSesnorResult
                .GoodSensors = qGoodSensors.ToList
                .BadSensors = qBadSensors.ToList
            End With
    
            'Return reslut
            Return retSesnorResult
        End Function
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim newRow As DataRow
    
            'This code sets controls at runtime and loads a datatable with test data
            SensorTable = New DataTable("Sensors")
    
            SensorTable.Columns.Add("Sensor", GetType(String))
    
            newRow = SensorTable.NewRow()
            newRow("Sensor") = "SN1"
            SensorTable.Rows.Add(newRow)
    
            newRow = SensorTable.NewRow()
            newRow("Sensor") = "SN2"
            SensorTable.Rows.Add(newRow)
    
            newRow = SensorTable.NewRow()
            newRow("Sensor") = "SN3"
            SensorTable.Rows.Add(newRow)
    
            newRow = SensorTable.NewRow()
            newRow("Sensor") = "SN11"
            SensorTable.Rows.Add(newRow)
    
            newRow = SensorTable.NewRow()
            newRow("Sensor") = "SN12"
            SensorTable.Rows.Add(newRow)
    
            Dim MyList As New ListBox
    
            With MyList
                .Dock = DockStyle.Left
                .Width = Me.Width * 0.45
                .Name = "lstGood"
            End With
    
            Me.Controls.Add(MyList)
    
            MyList = New ListBox
    
            With MyList
                .Dock = DockStyle.Right
                .Width = Me.Width * 0.45
                .Name = "lstBad"
            End With
    
            Me.Controls.Add(MyList)
    
            RunExample()
        End Sub
    
    
    End Class
    
    'If you use a Class for results you could pass on 
    'Valid sensors but also log Invalid ones.
    Public Class SensorResult
    
        Private mGoodSensors As List(Of String)
        Public Property GoodSensors() As List(Of String)
            Get
                If mGoodSensors Is Nothing Then
                    mGoodSensors = New List(Of String)
                End If
    
                Return mGoodSensors
            End Get
            Set(ByVal value As List(Of String))
                mGoodSensors = CType(value, List(Of String))
            End Set
        End Property
    
        Private mBadSensors As List(Of String)
        Public Property BadSensors() As List(Of String)
            Get
                If mBadSensors Is Nothing Then
                    mBadSensors = New List(Of String)
                End If
    
                Return mBadSensors
            End Get
            Set(ByVal value As List(Of String))
                mBadSensors = value
            End Set
        End Property
    
    End Class
    
    
    Thursday, April 2, 2009 6:51 PM
  • Thanks for all your responses,

    The FillTable method is using a data adapter

    Yes your assumptions were correct, this is a database server.  The values in the database are mostly static but they could change (although not very often).   However if they do change, they idea was to not have to stop the app  so that is why I was currently filling the table on every TCP request.

     I tried the If tbl.Rows.Contains("ID") then  because the ID is indeed the primary key but when I fill the table, the new table says there is no primary key.  How do I bring this is with the values?
     



    • Marked as answer by meriano Friday, April 3, 2009 5:52 PM
    • Unmarked as answer by meriano Friday, April 3, 2009 5:52 PM
    Friday, April 3, 2009 5:26 PM
  • you can use FillSchema pror to the Fill method that will pull the datatable schema from the select command

    daMyData = New SqlClient.SqlDataAdapter(cmdMyData)

    daMyData.FillSchema(MyDataTable, SchemaType.Source)
    daMyData.Fill(MyDataTable)


    or set it on the DataTable in code

    MyDataTable.PrimaryKey = New DataColumn() {MyDataTable.Columns("Id")}

    • Marked as answer by meriano Friday, April 3, 2009 5:52 PM
    Friday, April 3, 2009 5:45 PM
  • Thanks TechNoHick,

    I am using ODP.NET
     
    I was trying the FillSchema method but it kept giving me the error "InvalidCastEcxeption:  Unable to cast object of type 'Oracle.DataAccess.Client.OralceCommand' to type 'System.Data.Common.DbCommand'

    I was able to set it using code though.  

    Thanks again for everyone's help
    Friday, April 3, 2009 5:52 PM