Re: Fastest db methods?

From: Pablo Castro [MS] (pablocas_at_online.microsoft.com)
Date: 04/26/04 

Date: Mon, 26 Apr 2004 12:52:54 -0700

Please see comments inline. Note that these answers are strictly about raw
performance. There might be other factors that may cause negative
side-effects, such as scalability issues (for example, a SqlDataReader is
usually very fast, but if you keep it open for a long time it can hurt
scalability of an application that has multiple concurrent clients). 

-- 
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights.
"B0nj" <anonymous@discussions.microsoft.com> wrote in message
news:4867C257-DE3F-40B1-A66C-981CAC0729F0@microsoft.com...
> Could someone tell me which is faster, or no difference, between:
>
> Which is the fastest out of the following two methods???
> Reading a recordset using SqlDataAdapter.Fill(a_dataset) / reading a
recordset using SqlDataReader ??
SqlDataAdapter.Fill() uses a SqlDataReader under the covers, so adapter.fill
is "reader time + adapter work time". So SqlDataReader is faster, as long as
you consume it right away.
>
> Which is the fastest out of the following two methods???
> Reading a recordset using SqlDataAdapter.Fill(a_dataset) / reading a
recordset using SqlDataReader *after* doing an ExecuteNonQuery on the same
command (after changing an in parameter's value to enable the SQL to be
optimized) in to glean output parameters (which need to be used during the
reading of the records)
If what you need is some information up-front, you can pack that information
as a single-row result-set and send it to the client and the first result
from your stored-proc, and then send the actual resultset. For example (call
it "pseudo-TSQL" :):
declare @a int
declare @b int
set @a = get_value_for_a
set @b = get_value_for_b
-- send a couple of scalar values first as a single-row resultset
select @a, @b
-- now send the real resultset(s)
select c1, c2, c3, ... FROM mytable ....
In the client you now need only 1 call to ExecuteReader. The first result
will be the single-row scalars table, and the second (after calling
NextResult on the reader) will be your real resultset.
Note that a resultset is more expensive than output parameters, but if you
save a round-trip by using it then it's a good deal.
>
> Which of the following three is faster, or none at all???
> Accessing fields using dr.GetInt32(0) / dr["fieldname"] / or dr[0] ??
GetInt32(0)

Relevant Pages

  • Re: recordset not opening even though query analyzer returns resul
    ... I'm convinced that VB can execute this complex SP. ... > only way i could get an earlier version of it to return in a recordset. ... > table into a cursor and add up th evalues of the ccjs based on values in ... >> execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • Re: Missing Resultset When Calling an Stored Procedure w/a Nested
    ... we tried advancing to the next recordset by using the NextRecordSet ... program only returned the nested stored procedure call resultset and failed ...
    (microsoft.public.data.ado)
  • Re: SQL Stored Proc - ASP Page - Return Value
    ... the txtPaymentAmount field is a FLOAT data type and its returning a "Error ... >> that created the stored proc for me does not want to use an OUTPUT ... > This IS inefficient - why bring back a resultset for a single value? ... then you need to use the NextRecordset method of the recordset ...
    (microsoft.public.inetserver.asp.general)
  • Re: recordset not opening even though query analyzer returns resul
    ... only way i could get an earlier version of it to return in a recordset. ... table into a cursor and add up th evalues of the ccjs based on values in the ... When i test the sp in query analyzer I get the results I expect, ... > execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • Re: Datareader, dataset, else?
    ... A DataReader is most closely analogous to a Read-Only/Forward-Only Recordset ... > I'm writing my first real asp.net app at my job, ... > seems like I have to choose between using a SQldatareader or a dataset. ...
    (microsoft.public.dotnet.framework.aspnet)