DataReader vrs DataSet.. 5

[NoCoolHandle]

OK, I know that a firehose cursor is ALWAYS faster at bringing back multiple rows than any other type, and that a datareader uses a firehose cursor where a dataset will retrieve ALL the records to the serverside cursor before building the datatable, however... and this is the question.

If you are only bringing back one row, wouldn't the record come back as fast via a dataadapters fill method as through a commands executereader method?

If not, why? How much of a difference would it make?

TIA

Rob

 

[JohnYingling]

Try it yourself.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing

http://www.VBCompare.com

Compare Code

http://www.VBSortGen.com

Generate Sort Class in VB

http://www.Vbcompare.com/Check.Htm(1979

Check To MS)

 

[chrissie1]

for one record i dont think it would be that much difference, but perhaps for a thousand users with one record. depends on how robust you need to be or the program.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
What a wonderfull world - Louis armstrong

 

[RiverGuy]

Many times, if I want to bring back one row's worth of data, and I don't have a TON of fields, I will create a stored procedure to return output parameters, and then just .ExecuteNonQuery() and check the parameters.

 

[NoCoolHandle]

Thanks all.. (JohnYingling - I couldn't have said it better myself, I had just hoped someone else had done all the work)

I did some tests...

15,0000 loops of the same execution to a datareader and also a datatable, 3 fields being returned...

datareader = 12 seconds
datatable = 14-15 seconds
add 15 seconds to each for the first execution of the application to the datasource...

Never more than 2 connections, no noticable differences in memory.

I will test with output parameters next and get back...

Rob

At this point BOTTOM Line datatable = 20%-30% worse.

 

[JohnYingling]

Knowledge beats hearsay.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing

http://www.VBCompare.com

Compare Code

http://www.VBSortGen.com

Generate Sort Class in VB

http://www.Vbcompare.com/Check.Htm(1979

Check To MS)

 

[chrissie1]

thanks have a star

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
What a wonderfull world - Louis armstrong

 

[NoCoolHandle]

OK the last one was worthwhile!
Parameters - parameters - parameters!!!!!!

-- all code at bottom

Latest test results..

Initial load 30 seconds = datareader
28 seconds = dataset
3 seconds = parameters

secondary tests (a little slower this morning)
17 seconds = datatable
14 seconds = dataset
3 seconds = parameters

-- see full results below (after code)

SQL server Storedprocs

--code for datasets and datareaders
Create  Proc abc as 
select firstname,lastname,title from northwind.dbo.employees where employeeid = 1
go

--For parameter test
create proc adb2 
@firstname  varchar(3000) output,
@lastname varchar(3000) output,
@title varchar(3000) output
as
select @firstname = firstname,@lastname=lastname,@title=title from northwind.dbo.employees
where employeeid = 1

VB.Net code

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents RadioButton3 As System.Windows.Forms.RadioButton
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.RadioButton1 = New System.Windows.Forms.RadioButton
        Me.RadioButton2 = New System.Windows.Forms.RadioButton
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.RadioButton3 = New System.Windows.Forms.RadioButton
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(32, 200)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(128, 40)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'RadioButton1
        '
        Me.RadioButton1.Checked = True
        Me.RadioButton1.Location = New System.Drawing.Point(24, 16)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.Size = New System.Drawing.Size(216, 24)
        Me.RadioButton1.TabIndex = 1
        Me.RadioButton1.TabStop = True
        Me.RadioButton1.Text = "DataSet"
        '
        'RadioButton2
        '
        Me.RadioButton2.Location = New System.Drawing.Point(24, 48)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(216, 32)
        Me.RadioButton2.TabIndex = 2
        Me.RadioButton2.Text = "DataReader"
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(24, 144)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(232, 20)
        Me.TextBox1.TabIndex = 3
        Me.TextBox1.Text = "TextBox1"
        '
        'RadioButton3
        '
        Me.RadioButton3.Location = New System.Drawing.Point(24, 96)
        Me.RadioButton3.Name = "RadioButton3"
        Me.RadioButton3.Size = New System.Drawing.Size(216, 24)
        Me.RadioButton3.TabIndex = 4
        Me.RadioButton3.Text = "Parameters.."
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.RadioButton3)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.RadioButton2)
        Me.Controls.Add(Me.RadioButton1)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim tmStart As DateTime = Now()
        Dim a, b, c As String
        Dim DBLTM As Double = CType(Now.Minute.ToString & Now.Second.ToString & Now().Millisecond.ToString, Double)
        If RadioButton1.Checked Then
            Debug.Write("DataTable" & vbCrLf)
        ElseIf RadioButton2.Checked Then
            Debug.Write("DataReader" & vbCrLf)
        Else
            Debug.Write("Parameters")
        End If
        Debug.Write(DBLTM.ToString & " Start - " & vbCrLf)
        Dim y As Integer
        If IsNumeric(TextBox1.Text) Then
            y = TextBox1.Text
        Else
            y = 15000
        End If
        For y = 0 To y
            If RadioButton2.Checked = True Then
                Dim dr As SqlClient.SqlDataReader = ABCDatareader()
                dr.Read()
                TextBox1.Text = dr.Item(0).ToString & ", " & dr(1).ToString & ", " & dr.Item(2).ToString
                dr.Close()
                dr = Nothing
                GC.Collect()
            ElseIf RadioButton1.Checked = True Then
                Dim dt As DataTable = ABCDatatable()
                TextBox1.Text = dt.Rows(0)(0).ToString & ", " & dt.Rows(0)(1).ToString & ", " & dt.Rows(0)(2).ToString
                dt.Dispose()
                dt = Nothing
                GC.Collect()
            Else
                If ABC2Parameter(a, b, c) = True Then
                    TextBox1.Text = a & ", " & b & ", " & c
                Else
                    MsgBox("problem")
                    Exit Sub
                End If
            End If
        Next
        Dim tmEnd As DateTime = Now()
        Dim dblEnd As Double = CType(Now.Minute.ToString & Now.Second.ToString & Now().Millisecond.ToString, Double)
        Debug.Write(dblEnd & " End Time" & vbCrLf)
        Debug.Write(dblEnd - DBLTM & " time differenc in milliseconds " & vbCrLf & vbCrLf)

        Me.Text = DateDiff(DateInterval.Second, tmStart, tmEnd)
        MsgBox(Me.Text & "Seconds")
    End Sub
