Fastest db methods?

[Guest]

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 ??

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

Which of the following three is faster, or none at all??
Accessing fields using dr.GetInt32(0) / dr["fieldname"] / or dr[0] ??

 

[William Ryan eMVP]

1) DataReaders fill faster b/c DataADapter.Fill uses a datareader to fill
the datatable, so it's at least the same plus the overhead of building the
table. Depending on the size of the queyr, the difference can be
noticeable. Here's a test run where they did the comparison in multiple
scenarios http://www.devx.com/vb2themax/Article/19887
2) Calling 2 queries, if I understand your questions correctly ,
ExecuteNonQuery + ExcecuteReader vs Da.Fill is almost certianly going to
take mroe effort. I'm not totally sure I understand the scenario though and
if I do, I don't see how it's apples to apples comparison with DataAdapters.
In general though, Output Parameters, as well as InputOutput params are more
efficient than returning whole rowssets or recordsets\

3) dr.GetInt32(0) is the fastest of the method. If you use a Name, then it
needs to be resolved at each pass for each column. This defintiely has some
overhead. There's a GetOrdinan Method of the datareader, but BIll Vaugn
proposes using an Enum instead b/c this will give you the readability of
using a named based lookup (which is what GetOrdinal does for you) without
incurring the overhead of calling getOrdinal(If you use getordinal, remember
to call it only once before your loop. If you call it each time within the
loop, it will actually be slowing b/c it will have to do a lookup just like
the other method does, but is slightly more expensive from what I've been
told. So only call GetOrdinal once for each field. But Vaughn's idea is
very clear, easy to use and the most efficient.. so instead of GetInt32(0)
YOu could ue GetInt32(MyNum.FirstField) where FirstField is the 0th piece fo
the enum.

As you can tell, dr[FieldName] is the second slowest of the method and dr[0]
(I didn't even think that would compile in C#. or in VB.NET with Option
Strict On).
That is the worst possible way. It's not clear what you are trying to do,
uses implicit type conversion and is just ugly.

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 ??

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)

Which of the following three is faster, or none at all???
Accessing fields using dr.GetInt32(0) / dr["fieldname"] / or dr[0] ??

 

[Cor Ligthert]

Hi BOnj,

This kind of questions does me often thing on people from the US, some of
them want to ride in fast cars.

However I thought that in most places was a 60 miles maximum there.

It is not the speed what counts it is the performance.

Cor

 

[William Ryan eMVP]

Bonj:

The only thing I don't understand is where does the executeNonQuery fit in
in the first scenario.

Anyway, if I'm reading it correctly, the second method is probably the most
efficient b/c with one query, you have everythign you need and you can just
iterate. Iteration through the table is very fast (make sure you use
Numeric lookups on the rows insteaed of referencing column names, this could
cause a huge performance difference). The only thing I"d add in is that
performance isn't static. If you read the article I sent you before, the
difference between a reader and datatable is unnoticeable at small sizes
queries. With 100 records, you won't notice any difference. At a
1,000,000,000 you'll notice a tremendous one. So the size defintiely comes
into play here and if you aren't dealing with large records sets (also, do
you only have one column in the query? This certianly simplifies things), I
doubt the difference would be detectable in most cases.I'd say the
datareader would be the fastest but that second query for ExecuteNonQuery
(which admittitedly I don't understand at the moment) would probably be
enough to slow things down enough to favor the second approach.

OK. The problem is:
I have an SQL SP that I want to execute from C#, it returns a recordset

AND two output parameters, the minimum and maximum value. I need to use the
minimum and maximum values with each value as I'm iterating through them.
The problem is not *how* do I use the parameters *with* the values (I can
think of lots of ways), but what is the *fastest and cleanest code*, in
anyone else's opinion?

I've thought of the following options:

1) Have two different sections of the SP, one that gets the output

parameters and one that returns the recordset, and have a bit parameter that
determines which should be done, from .NET, I would then call
ExecuteNonQuery to get the parameters and then after changing the bit field,
call ExecuteReader to get the reader. Like it says in MSDN, the unfortunate
thing with this is that the parameters can't be read till all the rows have
been read. (This would be a bit messy but I could tolerate it if it was the
fastest)

