Dear programmers, 

Could someone give me advice concerning the design of application 
architecture? The situation is as follows:

I am writing a client application in VB6 that allows users to view and 
modify a server-side Access 2000 database (connected through a LAN). The 
app displays each record of a database table as a treeview node. To create 
the treeview structure, I retrieve a recordset from the database and use 
it to make multiple passes. As the number of records may grow to large 
amounts, this process will require an exponentially increasing amount of 
time. Thus, choosing the right architecture is an important issue.

I can imagine a solution to increase performance for large databases: by 
creating a server-side application that responds to a client request, 
calulates the treeview structure, and sends back these data in the form of 
a relatively small collection of objects corresponding with each node. 
This will reduce the amount of data sent across the network, and thus 
increase performance. 

The problem here is that I don?t know how to make a server-app communicate 
with a client-app. 
-	How to make the server-app respond to the client?s request? 
-	How to pass the object collection back to the right client? 
-	Is it possible to include this function in the Access database 
itself ? 
-	Or should the server-app be a *.dll or something ? 

Please excuse me if these are a lot of questions. I would really 
appreciate if someone could suggest a good way to get started on this.
	
Regards,
Sander

Ssander, rather than change the architecture of the application so 
severeley, is it not the case that with an ActiveX connection, if you 
supply a SQL statement as the command you would only get the required data 
coming back through the network anyway and not the entire database?  

