DataReader efficiency

  • Thread starter Thread starter Ken Pinard
  • Start date Start date
K

Ken Pinard

I noticed that the datareader has a way to reference returned fields by
index or name. Is using the name parameter very inefficient?

It seems that if the stored procedure is modified to have the fields in a
different order, then using the name as an index would avoid problems.

What are other peoples thoughts and experiences on this?

Thank you,
Ken
 
Yes, it's slower. You can use the GetOrdinal method of a given field so you
can assign it a clear name but get the benefit of using an index. Bill
Vaughn has an even better idea though...use an Enum and give the enum values
clear names. Then you get the clarity of using names with the speed of using
integers.

The flip side of the problem you mention is what if someone changes the
Alias of a column? If you use nominal references it will cause an error as
well.
 
Without knowing *how much* slower access-by-name is, I don't think it's a
good idea to write code that is more brittle (because of dependency on field
order) to achieve an unknown speedup. For many applications the time spent
in the db call is going to swamp the time accessing columns on the client
anyway. What's the XP saying, optimize later?

If someone changes a field name in db, then he or she better be searching
the code too! But after changing the field order, it would be very
difficult to search client code for such a dependency.

I've been known to create an embedded class named Field with only const
string members that are named like CustomerID, OrderID, etc, and have values
"customer_id", "order_id", etc. This really isolates the dependencies on db
field names, and minimizes opportunities for misspellings. Almost as good
as typed datasets.

Brad Williams
 
Actually, I was referring to changing the alias but changing the order of
existing aliases.

Despite all the warnings and policies, someone always wants to make the
procedure look orderly.

Using an Enum has the same problem as what I was looking at, although it
does make the code clearer and easier to read.

Using the getOrdinal method I assume has a high overhead as well, But if you
could use that result over many times it would be very useful.

I wonder if using name positions is a lot slower or if it is noticable?
Again, probably a function of quantity of records. I defently like the idea
of using the Enums and will start there.

Thank you,

Ken
 
I agree with you on the issue of speed. I wish I had time to run good timing
tests.

I really like your idea of the embedded class for the db names. It looks
like it could be useful.

Thank you,

Ken
 
Ken:

GetOrdinal will be faster and will effectively solve the moving around issue
as long as the alias is the same. The Enum method is more efficient in that
it doesn't have the overhead of finding it in the first place. Where I
work, we don't change the positions in procs very often and I originally
used GetOrdinal...when I read Vaughn's idea, I really liked it because it's
clever as heck and we tend to change the Alias of something more frequently
than the positions of columns (neither of which is very frequent). However,
it's a tradeoff.

DataReaders are fast so of you are querying 100 records you probably won't
notice the difference. However, databases tend to grow and what used to
perform well often doesn't after a zillion more records.

The truth of the matter is that using appropriate indexing strategies,
keeping them contiguous, avoiding redundancy etc will probably have a much
larger impact on performance than this particular issue. I guess it's a
judgement call: is somewhat better speed offset by errors caused by
changes? It depends on how often those changes will cause errors and how
critical performance is. It also depends on how changes are implemented.
Does everyone know when someone makes a change? How changes are communicated
will obviuosly have an impact. I will say though that if people shift things
around a lot, you will probably run into troubles with positions in
Databound controls, grids in particular.

HTH,

Bill
 
Hello Brad
If someone changes a field name in db, then he or she better be searching
the code too!
Click to expand...

The changing of "Field" names in a production database is absolutely insane.
Any DBA worth her or his paycheck would not allow such cavalier handling of
their databases. And changing Database names in a database that is being
used for development is also a very poor practice. The Database underlying
any serious development should alrady have its entities and attributes
settled in on and changes should only come after serious research into the
effects of such changes.
But after changing the field order, it would be very
difficult to search client code for such a dependency.
Click to expand...

heh...even worse. Unless the DBA or Database designer is trying to tweak
the most space possible out of a data page by re-arranging different field
types there is no valid reason I can think of to change the order of the
Attributes. The damage that can be done downstream by changing attribute
names or ordinal positions is just too great.

If you have need to change names or ordinal positions then do it in your SQL
statements or Stored Procedures.

Gaaaaaawd....


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
If you have need to change names or ordinal positions then do it in your
SQL
statements or Stored Procedures.
Click to expand...

That's the assumption of this entire thread, sorry if use of "field"
confused you. And the fact that production db is not supposed to change
doesn't mean code shouldn't be robust to such changes.

