HLP: Multi-Queries + Doing it Right

[Mr. B]

Still rather new to DB and ADO (but slowly gaining ground)... I've two
questions...


First deals with Filtering an OleDbDataAdaptor... I've such a beast and I've a
single query for it:

SELECT Employee, ID, Period_End_Date, Units, [Date] FROM tblTimeEntry WHERE
(Employee LIKE ? + '%')

Simple enough. And I can do my FILL via something like:

Dim strUserID As String ' User Name Filter
strUserID = UCase("feu001")

daTimberline.SelectCommand.Parameters(0).Value = strUserID
daTimberline.Fill(dsTimberline.tblTimeEntry)

What I'd like to know is HOW to do a query with TWO (2) queries. Say User
Name (as above) _and_ something else (say User ID Number)?

I 'think' in the Select's WHERE is where I do an 'ABC and 123' kinda query
(true?)... but I'm not sure. Then how on the SelectCommand line do I query
both items?


Second Item deals with 'whats the proper way' (or doing it the right way)...

My application deals with 2 Access Db's. My question is: Should one try to
keep the number of DataSets down? In other words, load the two DB's fully
(one per DS)... and deal with them as best as you can. Or create the DataSets
as much as you want as you need them?

I'm concerned that having too many DataSets will slow down loading performance
(right now I've 4 of them... and hope to reduce to 3 once I figure things out.

Thanks in advance...

Bruce

 

[William Ryan]

Remember first that the dataadapter isn't actually doing any filtering....
In the first statement, that's just a standard restriction. Afterward, you
can do something like dsTimberline.tblTimeEntry.Select(MyFirstFilter =
'someValue' AND MySecondValue = 'someOtherValue').

Typically you'd declare an array of DataRow objects and then set that array
= to the above statemet.

Alot and as much as you need them are relative, so it's hard to tell you in
absolute terms. however, as a general strategy, you don't want to pull over
data you don't need. Most of the time, you'll get the data at some point,
play with it and then send it back. Copying the entire databases probably is
something you want to avoid unless you are totally positive that they are
small and are going to stay that way. Even then it's probably overkill to
grab everything. Another thing on DataSets..Remember that DataSets are
comprised of 0 or more DataTables. So think of the DataSet as a desktop
version of Database. You can use DataRelation objects for instace to
relate your data...so your run of the mill app probably doesn't have a whole
lot of Datasets. I have an app for instance that has a bunch of lookup
tables and some other data..so I think I have 9 tables sitting one DataSet.
I pull over what I need at the beginning and then submit updates at certain
intervals. With all that said, it's not the number of Datasets per se
that's going to kill your performance (but you don't want to declare stuff
you don't need as a general rule) but the size of them. An app with 10
Datasets with 5 DataTables each, all of who's tables have no more than 50
rows is going to be a lot more responsive than say an App with 2 DataSets, 5
Tables each and 20,000 records each. Ultimately those datasets are holding
an in memory representation of your data, so limiting data is probably the
goal. If you have two databases though, you can probably can do whatever
you need with only 2 Datasets but that depends again on how everything is
going to be used.

Keep in mind that these are general suggestions. THings change over time
and it's not uncommon to see an approach that was responsive no longer be so
whenever the database starts growing. If performance is going to be an
issue, I'd definitely spend some time and see how long my queries would take
and get really familiar with the disconnected methodology. Also remember
that you can use a DataReader in many instances for things like filling
combo boxes and listboxes. They are going to perform faster b/c they
operate in a connected context so you may want to mix it up a little.

HTH,

Bill

Still rather new to DB and ADO (but slowly gaining ground)... I've two
questions...


First deals with Filtering an OleDbDataAdaptor... I've such a beast and I've a
single query for it:

SELECT Employee, ID, Period_End_Date, Units, [Date] FROM tblTimeEntry WHERE
(Employee LIKE ? + '%')

Simple enough. And I can do my FILL via something like:

Dim strUserID As String ' User Name Filter
strUserID = UCase("feu001")

daTimberline.SelectCommand.Parameters(0).Value = strUserID
daTimberline.Fill(dsTimberline.tblTimeEntry)

What I'd like to know is HOW to do a query with TWO (2) queries. Say User
Name (as above) _and_ something else (say User ID Number)?

I 'think' in the Select's WHERE is where I do an 'ABC and 123' kinda query
(true?)... but I'm not sure. Then how on the SelectCommand line do I query
both items?


Second Item deals with 'whats the proper way' (or doing it the right way)...

My application deals with 2 Access Db's. My question is: Should one try to
keep the number of DataSets down? In other words, load the two DB's fully
(one per DS)... and deal with them as best as you can. Or create the DataSets
as much as you want as you need them?

I'm concerned that having too many DataSets will slow down loading performance
(right now I've 4 of them... and hope to reduce to 3 once I figure things out.

Thanks in advance...

Bruce

 

[Mr. B]

Typically you'd declare an array of DataRow objects and then set that array
= to the above statemet.

I understand... thanks. I'll play with this.

Keep in mind that these are general suggestions. THings change over time
and it's not uncommon to see an approach that was responsive no longer be so
whenever the database starts growing. If performance is going to be an

I've grasped most of what you've said (just got to read it slower and more
thoughly). But I think I understand what you've suggested.

I realize that things change, etc. But it was the overall 'approach' (like
you've pointed out) which is what I'm looking for. I dont' want to get too
deep in my project only to find that I've created a monster (even though
things work).

Thanks for the feed back!

Regards,

Bruce