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?

 

[MichaelRed]

more or less. 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!




MichaelRed

 

[fneily]

As MichaelRed stated, that's Jet for you. However, Access does have another engine called MSDE that is a client/server database engine. The data is stored on the server and the queries(views) are ran there too, so only the resultant recordset is returned to the client. It also has the same data integrity procedures as SQL Server such as a tranaction log. You must install MSDE separately from Office. You would be creating "Access Projects". If you know how to build databases using Jet, you shouldn't have much trouble picking up Project.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmm

last time i checked, you can use plain old (you name it Ms. A. Type front end with MSDE (SQL Server Lite?, SQL Server with a lobotomy? ... ). what is missing is the 'neato' tools which are actually (mostly anyway) part of "MMC" (Microsoft Management Console) which is included (although under a different guise) in many versions of Windows. At least the last time I took any (formal) course (on SQL Server), T-SQL was supplied as part of the course, and melded nicely into ye olde MMC which I already had.

So, while I may be off the track here, I believe that you can even go s far as to use DAO data access to retrieve the "results set" from SQL Server, albeit the learning curve without T_SQL (and the tools) might not be quite as easy as just 'picking it up on the fly'. even though the SQL syntax is similar, the necessary maintenance for SQL Server is a bit more than ye olde and regularly venerable "Compact and Repair" reccomended for Jet. it should also be noted that "Big Brother" has some different data types, includes stored procedures, and really NEEDS the indicies to be re-built regularly (and depending on the database activity [add & delete of records] and often, as thses make a significant difference in performance.

Of course, one can continue to extoll the advantages of the ndustrial strength database engines (over the Jet engine) ad naseum, but it isn't fair to do so without giving some space to the other (downside) aspects. In all but the most trivial organizations / databases, SQL Server et al should have a DATABASE Administrator and small to medium implementations would also have a DATABASE programmer, responsible for generating the actual tables, relationships, queries (stored procedures) ...




MichaelRed

 

[BNPMike]

For each table referenced in a Jet query, the "BE" transfers the so-called cartesian join"

Access does not transfer all data across the network. That would indeed be extremely stupid. It uses various optimising techniques just like SQL Server etc to locate the precise blocks it needs. Obviously to do that, it needs to transfer index pages across the network. After that it may require a tiny amount of blocks even when carrying out joins.

The problem is you can write extraodinarily inefficient SQL etc and the massive power of modern pcs or servers will perform the query in the twinkling of an eye. The network merely exposes how bad your design is.

Another problem with Access is you can easily populate a form with loads of data calls (in controls).

Basically you need to think about each query in each form and identify how that data is being fetched. There certainly can be occasions when Access has to move entire tables across the network and you need to eliminate those as far as possible even to the point of trying to prepare some data in 'batch'.

 

[qwertyjjj]

1/
"The network merely exposes how bad your design is."

Well, yes. Except that even splitting the DB across the network takes a long time. It's only a 20Mb DB FWIW !

2/
Now that it's been mentioned, I do have some controls on the forms that extract data directly from the tables rather than through a recordset. I could look at those and move the data entirely into SQL ?

3/
If I was to use Access as the front end and SQL Server as the back end...how do you set up Access to use SQL server stored procedures, etc.? Is this possible or do all the queries have to be done via Access?
It just sounds like as soon as you move to a SQL Server back end, you may as well be designing an application in C# or VB rather than Access ?

 

[fneily]

Maybe you would like to read/argue with/reference/etc.

http://msdn2.microsoft.com/en-us/library/ms811092.aspx

 

[qwertyjjj]

Thanks for that.
A few questions though...
When you migrate everything to SQL Server (under MSDE) there are 2 options:
link tables
make ADP file (client server)

If you just link the files, where are the queries processed? On the client or at the SQL server end.
If you convert to ADP, I assume it is all done on the SQL server end?
As Access still has all the queries, does it pass the whole query through the network?

 

[Ed2020]

Considering moving the database to SQL Server or MSDE seems a little premature. If the database is only 20MB this is almost certainly not necessary.

The problem is with the design of the database, or the speed of your network, not the Jet engine.

The first thing I'd be checking is indexes on the tables (or lack of them). As a basic rule of thumb you should be indexing fields used in joins and those regularly searched on.

If that doesn't work then I'd also be reviewing the table structure to ensure the database is at least reasonably well normalised.

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

Have a look here:

http://uk.builder.com/architecture/db/0,39026552,20277979,00.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

Thanks PHV. I didn't know you could do that with Jet.

 

[qwertyjjj]

Interesting. I run this and get a lot of temporary indexes probably because of the way I've joined result sets onto result sets.

Any clues as where to look for optimisation for these?


---------------------------------------------
DATE: 03/19/07
VER: 4.00.8618

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- temp query ---

- Inputs to Query -
Table 'MSysObjects'
- End inputs to Query -

01) Restrict rows of table MSysObjects
using rushmore
for expression "ParentId=251658241"
then test expression "Type=6 And Connect Is Null"
store result in temporary table


---------------------------------------------
DATE: 03/19/07
VER: 4.00.8618

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- DisplayDivisions ---

- Inputs to Query -
Table 'Structure'
Table 'zarageddebt_CURR'
Table 'Acquisitions_zarageddebt_PREV'
Table 'zarageddebt_PREV'
Table 'zarageddebt_PREV-1'
Table 'zarageddebt_PREV-2'
Table 'baddebt_CURR'
Table 'Acquisitions_baddebt_PREV'
Table 'baddebt_PREV'
Table 'zarinvreg_CURR'
Table 'Acquisitions_zarinvreg_PREV'
Table 'zarinvreg_PREV'
Table 'zarinvreg_PREV-1'
Table 'zarinvreg_PREV-2'
Table 'zarinvreg_PREV-3'
Table 'zarinvreg_PREV-4'
Table 'zarinvreg_PREV-5'
Table 'zarinvreg_PREV-6'
- End inputs to Query -

01) Group table 'Structure'
02) Group table 'zarinvreg_CURR'
03) Outer Join result of '01)' to result of '02)'
using temporary index
join expression "S.DivisionID=ZI.DivisionID"
04) Group table 'zarinvreg_PREV-6'
05) Outer Join result of '03)' to result of '04)'
using temporary index
join expression "S.DivisionID=ZIMinus7.DivisionID"
06) Group table 'baddebt_PREV'
07) Outer Join result of '05)' to result of '06)'
using temporary index
join expression "S.DivisionID=BDMinus1.DivisionID"
08) Group table 'zarinvreg_PREV-5'
09) Outer Join result of '07)' to result of '08)'
using temporary index
join expression "S.DivisionID=ZIMinus6.DivisionID"
10) Group table 'Acquisitions_baddebt_PREV'
11) Outer Join result of '09)' to result of '10)'
using temporary index
join expression "S.DivisionID=NA_BD.DivisionID"
12) Group table 'zarinvreg_PREV-4'
13) Outer Join result of '11)' to result of '12)'
using temporary index
join expression "S.DivisionID=ZIMinus5.DivisionID"
14) Group table 'baddebt_CURR'
15) Outer Join result of '13)' to result of '14)'
using temporary index
join expression "S.DivisionID=BD.DivisionID"
16) Group table 'zarinvreg_PREV-3'
17) Outer Join result of '15)' to result of '16)'
using temporary index
join expression "S.DivisionID=ZIMinus4.DivisionID"
18) Group table 'zarageddebt_PREV-2'
19) Outer Join result of '17)' to result of '18)'
using temporary index
join expression "S.DivisionID=ZAMinus3.DivisionID"
20) Group table 'zarinvreg_PREV-2'
21) Outer Join result of '19)' to result of '20)'
using temporary index
join expression "S.DivisionID=ZIMinus3.DivisionID"
22) Group table 'zarageddebt_PREV-1'
23) Outer Join result of '21)' to result of '22)'
using temporary index
join expression "S.DivisionID=ZAMinus2.DivisionID"
24) Group table 'zarinvreg_PREV-1'
25) Outer Join result of '23)' to result of '24)'
using temporary index
join expression "S.DivisionID=ZIMinus2.DivisionID"
26) Group table 'zarageddebt_PREV'
27) Outer Join result of '25)' to result of '26)'
using temporary index
join expression "S.DivisionID=ZAMinus1.DivisionID"
28) Group table 'zarinvreg_PREV'
29) Outer Join result of '27)' to result of '28)'
using temporary index
join expression "S.DivisionID=ZIMinus1.DivisionID"
30) Group table 'Acquisitions_zarageddebt_PREV'
31) Outer Join result of '29)' to result of '30)'
using temporary index
join expression "S.DivisionID=NA_ZA.DivisionID"
32) Group table 'Acquisitions_zarinvreg_PREV'
33) Outer Join result of '31)' to result of '32)'
using temporary index
join expression "S.DivisionID=NA_ZI.DivisionID"
34) Group table 'zarageddebt_CURR'
35) Outer Join result of '33)' to result of '34)'
using temporary index
join expression "S.DivisionID=ZA.DivisionID"

 

