MS Best Practice - Load subset of a single Row

[Jim Brandley]

Can someone please tell me the recommended best practice for loading a
subset of the columns from a single row of a database table?

Users can make fields come and go from the form, so a stored procedure
is not an option as I see it.

Using a datareader opens a cursor, which seems an inefficient way to
get one row, but I do not see an alternative. I tried ExecuteNonQuery
with a Select statement, but it didn't seem to like selects at all.
ORA-01036 - invalid parameter name/number.

Any help would be appreciated.

 

[Cor Ligthert]

Jim,

The smallest part you can fill is a datatable.

However when it has the "where uniqueidentifier " of one row it is not more
than one datarow.

I hope this helps,

Cor

 

[Cor Ligthert]

Jim,

And when you don't want that, than there is not a best practise by reading
all the columns and than selecting. I would than create a special select.

Rotten done you can of course use a datareader and skip some columns.

Just my thought,

Cor

 

[Jim Brandley]

I don't really need a datatable for this load - just a few column
values to map into html. I'm just looking for a way to avoid opening a
cursor.

 

[Sahil Malik [MVP]]

Jim,

I'd stay away from a cursor for this.

There are 4 ways (maybe more) of doing this -

a) If you can get away with it - datatable fill and do dataviews on it. This
might be a better option if you need to work with the same data over and
over again.
b) datatable fill with a dynamic sql - If what you are doing is a display
only/readonly opeartion - then this is an overkill.
c) DataReader with a dynamic sql - okay option -
d) You can create a databindable datareader (though asp.net no big deal) -
as shown here -
http://www.codebetter.com/blogs/sahil.malik/archive/2004/12/11/36078.aspx .
You could then find what columns you are interested in, and view only those
in the arraylist's contents (DbDataRecord).

d -- In my head is the best option if you donot have any Huge columns to
fetch.
c and b suffer from the fact that you donot have query exec. plans cached
because the query changes at every request (possibly).
a - has a plus side to it that on repeat fetches you don't have to actually
do a fetch.

So the best solution - depends on your situation . Hope this helped.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Jim Brandley]

Are you saying that opening a datareader or filling a datatable do not
create cursors on the database server? I do not see how those functions
could be performed without a cursor.

I am constructing the select statement dynamically based on the content
of the request, and generating input parameters to match. Some of the
tables I have to query have 100 - 200 columns, and I do not want to
retrieve all for the (generally small) subset the users require. These
forms do not change frequently, once or twice while the users configure
the package to suit their needs, thereafter only as their business
rules and requirements change. The prepared statements should remain in
the db server cache most of the time, because I always generate the
same select. The returned data lives on the web server only for a short
time, and is likely different content (same columns but different
different rows) for each session.

 

[Stephany Young]

And you think that a DataAdapter Fill is not using a cursor? Of course it
is.

Given a carefully crafted SQl statement, returning only the columns you
specifically request and as you stated earlier, a single row, how many
milliseconds is it going to take to process the results from a DataReader?

I can't see how processing a single row with a small number of columns is
going to have an adverse impact on the database server to the extent that
you are going to have a DBA spitting his dummy.

 

[Sahil Malik [MVP]]

I meant - stay away from writing your own cursor.

Anyway, 100-200 columns in a table .. EEEEEEEEEEEEEEEEEEEE !!!!

But I'm gonna try and stay out of optimizing your database.

If you are sure that the query structure doesn't change too often, I say use
dynamic SQL to fetch the data you need.
How much data are we talking? (Rows?)

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Guest]

I agree that dynamic SQL to get your data is the way i would go in this
situation. You could also consider using FOR XML to return the data, but
frankly I would stick with grabbing the row via a DataTable or a DataReader
and test your performance.

John Papa
http://codebetter.com/blogs/john.papa