HLP: Multple DataSets vs. Speed?

[RamChip]

I've an ADO.net application I've written in VB.net (standard).

This was my first ADO project and I've finished it. But now I'm going back
into it and trying to clean it up.

In my application I read row info from two MS Access files. One file is
read-only info. The other is read and written to.

In the read-only file, there are Five (5) tables I read info from (one table I
read twice from). This all happens in Form_Load.

IF I bypass all these tables, my application starts fast (3-5 seconds). When
they are enabled, it's sometimes over 30-seconds.

I 'suspsect' it is because I'm opening and closing the MDB file 6 times (not a
big file or lots of rows - several hundred).

Is there a 'benifit' to consolidate some (or all) of my current 6 datasets
into ONE main one?

If so, how do I go about it as I've never attempted this before (note that
some DS's need filtering, etc.) OR should I combine only the ones without
filtering?

Some of the DataSets get binded to things like ComboBoxes, etc. So in these
cases, IF I can combine things, how can I bind them then?

NOTE: that I've done all the Connections, DataSets and DataAdapters via the
Wizard. So I've no real idea on how to do this all by Code lines

Here is what my DS's look like... any help/tips/info... much appreciated:

dsMstrCat - No Sorting
**********************
SELECT Category
FROM MASTER_JCM_STANDARD_CATEGORY

dsMstrEmPay - No Sorting
************************
SELECT Employee, Amount, Pay_ID, Pay_Type
FROM MASTER_PRM_EMPLOYEE_PAY
WHERE (Employee LIKE ? + '%')

dsMstrJobs - Sort by JOB
************************
SELECT Description, Job, Status
FROM MASTER_JCM_JOB_1
WHERE (Status LIKE ?)
ORDER BY Job

dsAllJobs - No Sorting
**********************
SELECT Description, Job
FROM MASTER_JCM_JOB_1

dsMstrCode - Sort by COST_CODE
******************************
SELECT Cost_Code, Description, Group_Cost_Code
FROM MASTER_JCM_STANDARD_COST_CODE
WHERE (Group_Cost_Code LIKE ?)
ORDER BY Cost_Code

dsMstrEmploy - Sort by EMPLOYEE
*******************************
SELECT Chargeout_Level, Class, Employee, Employee_Name, Pay_ID
FROM MASTER_PRM_EMPLOYEE
ORDER BY Employee

 

[William Ryan]

Ram:

In most instances, one Dataset is plenty but I don't think that's causing
the performance problem. IF you call the queries from Access are they fast
(I'm guessing so if they just a few hundred rows but those can be slow too.)
I'm just wondering b/c Access sin't exactly a speed demon over a network,
and if it's networked and that's the bottle neck, your choices are limited.

As fara as consolidation

Da1.Fill(myDataSet, "table1");
Da2.Fill(myDataSet, "table2");

etc.

You can attach the tables with a DataRelation object but I'm not sure if you
have these all related.

YOu can reference everything like myDataSet.Tables[0], myDataSet.Tables[1]
etc, to reference each table (You can also use the nominal name in quotes.)

this is a good place to start
http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx but
if you are new to ADO.NEt, you'll probably want to get David Sceppa's
ADO.NET Core Reference and/or Bill Vaughn's Best Practices book

 

[RamChip]

I'm just wondering b/c Access sin't exactly a speed demon over a network,
and if it's networked and that's the bottle neck, your choices are limited.

I understand... but I REM'ed out the DS and Fill lines for these and the App
loaded fast... removed the REM's... slug city.

YOu can reference everything like myDataSet.Tables[0], myDataSet.Tables[1]
etc, to reference each table (You can also use the nominal name in quotes.)

I guess the filters are okay as long as they are in the correct sequence?

if you are new to ADO.NEt, you'll probably want to get David Sceppa's
ADO.NET Core Reference and/or Bill Vaughn's Best Practices book

I've David's book... had many a good nigh sleep with it (joke).

Most of it went over my head as I'm rather new still to ADO. But I'll go
through it again if you think I'll find something useful.

Thanks for the reply.

RamChip

 

[bruce barker]

the odbc, ado and ado.net drivers are a lot slower than the native jet
drivers, so what is fast in Access is dog slow in .net. even in vb6 if you
wanted performance you couldn't use ado.

-- bruce (sqlwork.com)

I'm just wondering b/c Access sin't exactly a speed demon over a network,
and if it's networked and that's the bottle neck, your choices are

limited.

I understand... but I REM'ed out the DS and Fill lines for these and the App
loaded fast... removed the REM's... slug city.

YOu can reference everything like myDataSet.Tables[0], myDataSet.Tables[1]
etc, to reference each table (You can also use the nominal name in

quotes.)

I guess the filters are okay as long as they are in the correct sequence?

if you are new to ADO.NEt, you'll probably want to get David Sceppa's
ADO.NET Core Reference and/or Bill Vaughn's Best Practices book

I've David's book... had many a good nigh sleep with it (joke).

Most of it went over my head as I'm rather new still to ADO. But I'll go
through it again if you think I'll find something useful.

Thanks for the reply.

RamChip

 

[david epsom dot com dot au]

When you establish a new connection to a Jet database, one
of the first things that happens is that Jet Creates a
new file to use in control of the record locking. One of
the last things that happens is that the file is deleted.

The file is in the same folder as the database, and has the
same name as the database, but with the extension .ldb
instead of .mdb

File creation and deletion can be slow, or very slow. One
of the first speed optimisations for a Jet database is to
make sure that this file creation/deletion operation does
not take place more often than necessary.

So one possible speed optimisation for your application
would be to try it with all the data in just one file.

for more general ideas on working with a Jet (Access)
database, look at:
http://www.granite.ab.ca/access/performancefaq.htm

(david)