[BNPMike]

Wow - and it still runs. If that doesn't make people respect Jet, I don't know what will...

Do you really need all that data at once?

 

[qwertyjjj]

Sadly, yes !
I think I could maybe organise the table structure better.
zarageddebt_CURR, zarageddebt_PREV, etc. all essentially store the same information but each table holds a succint set of data for every month that new stuff is imported.
I guess I could use the same table and ass an extra column to identify the month instead ?

I don't really understand the temporary index though in the plan. Might this be because I am joining datasets rather than tables. I have this sort of thing in my SQL:
SELECT {whatever}
FROM (((((((((((((((((
SELECT [Div Level Description], DivisionID
FROM Structure
GROUP BY [Div Level Description], DivisionID

) AS S
LEFT JOIN (SELECT DivisionID,
(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000 AS Months0To3,
(Sum([121To150])+Sum([151To180])) /1000 AS Months4To5,
(Sum([181To365])) /1000 AS Months6To12,
(Sum([MoreThan366])) /1000 AS [Months12+],
((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt,
(Sum([151To180])) / 1000 AS DebtAged5Months
FROM zarageddebt_CURR
GROUP BY DivisionID
) AS ZA ON ZA.DivisionID = S.DivisionID)

LEFT JOIN (SELECT DivisionID,
(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000 AS Months0To3,
(Sum([121To150])+Sum([151To180])) /1000 AS Months4To5,
(Sum([181To365])) /1000 AS Months6To12,
(Sum([MoreThan366])) /1000 AS [Months12+],
((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt,
(Sum([151To180])) / 1000 AS DebtAged5Months
FROM Acquisitions_zarageddebt_PREV
GROUP BY DivisionID
) AS NA_ZA ON NA_ZA.DivisionID = S.DivisionID)

LEFT JOIN (SELECT DivisionID,
etc.

 

[lespaul]

I guess I could use the same table and as an extra column to identify the month instead

Exactly, the fundamentals document below lists the "rules" for designing tables.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[qwertyjjj]

The reason for doing this originally was that each month's data is separate in itself and the only thing joining the tables is the join specified as Structure S.
As soon as you start joining tables instead of separate result sets, the grouping figures get multiplied up exponentially.

For example in the above SQL, how would I group those 2 tables together in a statement but get separate columns.
I think I would still need all the separate result sets even though they might now be in 1 table. Each result set is effectively a temporary table of it's own ?

 

[lespaul]

As soon as you start joining tables instead of separate result sets, the grouping figures get multiplied up exponentially.

Depends on how you are doing the joins, check out the JOINS article for more information on that.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[fneily]

NOTE: Currently does not handle subqueries, vt parameters, and subqueries" - you have subqueries (Selects within Selects)

And as everyone as hinted at, did you NORMALIZE your tables?

 

[BNPMike]

Relational databases, including Jet, get the optimiser to look for the least amount of data to retrieve. However whist this might work well for simple SQL like a select on two joined tables, the fact is as the query gets more complex the optimiser quickly defaults to dragging all the data out of each table.

I don't understand the plan output but looking at your SQL I'd guess there's brute force approach being used by Jet. I'd guess it pulls all the data across the network and then builds indexes to perform the joins. If so, this is a worst-case scenario. Moving to a server solution will help a lot but I suspect you have your data far more fragmented than you need.

 

[qwertyjjj]

I have a lot of subqueries in this SQL, hence all the different point in the workplan.
If I combine 6 tables into 1 as I can, would this make a difference on the efficiency because I'm pretty sure I'm still going to have to have separate subqueries in the SQL to separate the data.

The only joing to all the 'tables' or subqueries containing distinct resultsets is the Structure table.
Someone recommended the subquery (distinct resultset) perviously as the way the joins were working messed up the grup by figures, i.e. because of all the different tables the final summed figures were multiplied up due to differences in the number of records between each of the tables.