Network performance issue 1

[qwertyjjj]

I have designed an Access DB with various forms to display data populated by queries. This runs fairly efficiently when on the same system.
As soon as I try to split the DB into a front and back end and place the data part in a network folder location (across a WAN) the performance is incredibly slow.

The strange thing is that when testing the queries out on SQL server (just as a test), they run quickly and the data can't be more than a few kb in size.

As it was explained to me the other day:
"Access is an file oriented database. There is no client-server code, so all data manipulations are done on client side anyway. Access has to load data across network."

This sounds to me like Access would be loading all 20Mb of data across the WAN and processing it on the client end rather than running the query at the back end first and only sending through 10kb of data.
Is this true?

 

[BNPMike]

Your original question was:

As it was explained to me the other day:
"Access is an file oriented database. There is no client-server code, so all data manipulations are done on client side anyway. Access has to load data across network."

This sounds to me like Access would be loading all 20Mb of data across the WAN and processing it on the client end rather than running the query at the back end first and only sending through 10kb of data.
Is this true?

The answer in your case is an emphatic 'yes'.

Everything was like this in the old days. What you had to do was run batch jobs that build the views you want at leisure (eg overnight). Then you access pre-digested tables during the day. I think you need to do that here.

 

[MichaelRed]

" ... answer in your case is ... "

seems to be a contradiction:

" ... Access does not transfer all data across the network ... "

???????????????????????




MichaelRed

 

[BNPMike]

Michael:

" For each table referenced in a Jet query, the "BE" transfers the so-called cartesian join. This is every record in every table joined to every record in every other table. As a simplistic example, take three tables (tblA, tblB, tblC). Assume they have (respectively 10, 100, and 1000 records). Further take (the unlikely position that the record length of all three tables is 200 Bytes). Ignoring the overhead of joins and indicies, we could roughly calculate some transfers:

I will forbear actually doing the math, both because it is trivial and it is depressing!
"

This is simply wrong. You can't take a simplistic view of a subject that is actually subtle. There's a lot of stuff on the Microsoft site that describes how Jet works. Start with "Rushmore" as a good intro to the sophistication.

In the case of the subject of this thread, it happens that Jet probably does transfer complete table scans across the network but I doubt very much it uses a cartesian product. Could you tell us where you got that idea from? I'm not saying you're wrong but it seems an odd strategy for Jet to use.

I guess the moral is Access can easily get into transferring large amounts of data if you are not quite careful about what you are doing. Using a server database will mask such inefficiencies but users should also consider tuning their applications as stress on an individual Access dialogue probably will mean problems elsewhere in the application design.

 

[MichaelRed]

It has been a while since I first "heard" the concept, and it DID seem unlikely at the time. After more than a decade of use of Ms. Access (with and with out Jet), it now seems not just likely - but downright undisputable. I have seen numerous references to Jet as a "File Server", with the accompanying explaniation the it DOES NOT process SQL, but provides the data to the .mdb application for processing. At least one such reference to Jest as a file server is in "Access Developer's Handbook". I believe I have seen similar references in Ms. as well, but do not recall a specific page/location.




MichaelRed

 

[Ed2020]

MichaelRed,

Surely then Jet will return all data from all queries used in the table but *not* as a cartesian product?

If we have a query using two tables, each containing a thousand records, then Jet can either return two sets of a thousand records for local processing, or it can return a cartesian product (a million records). The latter option just doesn't make sense. I can see no benefits to returning such a significantly larger recordset.

Ed Metcalfe.

Please do not feed the trolls.....

 

[BNPMike]

Jet certainly is file server based. The issue is how intelligent it is at calling for the minimum amount of data to satisfy a query. In some cases you can get almost as little data crossing the network as with say MS SQL Server. You could get a just a few blocks of result set plus the overhead of some index blocks.

Jet seems to work very much like any other relational database except that the processing is done across a network rather than across an internal bus. What I'm trying to say is don't let that go to your head. Running the query mentioned above is going to create the same problem for a server database. OK so it's latency is much lower but if it's running other threads, it may have noticeably less memory and cpu compared to your Access client. Also networks are pretty fast even if you have to pull over all the data in your target tables.

I do think, as a developer, you have at some point to take responsibility for the "runability" of your code rather than just throw technology at it. The SQL above is clearly capable of tuning. Also I would suggest that any given screen should not have vast amounts of data on it. What's the point? Jet rather than hindering you, is actually constraining you to do sensible things.