If you do need to distribute the application, there is a technology called 
DCOM (which is like COM components (ie dll files etc) only for distributed 
applications.

Hope this helps,

Kind regards, Phillip

> Dear programmers, 

> Could someone give me advice concerning the design of application 
a> rchitecture? The situation is as follows:

> I am writing a client application in VB6 that allows users to view and 
m> odify a server-side Access 2000 database (connected through a LAN). The 
a> pp displays each record of a database table as a treeview node. To 
create 
t> he treeview structure, I retrieve a recordset from the database and use 
i> t to make multiple passes. As the number of records may grow to large 
a> mounts, this process will require an exponentially increasing amount of 
t> ime. Thus, choosing the right architecture is an important issue.

> I can imagine a solution to increase performance for large databases: by 
c> reating a server-side application that responds to a client request, 
c> alulates the treeview structure, and sends back these data in the form 
of 
a>  relatively small collection of objects corresponding with each node. 
T> his will reduce the amount of data sent across the network, and thus 
i> ncrease performance. 

> The problem here is that I don?t know how to make a server-app 
communicate 
w> ith a client-app. 
-> 	How to make the server-app respond to the client?s request? 
-> 	How to pass the object collection back to the right client? 
-> 	Is it possible to include this function in the Access database 
i> tself ? 
-> 	Or should the server-app be a *.dll or something ? 

> Please excuse me if these are a lot of questions. I would really 
a> ppreciate if someone could suggest a good way to get started on this.
	> 
R> egards,
S> ander

I would suggest that the best architectural advice is to use MSDE or SQL 
Server instead of Access.  As the amount of data grows, your app will get 
progressively slower and slower and there is not much you can do to 
optimize it.  Access is a file-based product, not a server-based.  That 
means that ALL SQL processing is done on the client machine.  If you want 
two rows out of 2,000, all 2,000 will go across the LAN and THEN the two 
rows you want will be selected out.

You may get some relief if you use an ActiveX EXE on the server that 
processes the SQL and sends only the results, but DCOM is complicated 
(and on its way out) and cross-machine marshalling is expensive.

I would try and emulate the ideas that are coming out of VB.NET as much 
as possible - web services that dispense XML (or some form of delimited 
ASCII that can be rehydrated on the client), disconnected datasets.

You can abstract most of this away from the main part of your app by 
having your User Interface (your TreeView) talk to business objects which 
know how to manage themselves (persist changes, rollback, ...).  In this 
way, you can abstract off the database access so that any changes to how 
you store data will only affect a small percentage of your app.  This 
allows you to start with Access and grow into a bigger solution at a 
later time.

Use a server DB product (MSDE, SQL Server) for best performance.

R

> Ssander, rather than change the architecture of the application so 
s> evereley, is it not the case that with an ActiveX connection, if you 
s> upply a SQL statement as the command you would only get the required 
data 
c> oming back through the network anyway and not the entire database?  

> If you do need to distribute the application, there is a technology 
called 
D> COM (which is like COM components (ie dll files etc) only for 
distributed 
a> pplications.

> Hope this helps,

> Kind regards, Phillip

> > Dear programmers, 

> > Could someone give me advice concerning the design of application 
a> > rchitecture? The situation is as follows:

> > I am writing a client application in VB6 that allows users to view 
and 
m> > odify a server-side Access 2000 database (connected through a LAN). 
The 
a> > pp displays each record of a database table as a treeview node. To 
c> reate 
t> > he treeview structure, I retrieve a recordset from the database and 
use 
i> > t to make multiple passes. As the number of records may grow to 
large 
a> > mounts, this process will require an exponentially increasing amount 
of 
t> > ime. Thus, choosing the right architecture is an important issue.

> > I can imagine a solution to increase performance for large databases: 
by 
c> > reating a server-side application that responds to a client request, 
c> > alulates the treeview structure, and sends back these data in the 
form 
o> f 
a> >  relatively small collection of objects corresponding with each 
node. 
T> > his will reduce the amount of data sent across the network, and thus 
i> > ncrease performance. 

> > The problem here is that I don?t know how to make a server-app 
c> ommunicate 
w> > ith a client-app. 
-> > 	How to make the server-app respond to the client?s request? 
-> > 	How to pass the object collection back to the right client? 
-> > 	Is it possible to include this function in the Access database 
i> > tself ? 
-> > 	Or should the server-app be a *.dll or something ? 

> > Please excuse me if these are a lot of questions. I would really 
a> > ppreciate if someone could suggest a good way to get started on this.
	> > 
R> > egards,
S> > ander

Writing your front-end in VB6 will allow you to use ADO to communicate with
the back-end database.  SO what was said about all the data moving over the
network to the client before any SQL processing is done is probably not
accurate.  If you were using Access as your front-end connected to another
Access database then there are situations where that would be the case.
However, you will be using pure ADO so the SQL processing will be done by
JET on the server side.  

The first thing you should do is make sure your database is design well
without regard to the front-end display requirements.  I've seen Access
databases with millions of rows perform very well if designed correctly.
How to do that is another topic... normalize...

To make most efficient use of Access as back-end database you'll want to use
stored queries as much as possible.  In ADO these will appear as tables, so
you will still be writing SELECT statements but it will be like this 

"SELECT * FROM qry_GetCustomers WHERE CustID =" & iCustID 

and if you looked at the definition of "qry_GetCustomers" it might join 3
tables or something... keep the complexity on the back-end.

I try to always use class structures in my ADO apps.  I hesitate to call it
object oriented but it is more or less that.  I use a class for each
underlying database table.  For each base-table class:

-> Write get/let methods for each database field (note: not all fields have
both get and let.  Autonumber ID fields would not have a let for example).  

-> Include Boolean variables tracked internally to the class called EXISTING
and DIRTY.  In each property LET procedure, compare the new value to the
existing value and if different, the flag DIRTY is set to true.

Public Property Let PremierFlag(NewVal As Boolean)
    If mPremierFlag <> NewVal Then
        mPremierFlag = NewVal
        mDirty = True
    End If
End Property

-> Write one or more "Find" methods that will accept as parameters the
primary key or alternate keys and will connect to the database and retrieve
the specified record, and populate the object.  If the record is found and
populated correctly, the function returns TRUE. If not found then it returns
FALSE.  The FIND method sets the EXISTING flag to TRUE and DIRTY to FALSE if
the record exists in the database.  Be sure to use the stored queries in the
find procedures.

Public Function FindByID(CustID as integer) as Boolean
Public Function FindBySSN(CustSSN as string) as Boolean
...

-> Write a "NEW" method that sets up a new object that will eventually be
saved to the database.  The NEW method should set EXISTING to FALSE and
DIRTY to FALSE

-> Write "Save" and "Revert" methods for each base-table class.  The SAVE
method checks the DIRTY flag, if the object is not dirty it does not need to
be saved.  Then it checks EXISTING to know if it needs to do an insert or
update.  Using access it may not be efficient to use stored INSERT or UPDATE
queries but doing so will make your code more consistent and easier to
migrate to SQL server one day.  (note - revert is just 'new' and 'find'...)

-> A base-table class may contain collections of objects from related
tables.  I might have a ORDERS class and one of its properties is a
collection of Ordered_items.  Each object in the collection of order-Items
is itself an instance of a base-table class order_item.  Order_item should
have all the features of a base-table class I just described...  To access a
member of the collection have a GET procedure on the parent object that
accepts a parameter

PUBLIC FUNCTION GET_OrderItem (LineNo as integer) as clsOrder_Item
also have an add function
PUBLIC FUNTION ADD_OrderItem (Item as clsOrder_Item) as Integer  (returns
LineNo)


** use a global object to represent your database connection.  Get your DSN
name, establish the connection, and use that global object in all your FIND
and SAVE methods.  That way, if you have to change how you connect to the
database, you only have to change it in one place.  Chances are good you
WILL have to change how you connect to the database... Murphy's Law...


** now that you have a "layer" of code that completely isolates your
database, its time to design your user interface.  it sounds like you
already have a pretty complete idea for this.  Perhaps you could use a
collection of top-level objects as the highest level of the tree.  On each
tree node keep the key of the record, use this key to look up each object in
the collection.  Each of those objects could then contain a collection of
the nodes below it, and so on however deep you need to go.  You should also
track in the treeview a flag that indicates if a particular node has been
expanded already or not -then when the user expands that node, if never
expanded before, go to the database and get the next level of nodes for just
that particular branch.  If that node has been expanded before, no call to
the db is needed since that data for that node is already in memory in a
collection of objects...  This will prevent your app from trying to load all
the data for the entire tree structure all at once.  By linking the
tree-view interface to the base-table classes, the tree-view only needs to
say "this field changed, update it to the object" and "user clicked Save,
call the Save method".  Very clean and neat.

Ok That's all I have time to write right now.  Have fun, plan ahead, and
think about "layers".

David







-----Original Message-----
From: Sander Groffen [mailto:sander.groffen@z...]
Sent: Sunday, May 26, 2002 7:23 AM
To: Access
Subject: [access] Database application architecture


Dear programmers, 

Could someone give me advice concerning the design of application 
architecture? The situation is as follows:

I am writing a client application in VB6 that allows users to view and 
modify a server-side Access 2000 database (connected through a LAN). The 
app displays each record of a database table as a treeview node. To create 
the treeview structure, I retrieve a recordset from the database and use 
it to make multiple passes. As the number of records may grow to large 
amounts, this process will require an exponentially increasing amount of 
time. Thus, choosing the right architecture is an important issue.

I can imagine a solution to increase performance for large databases: by 
creating a server-side application that responds to a client request, 
calulates the treeview structure, and sends back these data in the form of 
a relatively small collection of objects corresponding with each node. 
This will reduce the amount of data sent across the network, and thus 
increase performance. 

The problem here is that I don't know how to make a server-app communicate 
with a client-app. 
-	How to make the server-app respond to the client's request? 
-	How to pass the object collection back to the right client? 
-	Is it possible to include this function in the Access database 
itself ? 
-	Or should the server-app be a *.dll or something ? 

Please excuse me if these are a lot of questions. I would really 
appreciate if someone could suggest a good way to get started on this.
	
Regards,
Sander

Thank you very much for the helpful comments. Especially Dave's comments 
were very valuable to me. Regards, Sander

With all due respect, I disagree that any of the SQL processing will 
occur on the server.  In order to verify that this is still true, I e-
mailed an expert on Access DAO and ADO (over 10 books to his credit).  It 
is still true that it is the LOCAL Jet DLLs that do all the work.  The 
server is nothing more than a big, "slow" disk drive.

The one thing that he DID remind me that I did not put in my first 
posting is that when you query a large table, you get only the INDEXES 
for all the rows, not the actual data.  Then, Jet uses random file access 
and the index to retrieve only the data row(s) it needs.  So, if you have 
500,000 records and your index (or Primary Key) is a Long, you will get 4 
bytes * 500K, or 2MB of data across the LAN before you can fetch your 
target record.

I stand by my earlier post.  Abstract your data access code so that when 
the day comes that Access is too slow, you can move to SQL Server or MSDE 
without tearing your whole application apart.
 
R
 
Writing your front-end in VB6 will allow you to use ADO to communicate 
with
the back-end database.  SO what was said about all the data moving over 
the
network to the client before any SQL processing is done is probably not
accurate.  If you were using Access as your front-end connected to another
Access database then there are situations where that would be the case.
However, you will be using pure ADO so the SQL processing will be done by
JET on the server side.  

The first thing you should do is make sure your database is design well
without regard to the front-end display requirements.  I've seen Access
databases with millions of rows perform very well if designed correctly.
How to do that is another topic... normalize...

To make most efficient use of Access as back-end database you'll want to 
use
stored queries as much as possible.  In ADO these will appear as tables, 
so
you will still be writing SELECT statements but it will be like this 

"SELECT * FROM qry_GetCustomers WHERE CustID =" & iCustID 

and if you looked at the definition of "qry_GetCustomers" it might join 3
tables or something... keep the complexity on the back-end.

I try to always use class structures in my ADO apps.  I hesitate to call 
it
object oriented but it is more or less that.  I use a class for each
underlying database table.  For each base-table class:

-> Write get/let methods for each database field (note: not all fields 
have
both get and let.  Autonumber ID fields would not have a let for 
example).  

-> Include Boolean variables tracked internally to the class called 
EXISTING
and DIRTY.  In each property LET procedure, compare the new value to the
existing value and if different, the flag DIRTY is set to true.

Public Property Let PremierFlag(NewVal As Boolean)
    If mPremierFlag <> NewVal Then
        mPremierFlag = NewVal
        mDirty = True
    End If
End Property

-> Write one or more "Find" methods that will accept as parameters the
primary key or alternate keys and will connect to the database and 
retrieve
the specified record, and populate the object.  If the record is found and
populated correctly, the function returns TRUE. If not found then it 
returns
FALSE.  The FIND method sets the EXISTING flag to TRUE and DIRTY to FALSE 
if
the record exists in the database.  Be sure to use the stored queries in 
the
find procedures.

Public Function FindByID(CustID as integer) as Boolean
Public Function FindBySSN(CustSSN as string) as Boolean
...

-> Write a "NEW" method that sets up a new object that will eventually be
saved to the database.  The NEW method should set EXISTING to FALSE and
DIRTY to FALSE

-> Write "Save" and "Revert" methods for each base-table class.  The SAVE
method checks the DIRTY flag, if the object is not dirty it does not need 
to
be saved.  Then it checks EXISTING to know if it needs to do an insert or
update.  Using access it may not be efficient to use stored INSERT or 
UPDATE
queries but doing so will make your code more consistent and easier to
migrate to SQL server one day.  (note - revert is just 'new' 
and 'find'...)

-> A base-table class may contain collections of objects from related
tables.  I might have a ORDERS class and one of its properties is a
collection of Ordered_items.  Each object in the collection of order-Items
is itself an instance of a base-table class order_item.  Order_item should
have all the features of a base-table class I just described...  To 
access a
member of the collection have a GET procedure on the parent object that
accepts a parameter

PUBLIC FUNCTION GET_OrderItem (LineNo as integer) as clsOrder_Item
also have an add function
PUBLIC FUNTION ADD_OrderItem (Item as clsOrder_Item) as Integer  (returns
LineNo)


** use a global object to represent your database connection.  Get your 
DSN
name, establish the connection, and use that global object in all your 
FIND
and SAVE methods.  That way, if you have to change how you connect to the
database, you only have to change it in one place.  Chances are good you
WILL have to change how you connect to the database... Murphy's Law...


** now that you have a "layer" of code that completely isolates your
database, its time to design your user interface.  it sounds like you
already have a pretty complete idea for this.  Perhaps you could use a
collection of top-level objects as the highest level of the tree.  On each
tree node keep the key of the record, use this key to look up each object 
in
the collection.  Each of those objects could then contain a collection of
the nodes below it, and so on however deep you need to go.  You should 
also
track in the treeview a flag that indicates if a particular node has been
expanded already or not -then when the user expands that node, if never
expanded before, go to the database and get the next level of nodes for 
just
that particular branch.  If that node has been expanded before, no call to
the db is needed since that data for that node is already in memory in a
collection of objects...  This will prevent your app from trying to load 
all
the data for the entire tree structure all at once.  By linking the
tree-view interface to the base-table classes, the tree-view only needs to
say "this field changed, update it to the object" and "user clicked Save,
call the Save method".  Very clean and neat.

Ok That's all I have time to write right now.  Have fun, plan ahead, and
think about "layers".

David







-----Original Message-----
From: Sander Groffen [mailto:sander.groffen@z...]
Sent: Sunday, May 26, 2002 7:23 AM
To: Access
Subject: [access] Database application architecture


Dear programmers, 

Could someone give me advice concerning the design of application 
architecture? The situation is as follows:

I am writing a client application in VB6 that allows users to view and 
modify a server-side Access 2000 database (connected through a LAN). The 
app displays each record of a database table as a treeview node. To 
create 
the treeview structure, I retrieve a recordset from the database and use 
it to make multiple passes. As the number of records may grow to large 
amounts, this process will require an exponentially increasing amount of 
time. Thus, choosing the right architecture is an important issue.

I can imagine a solution to increase performance for large databases: by 
creating a server-side application that responds to a client request, 
calulates the treeview structure, and sends back these data in the form 
of 
a relatively small collection of objects corresponding with each node. 
This will reduce the amount of data sent across the network, and thus 
increase performance. 

The problem here is that I don't know how to make a server-app 
communicate 
with a client-app. 
-	How to make the server-app respond to the client's request? 
-	How to pass the object collection back to the right client? 
-	Is it possible to include this function in the Access database 
itself ? 
-	Or should the server-app be a *.dll or something ? 

Please excuse me if these are a lot of questions. I would really 
appreciate if someone could suggest a good way to get started on this.
	
Regards,
Sander

You are incorrect.  If you have a persisted query, i.e., stored query, in a
backend database on a network the front end Jet drivers do not process the
SQL.  They have no way of knowing what the SQL statement is in the query.
It exists entirely on the backend database.  As far as where the processing
occurs in a query in the front end database or a query created in the code,
it depends on where the cursor is.  I have done quite a bit of work with an
Access database on my local computer, in Southern Calif., downloading major
portions of our corporate Oracle warehouse, in New York.  While optimizing
the process I watched network traffic, cpu load, and memory usage in task
manager.

To download all the data in a table with 200,000 rows if I SELECT * FROM
[TableName] with a server side cursor it uses virtually no memory or cpu
time and returns the recordset in 5-10 seconds with no significant network
usage.  Now when I move through the recordset and append the data to a local
table the traffic starts coming across the network but memory usage does not
grow significantly.

If I try the same thing with a client side cursor memory and network traffic
go through the roof.  In fact there are some tables in the warehouse that I
cannot download with ADO because they exceed 1.5gig of memory usage, even
though I have 2.5gig counting virtual memory, and Access VBA errors out.  In
the case of the client side cursor it is putting the entire recordset into
memory on the local machine when you open it.

I don't know who your friend is who has written the books, but I can say I
have found many books with poorly written code for VB/VBA, even some from
Microsoft.  Things like:

Dim RS as New Recordset

instead of;

Dim RS as ADODB.Recordset

Set RS = New ADODB.Recordset

There are books that don't even declare a variables type, making it a
variant.  Ask your friend how I can have a recordset that is easily 20meg in
size with 200,000 records and move anywhere in it using find without
bringing major portions of the recordset across the network.  Stop and think
about what you said.  Do you thing when you go to a web site that the
database resides on the same machine as the web server.  Not for any major
web site.  How could such a web site sustain 1000s of data accesses a second
if the web server was constantly processing the queries by pulling all that
data across the network.  The web site processes the requests sends the
queries to the data server who processes the queries/stored procedures and
just sends as little data across the network as necessary to satisfy the web
servers request.  The model you prescribed would bring most major web sites
to their knees, Amazon, Dell, Compaq, etc.

|-----Original Message-----
|From: Randy Cornish [mailto:rlcornish@c...]
|Sent: Thursday, May 30, 2002 1:28 AM
|To: Access
|Subject: [access] RE: Database application architecture
|
|
|With all due respect, I disagree that any of the SQL processing will
|occur on the server.  In order to verify that this is still true, I e-
|mailed an expert on Access DAO and ADO (over 10 books to his credit).  It
|is still true that it is the LOCAL Jet DLLs that do all the work.  The
|server is nothing more than a big, "slow" disk drive.
|
|The one thing that he DID remind me that I did not put in my first
|posting is that when you query a large table, you get only the INDEXES
|for all the rows, not the actual data.  Then, Jet uses random file access
|and the index to retrieve only the data row(s) it needs.  So, if you have
|500,000 records and your index (or Primary Key) is a Long, you will get 4
|bytes * 500K, or 2MB of data across the LAN before you can fetch your
|target record.
|
|I stand by my earlier post.  Abstract your data access code so that when
|the day comes that Access is too slow, you can move to SQL Server or MSDE
|without tearing your whole application apart.

Hello Randy,



Read your comments with interest.

I have an Access 97 0.7 million records  100 meg backend it is slow
across a network.

Do you think it would be quicker to run if

   1 Convert backend to Access 2k. On each user's machine have Access 2k front end
   using ADO to connect to backend)

   2 Install MSDE on each user's machine and convert backend to Srvr2k tables

 End client does not want Srvr2k as such installed on a network



-- 
Best regards,
 Arthur                            mailto:ArthurMaloney@s...

Leo

Randy and yourself seem to be talking about completly different scenarios -
 and are both right!

You're describing the working of a proper client-server database, Oracle 
or SQL-Server. In this case the database server does do all the work. If 
you've designed your database properly then network traffic will be 
minimised.

Randy is talking about the file-server model that Access uses when both 
front and back end are Access databases. In this case there is no database 
engine running on the server where the backend database resides. Access 
and JET are unlikely to have been installed on the file server. The only 
database engine available to the user, JET, runs on the users machine. JET 
therefore has to pull all the data across the network before it can 
process a query. Native Access databases doesn't support server side 
cursors, as there is no server to run them - all cursors are client side.

As you say, Amazon, Dell, Compaq, etc. websites would fail extremly 
quickly using this model. Which is why, I hope, they are not using Access 
databases to store their data!


Brian

> You are incorrect.  If you have a persisted query, i.e., stored query, 
in a
backend database on a network the front end Jet drivers do not process the
SQL.  They have no way of knowing what the SQL statement is in the query.
It exists entirely on the backend database.  As far as where the processing
occurs in a query in the front end database or a query created in the code,
it depends on where the cursor is.  I have done quite a bit of work with an
Access database on my local computer, in Southern Calif., downloading major
portions of our corporate Oracle warehouse, in New York.  While optimizing
the process I watched network traffic, cpu load, and memory usage in task
manager.

To download all the data in a table with 200,000 rows if I SELECT * FROM
[TableName] with a server side cursor it uses virtually no memory or cpu
time and returns the recordset in 5-10 seconds with no significant network
usage.  Now when I move through the recordset and append the data to a 
local
table the traffic starts coming across the network but memory usage does 
not
grow significantly.

If I try the same thing with a client side cursor memory and network 
traffic
go through the roof.  In fact there are some tables in the warehouse that I
cannot download with ADO because they exceed 1.5gig of memory usage, even
though I have 2.5gig counting virtual memory, and Access VBA errors out.  
In
the case of the client side cursor it is putting the entire recordset into
memory on the local machine when you open it.

I don't know who your friend is who has written the books, but I can say I
have found many books with poorly written code for VB/VBA, even some from
Microsoft.  Things like:

Dim RS as New Recordset

instead of;

Dim RS as ADODB.Recordset

Set RS = New ADODB.Recordset

There are books that don't even declare a variables type, making it a
variant.  Ask your friend how I can have a recordset that is easily 20meg 
in
size with 200,000 records and move anywhere in it using find without
bringing major portions of the recordset across the network.  Stop and 
think
about what you said.  Do you thing when you go to a web site that the
database resides on the same machine as the web server.  Not for any major
web site.  How could such a web site sustain 1000s of data accesses a 
second
if the web server was constantly processing the queries by pulling all that
data across the network.  The web site processes the requests sends the
queries to the data server who processes the queries/stored procedures and
just sends as little data across the network as necessary to satisfy the 
web
servers request.  The model you prescribed would bring most major web sites
to their knees, Amazon, Dell, Compaq, etc.

Leo,

As posting #9 (Brian) points out, I believe we are talking about two 
different things.  Your example in your posting is an Oracle back-end.  I 
am talking about an Access backend with a VB client (using Jet).  In that 
scenario, Jet has access to the queries and everything (including index 
information) within the Jet object model.  I stand by my statement.  

The original posting (Sander) was a question about using Access as a 
backend server.

I heartily agree that most "real" database servers (SQL Server, Oracle, 
et.al.) do all the SQL processing.  Access is not among that family 
(although this is in no way an attack on Access - it is superb at what it 
does, server up small quantities of data (under 1 million records) to 
small quantities of users (under 10).

Can I assume we are back in alignment or is this issue still open for 
discussion? 

R

> You are incorrect.  If you have a persisted query, i.e., stored query, 
in a
backend database on a network the front end Jet drivers do not process the
SQL.  They have no way of knowing what the SQL statement is in the query.
It exists entirely on the backend database.  As far as where the 
processing
occurs in a query in the front end database or a query created in the 
code,
it depends on where the cursor is.  I have done quite a bit of work with 
an
Access database on my local computer, in Southern Calif., downloading 
major
portions of our corporate Oracle warehouse, in New York.  While optimizing
the process I watched network traffic, cpu load, and memory usage in task
manager.

To download all the data in a table with 200,000 rows if I SELECT * FROM
[TableName] with a server side cursor it uses virtually no memory or cpu
time and returns the recordset in 5-10 seconds with no significant network
usage.  Now when I move through the recordset and append the data to a 
local
table the traffic starts coming across the network but memory usage does 
not
grow significantly.

If I try the same thing with a client side cursor memory and network 
traffic
go through the roof.  In fact there are some tables in the warehouse that 
I
cannot download with ADO because they exceed 1.5gig of memory usage, even
though I have 2.5gig counting virtual memory, and Access VBA errors out.  
In
the case of the client side cursor it is putting the entire recordset into
memory on the local machine when you open it.

I don't know who your friend is who has written the books, but I can say I
have found many books with poorly written code for VB/VBA, even some from
Microsoft.  Things like:

Dim RS as New Recordset

instead of;

Dim RS as ADODB.Recordset

Set RS = New ADODB.Recordset

There are books that don't even declare a variables type, making it a
variant.  Ask your friend how I can have a recordset that is easily 20meg 
in
size with 200,000 records and move anywhere in it using find without
bringing major portions of the recordset across the network.  Stop and 
think
about what you said.  Do you thing when you go to a web site that the
database resides on the same machine as the web server.  Not for any major
web site.  How could such a web site sustain 1000s of data accesses a 
second
if the web server was constantly processing the queries by pulling all 
that
data across the network.  The web site processes the requests sends the
queries to the data server who processes the queries/stored procedures and
just sends as little data across the network as necessary to satisfy the 
web
servers request.  The model you prescribed would bring most major web 
sites
to their knees, Amazon, Dell, Compaq, etc.

|-----Original Message-----
|From: Randy Cornish [mailto:rlcornish@c...]
|Sent: Thursday, May 30, 2002 1:28 AM
|To: Access
|Subject: [access] RE: Database application architecture
|
|
|With all due respect, I disagree that any of the SQL processing will
|occur on the server.  In order to verify that this is still true, I e-
|mailed an expert on Access DAO and ADO (over 10 books to his credit).  It
|is still true that it is the LOCAL Jet DLLs that do all the work.  The
|server is nothing more than a big, "slow" disk drive.
|
|The one thing that he DID remind me that I did not put in my first
|posting is that when you query a large table, you get only the INDEXES
|for all the rows, not the actual data.  Then, Jet uses random file access
|and the index to retrieve only the data row(s) it needs.  So, if you have
|500,000 records and your index (or Primary Key) is a Long, you will get 4
|bytes * 500K, or 2MB of data across the LAN before you can fetch your
|target record.
|
|I stand by my earlier post.  Abstract your data access code so that when
|the day comes that Access is too slow, you can move to SQL Server or MSDE
|without tearing your whole application apart.

Converting to Access 2000 will not really buy you any speed improvement 
that I can think of (mileage may vary).  In fact, it now stores all 
string as double-byte (for Unicode) and could conceivably be slower if 
you move a lot of string data around.

What you could do is audit your use of Indexes and make sure that for any 
query with filter criteria that you are running against indexes.  This 
might provide marginal improvements.  You would have to write some timing 
code to determine that.  Use caution as the second time you run a query, 
you will often get a much quicker return due to the way Jet caches the 
query optimization.  I would run a query 10 times and divide the result 
by 10 to get an average execution time to use as a baseline.

I understand that ADO is actually slower than Jet for Access because Jet 
is optimized for Access.  

You only need to install either MSDE or SQL Server on the server, not the 
client.  Once your data is housed in MSDE/SQLServer2K, you can use ADO to 
query against it and performance will jump dramatically (assuming you 
have laid it out properly).  SQLServer2K is easier since you have a GUI 
interface to administer the database, test stored procedures and monitor 
performance.  SQL Books Online is also excellent.  HOWEVER, SQLServer2000 
requires a LOT more administration effort than "good ol' Access".  It's a 
tradeoff.  You've got to decide what your client has a appetite for and 
what they can handle if you're no longer there (and what you can handle).

If I have misinterpreted your question, please restate.

R

> Hello Randy,

Read your comments with interest.

I have an Access 97 0.7 million records  100 meg backend it is slow
across a network.

Do you think it would be quicker to run if

   1 Convert backend to Access 2k. On each user's machine have Access 2k 
front end
   using ADO to connect to backend)

   2 Install MSDE on each user's machine and convert backend to Srvr2k 
tables

 End client does not want Srvr2k as such installed on a network



-- 
Best regards,
 Arthur                            mailto:ArthurMaloney@s...

We probably are but as soon as I get some free time I want to do some
testing on my local network.  There is no doubt that if a server has no Jet
drivers loaded on it and you are just storing and accessing an .mdb file
there that the processing has to occur on the client, but I want to do some
testing to see if the server does the processing if it has Access installed
on the server.  I'm not sure I can determine that but I'm going to look at
some CPU utilization data accessing a legnthy query on one machine while
running it locally then accessing it from an ADO connection on another
machine where the recordset just opens the persisted query name.  If you are
correct in the second scenerio the CPU load should not spike as high or as
long on the server as when it processes it locally.

|-----Original Message-----
|From: Randy Cornish [mailto:rlcornish@c...]
|Sent: Friday, May 31, 2002 1:29 AM
|To: Access
|Subject: [access] RE: Database application architecture
|
|
|Leo,
|
|As posting #9 (Brian) points out, I believe we are talking about two
|different things.  Your example in your posting is an Oracle back-end.  I
|am talking about an Access backend with a VB client (using Jet).  In that
|scenario, Jet has access to the queries and everything (including index
|information) within the Jet object model.  I stand by my statement.
|
|The original posting (Sander) was a question about using Access as a
|backend server.
|
|I heartily agree that most "real" database servers (SQL Server, Oracle,
|et.al.) do all the SQL processing.  Access is not among that family
|(although this is in no way an attack on Access - it is superb at what it
|does, server up small quantities of data (under 1 million records) to
|small quantities of users (under 10).
|
|Can I assume we are back in alignment or is this issue still open for
|discussion?
|
|R