2) Use SqlDataAdapter.Fill(dataset) to fill a dataset

(disconnected-style), the parameters can then be read before looping through
the dataset's table's rows collection (this is what I'm doing at the moment)

3) Read the values into an array first, then get the output parameters,

then loop through the array? (would only want to do this if it won
comfortably, as it would be more code)

4) Something else?

FWIW, the values are being put into Excel and all the values have to be,

say, multiplied by the ratio of the maximum value over the minimum value
(it's not exactly that, but conceptually, and logically as regards the
dataflow, it's essentially something *like* that going on)

 

[Cowboy \(Gregory A. Beamer\)]

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 ??

I have an ex-coworker who was a performance freak. All he was worried about
was performance. His apps were difficult, overall, to maintain, as companies
normally hire advanced devs for initial development and junior devs for
maintenance. I am not faulting his push for pure performance, if the apps
needed it, but most apps are well below the performance bar, even using
DataSets. I do not see making the code more complex for a few milliseconds
of CPU time, unless the extra perf is needed. As such, "which is faster" is
a horrible question in most instances.

Off my soapbox:

Which is faster? SqlDataReader.
Why? Behind the scenes, the DA uses a DataReader to fill the table, so you
are employing more software with the DataAdapter > DataSet (DataTable
actually) method.

DataReaders are great for displaying data. Realize, however, that Whidbey
throws a monkey wrench into the mix, as the default data model requires far
less code and relies heavily on DataSets. Your code will still work
(DataReader or DataSet), but you will move farther away from a system where
junior devs who learn Whidbey .NET can keep up with your code.

DataReaders are great if you are not using the extra FUD with the
DataAdapter model, or you are not creating multiple tables through one
command object. I would also watch data churning on the application (outside
of T-SQL) with a Reader, as the connection is open the entire time you play.
If you have to filter and curse through records (forward and backwards),
Readers suck.

Overall, I would stick with the DataSet model. For a bit of perf, you can
strongly type your datasets. If you truly are running up against the bar and
the boss cannot shell out for a bigger box, move to a DataReader on
non-editable data displays first (reports, for example).

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)

I do not understand running the command twice (once cmd.ExecuteNonQuery and
one cmd.ExecuteReader). Can you explain a real world reason for this?

Overall, I chain output using stored procedures into a DataSet, so I can run
any number of commands in sequence, including the equivalent of
ExecuteNonQuery followed by ExecuteReader.

Which of the following three is faster, or none at all???
Accessing fields using dr.GetInt32(0) / dr["fieldname"] / or dr[0] ??

GetInt32(0)
Why? You are optimizing the data pull by telling the compiler what is
happening. With the other two, you really need to cast out the int to be
safe.

Here is another question. Which is easier to maintain?
dr["fieldname"]
why? Easy. Even if I change the command order, I can still pull the correct
field.

For the most part, I err on the side of maintainability, as I never know who
will inherit my code. You can always improve perf later, but you will have a
hard time making an app more maintainable. You will also find the CIO will
allocate time to make an app perf a bit better, if needed, as it is a
business need. Telling him you need to reduce perf a bit to make it more
maintainable is a hard sell.

Back on soapbox
------------------
For the record, maintainability generally costs a company more, in the long
run, than perf. Want proof?

Entry level .NET dev $40k
Mid level .NET dev $65k
Senior level .NET dev $85k

If I make an extremely maintainable solution, an entry level dev should be
able to maintain most of the code. This saves the company between $25k -
$45k per year for maintenance.

If I need a bit more perf, another 1U server costs about $10k (maybe $20k
for a heavily hit app) and a bit of time to cluster it in. Updating a proc
is less expensive and adding memory is even cheaper.

The extra $25k - $45k is a yearly expense compounded by the number of
complex applications (logic wise). The server is a one time expense; any
regular upgrade cycle will happen regardless.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Cowboy \(Gregory A. Beamer\)]