Still I am curious how you think no dev database is reasonably expected to
ever change. If it's dev, isn't it in development? Change is unacceptable?
(How's that for the new software dev motto for 2004!) I would like to work
on these projects which are designed perfectly the first time and
requirements do not change two weeks later.

Brad Williams
 
If I may chime in.... I totally agree with Ibrahim. In the context of what
we are talking about, here's what Ken is asking:

a Stored Proc is created, SELECT VarcharFIELD1, IntField2 From SomeTable.
The code uses indexes to read the query....myDataReader.GetString(0)
myDataReader.GetInt(1). Then, some programmer decides he doesn't like
mixing the fields so he wants all int fields to come first, then all varchar
fields so the positions change. Our query is now broken.

Understand that this change has NO effect on the logic of the query. It's
solely a formatting issue that some programmer doesn't look like. In my
previous life as an Oracle DBA, this wouldn't happen because I wouldn't
allow it. If it's your schema, do what you want in test, but in Production,
it's My object. I know that sounds petty, but as a DBA where it's your job
to keep things in check, no one should be able to create/destroy/change db
objects without going through you. So you change the postions in test, I
see the changes in Production and I come to you saying "That's not a good
enough reason" I'd at least ask for written indemnification of any adverse
consequences.

It's the same reason I won't allow people to run the debugger on my current
Sql Server DB. I'm the de facto DBA, but my main role is programming. And
our current policy is if it's your proc, it's your code that references it
and if you change it, you notify everyone or it's your fault if it breaks.
It works great. In two years, only 3 procs have had field changes. That's
not to say that we haven't added fields, but that won't break your logic in
the context of which we are speaking.

As a programmer, I'm the first to acknowledge that programmers should have
very limited privileges (ideally only execute on stored procs) on the
production DBA. Programmers and DBA's should communicate regularly and
always advise when changes are made.

Let me ask you this.... If you wrote some code that had redundant data, and
as a DBA, I didn't like it so I created another table or two and normalized
it, and I didn't tell you, you'd probably be pissed about it. Rightfully
so. Same works in reverse. And honestly, production db's aren't the places
for freestyling.

Just my two cents, but I totally agree with Ibrahim.

Cheers,

Bill
 
I do agree as well, the problem is I am a lowly contractor. My control of
the database is limited to the sanity of the next DBA at the company. I have
already come across those few who decide they need improve, learn, correct
the database design. Then the "FIX" something they preceive to be wrong. Of
course, the software breaks and guess who's fault that is. The error occurs
in the application not on the database server. This may sound silly to all
of us, but ...

My intent is not to make it easier to modify the structure of a Database.
But to protect my development from the "GOOD" intentions of some unknown or
uncontrollable DBA (We had a new recruit with a wonderful 4.0 from a great
college correct the spelling of all the field names. Now that was fun.)

In a development environment the safety is only as good as the management. I
currently see a need to watch my back.

I believe in good Database design and control stratagies. I also believe in
looking out for the best approach to protect my applications. So, I look to
increase the likelyhood that my application will not fail without decreasing
efficiency to greatly.

Ken
 
Thank you for the insight. I don't expect the aliases to change. I really
don't expect the ordinals to change. But It is much easier to read when
names are present and not a number.

I will look into this GetORdinal command. Even if I use the Enums at least
it is obvious which fields go in which order.

Thank you again,

Ken
 
Hello Brad
Still I am curious how you think no dev database is reasonably expected to
ever change. If it's dev, isn't it in development? Change is unacceptable?
(How's that for the new software dev motto for 2004!) I would like to work
on these projects which are designed perfectly the first time and
requirements do not change two weeks later.
Click to expand...

Perhaps where the development database and the code project live in an
isolated environment they can dictate to the external world what will be
delivered, then the development database's structure can be fluid according
to the whims of the developers. My experience's have shown that projects
have a much better chance of successfully meeting their objectives when
changes to specifications are minimized once the coding begins. If changes
become neccesary later on in the project, they become very expensive in
terms of productivity. That's the reality. Yes, requirements do change.
And those changes can get out of hand and destroy a project if you do not
have a procedure to qualify those requirements changes. I love change
control committees that are populated by bean counters who understand the
ramifications of downstream changes. They can get real stone-like when sales
dweebs or upper management has just go to have this thing-a-ma-jig added to
the project.

Database's reflect the business needs of their owners. Even when designing
a database from scratch in an all new business reality a well thought out
set of requirements, use cases, and a good understanding of the business
processes that the database is going to have to service should provide a
solid DB Model that requires little in the way of Entity modifications.
Maybe I belong in the ranks of the Anal Retentive DBA/Project Manager Union
but I believe in very strict change control by the time a project has
entered into the coding phase. (Soap box breaks...Ibrahim falls of of
it...calms down...looks human again...regains focus..moves on)

Now, to the original discussion; as Bill Ryan mentioned, one of the best
tools to use to maintain the retrieval of field data in a rowset is the
Enumerator. The use of the Enumerator is immune to field name changes and
(gulp)changing of ordinal positions. Here's an example of why:

"SELECT Firstname, Last Name, DOB, SSN, Age FROM Persons WHERE..."

Public Enum PersonData
FirstName = 0
LastName = 1
DOB = 2
Age = 3
End Enum

txtLastName.Text = MyTable.Items(PersonData.LastName)

simple, fast

Query changes to:

"SELECT FirstName, MiddleName, Last Name as FamilyName, DOB, SSN, Age FROM
Persons WHERE..."

uh oh...change in ordinal position and change in Field name. The only
change you have to make is in the Enumerator. The rest of your code does
not care about the order or naming conventions of your Rowset, it only needs
to have the right lookup index provided by the enumerator. IMHO Enums
provide one of the best protections from downstream changes to data
structures.

Public Enum PersonData
FirstName = 0
MiddleName=1
LastName = 2
DOB = 2
Age = 3
End Enum


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
Back
Top