#Region "database code built at lynchtek.com/sql.aspx"
    Private Function ABCDatareader() As SqlClient.SqlDataReader
        Dim con As New SqlClient.SqlConnection("server=(local);database=Northwind;trusted_connection=yes")
        Dim cmd As New SqlClient.SqlCommand("abc", con)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim dr As SqlClient.SqlDataReader
        With cmd
            .CommandType = CommandType.StoredProcedure
            con.Open()
            Try
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                MsgBox(ex.Message)
                'response.write ex.message
                Return Nothing
                Exit Function
            Finally
                cmd.Dispose()
            End Try
        End With
        If dr Is Nothing Then
            Return Nothing
        Else
            Return dr

        End If
    End Function

    Private Function ABCDatatable() As DataTable
        Dim con As New SqlClient.SqlConnection("server=(local);database=Northwind;trusted_connection=yes")
        Dim cmd As New SqlClient.SqlCommand("abc", con)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim dt As New DataTable
        With cmd
            .CommandType = CommandType.StoredProcedure
            con.Open()
            Try
                da.Fill(dt)
            Catch ex As Exception
                Return Nothing
                Exit Function
            Finally
                con.Close()
                cmd.Dispose()
            End Try
        End With
        If dt Is Nothing Then
            Return Nothing
        Else
            Return dt
        End If
    End Function

    Private Function ABC2Parameter(ByRef outPrmfirstname As String, ByRef outPrmlastname As String, ByRef outPrmtitle As String) As Boolean
        Dim con As New SqlClient.SqlConnection("server=(local);database=Northwind;trusted_connection=yes")
        Dim cmd As New SqlClient.SqlCommand("adb2", con)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        With cmd
            .CommandType = CommandType.StoredProcedure
            With .Parameters
                .Add("@firstname", SqlDbType.VarChar, 3000).Direction = ParameterDirection.Output
                .Add("@lastname", SqlDbType.VarChar, 3000).Direction = ParameterDirection.Output
                .Add("@title", SqlDbType.VarChar, 3000).Direction = ParameterDirection.Output
            End With
            con.Open()
            Try
                cmd.ExecuteNonQuery() ' For a stored proc with no records...

                outPrmfirstname = .Parameters("@firstname").Value.ToString
                outPrmlastname = .Parameters("@lastname").Value.ToString
                outPrmtitle = .Parameters("@title").Value.ToString
            Catch ex As Exception
                'msgbox ex.message
                'response.write ex.message
                Return False
                Exit Function
            Finally
                con.Close()
                cmd.Dispose()
            End Try
        End With

        Return True
    End Function
#End region
End Class

More results
DataReader
5017879 Start -
5031708 End Time
13.829 time differenc in milliseconds

DataReader
5035153 Start -
5048923 End Time
13.770 time differenc in milliseconds

Parameters5112757 Start -
5115842 End Time
3.085 time differenc in milliseconds

Parameters
5118476 Start -
5121580 End Time
3.104 time differenc in milliseconds

DataTable
5139436 Start -
5156190 End Time
16.754 time differenc in milliseconds

DataTable
5211282 Start -
5228206 End Time
16.924 time differenc in milliseconds

Parameters
5523358 Start -
5526282 End Time
2.924 time differenc in milliseconds

Parameters
5529487 Start -
5532341 End Time
2.854 time differenc in milliseconds

 

[RiverGuy]

Wow, I didn't expect that much difference. Thanks for doing the research.

 

[svankley]

Your my HERO!! ;^)

 

[NoCoolHandle]

So watya think Steve, getting stars for my own question..
Rob
PS call me sometime
(i left my phone at home today though )

 

[BobRodes]

You get another one from me.



Bob

 

[chiph]

A lot of the time used in returning a resultset from the database is spent parsing the SQL statement that you gave it. Is the syntax right? Do all the databases/tables exist? etc.

This time can be vastly reduced by using stored procedures. This is because the syntax got verified when the SP got created (there are certain exceptions, like using the EXEC command). The other way to get this benefit is using parameterized queries (as you've discovered). The first time it runs, it has to be checked. But once it passes checking, SQL Server stores it in it's procedure cache. On subsequent calls using the identical SQL, with the identical credentials, SQL Server will retrieve the pre-compiled query from it's cache, saving tons of time.

It's still not quite as fast as a stored proc, but it's around 85-90% as fast, plus it's easier to manage the SQL, since it's stored in your source-code control system along with your VB.NET code. Versioning a SP is tough.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[NoCoolHandle]

Chip, I don't know if you looked at the scripts for the tests, but you will find it was all done via a stored proc.

Even if it wasn't, as long as the sql statement was exactly the same, you would find that once the statement was executed, it would have been cached for all future executions... The speed diffence between a proc and adhoc query destroyed or maybe represented by a null value

However this was not the case. It was all via a single store proc that was in cache (very testable - syscacheobjects) all the time..

THe expense is all in the objects being used, and the type of return being generated... If I had a bunch of records.. I am sure 200000 output parameters would have sucked.

As it was a single return parameter will always benifit from a command object and it's parameters.