Even more important is the concept that performance is one aspect in the
development cycle. While "everyone" seems to err on the side of perf, they
often end up sacrificing security, maintainability and extensibility through
their perf bias.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Guest]

The only thing I don't understand is where does the executeNonQuery fit i
in the first scenario

Don't worry. It was a convoluted plan of mine to effectively have two different 'branches' of the stored procedure, one that would return the parameters, and the other the recordset. Thus, I could then run ExecuteNonQuery, get the parameters, change the flag parameter so it returns a recordset next time, and then call ExecuteReader

Anyway, if I'm reading it correctly, the second method is probably the mos
efficient b/c with one query, you have everythign you need and you can jus
iterate

Good. That's what I wanted to hear

Iteration through the table is very fast (make sure you us
Numeric lookups on the rows insteaed of referencing column names, this coul
cause a huge performance difference)

O.K.... as I thought... presumably you mean like the following...? ( the snippet of my code that puts the data into Excel

foreach(DataRow dr in ds.Tables[0].Rows

xlDataCells[(++row), 1] = dr[0]
xlDataCells[row, 2] = (double)dr[1]; // scale
xlDataCells[row, 3] = (double)dr[2]; // scale


The only thing I"d add in is tha
performance isn't static. If you read the article I sent you before, th
difference between a reader and datatable is unnoticeable at small size
queries. With 100 records, you won't notice any difference. At
1,000,000,000 you'll notice a tremendous one. So the size defintiely come

This particular one only returns 52 records. That's all the data that's needed to be returned to the client, and the likeliness of a much greater amount of records needing to be returned to the client (even as much as this), are low, due to my supreme SQL skills isolating all the heavy work to a suitable machine that acts as the backend (just another SQL server, but dedicated to the task, rather than running a web server aswell)

into play here and if you aren't dealing with large records set

Nope, they're always small. I'm presuming all a DataSet does is reads it into the client's RAM (by client I mean the 'client' of the SQL server, i.e. the web server) and then returns that

(also, d
you only have one column in the query

No, three

This certianly simplifies things)

Why

I doubt the difference would be detectable in most cases.I'd say th
datareader would be the fastest but that second query for ExecuteNonQuer
(which admittitedly I don't understand at the moment) would probably b
enough to slow things down enough to favor the second approach

That makes enough sense, hope I am doing so..

OK. The problem is
I have an SQL SP that I want to execute from C#, it returns a recordse

AND two output parameters, the minimum and maximum value. I need to use th
minimum and maximum values with each value as I'm iterating through them
The problem is not *how* do I use the parameters *with* the values (I ca
think of lots of ways), but what is the *fastest and cleanest code*, i
anyone else's opinionparameters and one that returns the recordset, and have a bit parameter tha
determines which should be done, from .NET, I would then cal
ExecuteNonQuery to get the parameters and then after changing the bit field
call ExecuteReader to get the reader. Like it says in MSDN, the unfortunat
thing with this is that the parameters can't be read till all the rows hav
been read. (This would be a bit messy but I could tolerate it if it was th
fastest(disconnected-style), the parameters can then be read before looping throug
the dataset's table's rows collection (this is what I'm doing at the momentthen loop through the array? (would only want to do this if it won
comfortably, as it would be more code)say, multiplied by the ratio of the maximum value over the minimum value
(it's not exactly that, but conceptually, and logically as regards the
dataflow, it's essentially something *like* that going on)

 

[Guest]

Interesting... see inlin

----- Cowboy (Gregory A. Beamer) wrote: ----

Could someone tell me which is faster, or no difference, between
Reading a recordset using SqlDataAdapter.Fill(a_dataset) / reading

recordset using SqlDataReader ?

I have an ex-coworker who was a performance freak. All he was worried abou
was performance. His apps were difficult, overall, to maintain, as companie
normally hire advanced devs for initial development and junior devs fo
maintenance. I am not faulting his push for pure performance, if the app
needed it, but most apps are well below the performance bar, even usin
DataSets. I do not see making the code more complex for a few millisecond
of CPU time, unless the extra perf is needed. As such, "which is faster" i
a horrible question in most instances

I agree
I got to be a bit of a 'code speed nutter' a bit ago too. However I realised what road I was going dow
and realised it was one with too much effort for too little gain, so I made a conscious effor
to always spend a bit of time thinking about how to strike the balance between maintainabilit
and performance, (and other things like development time and genericity). IOW, I make sur
it is at least a certain percentage as fast as it could physically possibly be, and then from ther
make it nice, clean, maintainable code
However, .NET is quite new to me and I always like to at least know which are the faster methods
and what each one is doing under the hood, as in some scenarios it seems there are multipl
ways of achieving the same end


Off my soapbox

Which is faster? SqlDataReader
Why? Behind the scenes, the DA uses a DataReader to fill the table, so yo
are employing more software with the DataAdapter > DataSet (DataTabl
actually) method

This is what I thought. Thanks for confirming it

DataReaders are great for displaying data. Realize, however, that Whidbe
throws a monkey wrench into the mix, as the default data model requires fa
less code and relies heavily on DataSets. Your code will still wor
(DataReader or DataSet), but you will move farther away from a system wher
junior devs who learn Whidbey .NET can keep up with your code

Sod Whidbey - we've no reason to buy Whidbey. We'd still be with VB6 and Interde
if we'd have had licenses for them. But one day, the management found we didn't hav
a license for my copy of VB6, so decided to buy me a nice shiny new .NET 2k3 EA
the license for which the management believe to be valid for VB6..
whether it is or not I don't know, but I'll go along with it! I haven't used VB6 for ages now
Now we've got the license, we'll always have it - it doesn't expire. So however old technolog
it gets - it may not have 2005, -07, -10 etc. technology but at least we'll be able to use it t
build intranet applications. The concept of the thin-client app and the web browser isn'
going away from offices for a LONG time - certainly from ours if there's no reason for it to

There are a main IT department with apparently about 80 developers, not to boast bu
they can't prove themselves like we can due to the need to constantly have scoping meeting
and draw up formal specifications. Hence, our dept are seen to be much better, even thoug
there are probably a few guys down there who are pretty hot at C++ - they build 'wide scale
apps for the field, probably in VC6 or VB6. Thus, we are the first to get .NET because we are thought t
be the best - no one else in the company has got .NET but us.


DataReaders are great if you are not using the extra FUD with th

FUD

For a bit of perf, you ca
strongly type your datasets

That sounds a bit crap to me. It's that old concept that harks back to old days
of having a class for every single recordset the application needs to use. Well I'm sorr
but I haven't got anything like enough time to create a class for each recordset!

If you truly are running up against the bar

Nah. I'm not. It's currently running on the web server of my PC (don't laugh - it's
fine for development and 'showing the bosses my work' purposes), and when (and
probably only when) management are happy with what can be done software wise, they'll
shell out for some new hardware to host it on. I've told them we're having SQL server and IIS
on the same machine, which I'm sure will be fine to start with as there are other SQL boxes to
do the pre-aggregation.

the boss cannot shell out for a bigger box,

They can, and will.
I'm in the lucky position of developing a system that's mainly going to be
used by directors - this has two benefits (for me): (1) there aren't as many of them, so there's not going
to be like 10,000 people hitting my site as soon as it's launched, and (2) they're in a position such
that if they like it, they'll pump money our way.


I do not understand running the command twice (once cmd.ExecuteNonQuery and
one cmd.ExecuteReader). Can you explain a real world reason for this?

See reply to somebody else. Basically I would have only used this if the answer had been
"a DataSet is horribly slow, and will take many times longer than a datareader whatever the number
of records." But it wasn't, as I expected - but I'm still finding my footing with .NET still, and I want to
be sure I'm doing the right thing.

Overall, I chain output using stored procedures into a DataSet, so I can run
any number of commands in sequence, including the equivalent of
ExecuteNonQuery followed by ExecuteReader.

So if I have a stored procedure that returns multiple recordsets, I would use a dataset - as it can
have multiple tables. That makes sense... got any example code so I can see how it works?


GetInt32(0)
Why? You are optimizing the data pull by telling the compiler what is
happening. With the other two, you really need to cast out the int to be
safe.

Here is another question. Which is easier to maintain?
dr["fieldname"]
why? Easy. Even if I change the command order, I can still pull the correct
field.

True, true. Like I say, I try to strike a balance...

For the most part, I err on the side of maintainability, as I never know who
will inherit my code.


For me, 'maintainable' code is code that follows the notion that management see
that I'm capable of maintaining it myself, and more
importantly I can maintain it within a short timeframe,
therefore it's maintainable. I couldn't give a toss about the plight of developers
following in my footsteps, I'm afraid. At work, I only care about two things:
how well our department's systems are working - thus how much efficiency our
department adds to the company and how much brownie points our department gets,
and how well I personally am seen to be, and my own financial gains.

There's no point adding your own morals to your job. It sounds selfish, but you might aswell
follow what's best for yourself only - as whatever the right thing to do is, will dictate that.
If the company recognises that it's in the company's best interest for senior developers to give more
training to junior developers, then they'll either say "train them, or else you'll be out on your ear", or
"train them, and you'll get a bonus". Either way, it's in the senior developer's best interest to then give
training to the junior developers. But not otherwise! Companies have to be run from a central standpoint,
and can't be influenced by people who think they know better than the layman. A corporation has to be
one unit - acting as one controlled, single entity. So you might as well just be like an ant - selfish, but
serving the maximum efficiency to the greater good.

Not criticising you in any way or saying you're any of those things, but it just reminded me recently of a
post from a guy whose users were looking up VBA password crackers on the internet, and he was spitting
feathers because they'd cracked his access database and weren't using his specially designed interface.
AARRRRGGGHHHHH!!!!!! People like that really irritate me. (The access developer that is, not his users!)



Entry level .NET dev $40k <that's me! but I'm only young yet.
Mid level .NET dev $65k
Senior level .NET dev $85k

If I make an extremely maintainable solution, an entry level dev should be
able to maintain most of the code. This saves the company between $25k -
$45k per year for maintenance.

Good for you! I'm not that interested in company profits myself - I leave that to the
accountants!

 

[Cor Ligthert]

Hi Onj,

With the 5 seconds you hit in my opinion the point. 5 seconds is to slow for
a client, there is than a progress bar or whatever needed.

However 0,01 seconds makes also no sense. They are humans, they even do not
noticed it.

Maybe it is an old measurment, however 3 seconds seems to be the limit to be
that a client does not go looking around.

(This is of course with a full submit not with looping though controls on a
screen or a page).

Cor

 

[Pablo Castro [MS]]

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.

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)

 

[Bonj]

mmm, I suppose I could do that.

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.

And presumably a dataset involves another round trip? Or no? If it would
involve minimal processing overhead, then I would use your method, but if it
involved more network traffic then I would use your dual-resultset
methodology. I guess what I was getting at was would it involve more network
traffic, as this would be the bottleneck probably.

 

[Pablo Castro [MS]]

Regarding DataSet, it depends on how you're using it. How are you obtaining
the output parameters in the dataset case? Are you going to the
SelectCommand property and obtaining the output parameters from there? If
so -implying that you're calling Fill once, and not calling ExecuteNonQuery
at any point- then that's only 1 round-trip, but you're buffering all the
resulting data on the client. If you never expect this resultset to be too
large, then the dataset option is probably (*probably* - without knowing all
the details) the best one as it keeps code simple and doesn't have the
overhead of an extra result-set.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

The dataset IS small (less than 100 rows) and since it buffers the data I'm not needing to *explicitly* do an extra round trip to get the output parameters, so I'm going to stick with it - that answer tells me what i wanted to know, ta.

 

[Guest]

I could make a normal progress bar for most operations, then one that subtly goes a little bit faster for the long operations that take 5 seconds, that way it would make people think they were a bit too wired on coffee and impatient and they would *believe* it's taking 3 seconds when it is in fact taking 5...;-