p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: RE: Database application architecture


Message #1 by Arthur Maloney <ArthurMaloney@s...> on Sat, 1 Jun 2002 08:36:02 +0100
Hello Randy,

Friday, May 31, 2002, 2:42:19 AM, you wrote:

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

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

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

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

RC> If I have misinterpreted your question, please restate.

RC> R

>> Hello Randy,

RC> Read your comments with interest.

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

RC> Do you think it would be quicker to run if

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

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

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




Thank you for your comments my muddled mind is beginning to clear.

Consider
        1.  Access 2k .mdb or .mde front end on user's machine.   Access 2k
        backend on a network(mainly for backup purposes).

        2. Access 2k .adp or ade front end on user machine. Srvr 2k
        .mdf/.ldf   as backend.

        If MSDE has been installed on the user's machine (NOT on the
        network server). Do you think that 2 would be quicker than 1 for a
        backend with 0.9 Million records?


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

Message #2 by "Randy Cornish" <rlcornish@c...> on Sun, 2 Jun 2002 00:31:03
I'm not really all that familiar with how adp's and ade's work in 
detail.  I mostly use Access as a datastore, and almost never with code 
inside it.  I use VB for that.

I'm not quite following your question either.  Bottom line, anytime 
Access is NOT on the user's machine, stuff has to travel over the LAN to 
be executed as it is my opinion that CPU cycles do not get used on the 
server where the MDB/MDE/ADP/ADE is stored.

Placing MSDE on the user's machine won't do much because I assume that 
the MDB would still be remote.  You need MSDE and the data to be on the 
same machine (and the data needs to be IN MSDE or SQL Server, not Access) 
to get the performance improvement once you go over a certain number of 
records and/or users.

R
  
> Hello Randy,

Thank you for your comments my muddled mind is beginning to clear.

Consider
        1.  Access 2k .mdb or .mde front end on user's machine.   Access 
2k
        backend on a network(mainly for backup purposes).

        2. Access 2k .adp or ade front end on user machine. Srvr 2k
        .mdf/.ldf   as backend.

        If MSDE has been installed on the user's machine (NOT on the
        network server). Do you think that 2 would be quicker than 1 for a
        backend with 0.9 Million records?


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


  Return to Index