Network lag or Normalization? (1 Viewer)

[twinupshot]

Hello,

I need help knowing what I should look for to fix this problem:

My company has a database (Access 2000) that will run a report locally in 12 seconds. When we take the same computer and run the same report 1 mile down the street via T1 the report takes over a minute. Sounds like the network right? Well........
When I take a different database (mine, Access 2000) which is much bigger (1.9Gb), I get only about 3-5 seconds delay between the exact same locations.

I havent seen the design or structure of the slower Db, but I know it is running less queries, macros, etc... than mine to run this report.

So what should I look for when I open the slower Db? If it was the network then I would think that all Access Dbs would act the same and be slow to report, but that isn't the case.
Can the table design or query design cause the Db to act slower because the data is going through a couple routers?

We have a very knowledgable IT hardware person that swears the network is not the problem.(he performed speed tests of his own to verify this) He thinks the slower Db has a design problem that is causing it to be slower when on the T1.

So where should I look first?

Any ideas will help.

Thanks,

Bryan

 

[jfgambit]

Bryan:

I used to work for an ISP and I can tell you this, you can ping test until you are blue in the face, but the fact of the matter is that the T1 can be slowing down the connection. All a ping test does is test the response from the 1st Router to the port of the machine, it does not take into account the lag time to swap between routers or the capacity issues that are associated with the sharing of routers within the ISP Network. Even in cases where a dedicated T1 line is given to a customer, that T1 can be purchased for another Vendor that is shaing it with other Customers... i.e. UUNET buys from Level(3).

Copy the database (all tables, forms, etc) to a couple of computers in the Remote offcie and run the report. Does it still run 12 - 15 seconds on a local machine? If so, I would say it was the Network Connection.

 

[twinupshot]

But what about the fact that the larger database was tested and doesn't run much slower?
If it was the T1 wouldn't both Dbs act the same?

Why would it only affect one and not both?


Thanks,

Bryan

 

[jfgambit]

Is your database a local version or connected over the T1 with linked tables?

 

[twinupshot]

Oops! I guess I forgot to mention the backends are on the same local server.

So, locally both Dbs report in 12 seconds. Take them 1 mile down the street via T1 (to get to the backends). One Db takes 14 seconds(no big deal) and the other takes 1min15secs.

Same computers. Same exact reports. Different Dbs in Access 2000.

Thanks,

Bryan

 

[The_Doc_Man]

You need to understand the dataflow for an Access query.

First, if your application is running on machine X even though the .MDB file is on machine Y, split or otherwise,...

When you open a query in Access, it names one or more tables, provides zero or more criteria, and involves zero or more joins. The criteria can be at most a minor problem unless they involve domain-aggregate functions. However, the tables and joins can be a distinct problem.

When you execute a JOIN, Access has to step through all records of each table to identify matching records from each table. But that means that EVERY RECORD has to be accounted for in order to be sure that the result set is correct and complete. This has to happen once for EVERY JOIN CLAUSE in the query. It is like a problem in combinatorial math. The greater the number of JOIN clauses, the more records you have to manage, and the ratio is an exercise in factorials.

Now, what do you do about it? You add indexes to every field participating in the JOIN clause for your major queries. If Access knows there is an index on a table that is applicable to the JOIN, it will read the index (which will be smaller than the table itself) to evaluate the joining. Ideally, the indexes should exactly match the

So the questions that would apply in determining why things are so slow in only one of the two DBs are:

1. How wide (how many bytes) is one returned record for the most commonly used query?
2. How many JOIN clauses are involved?
3. Do indexes exist for all participant tables for the fields that are part of the JOIN?

Answer these questions for both of your databases and compare the answers. Dollars to donuts says you'll be able to identify the culprit(s) quickly.

 

[twinupshot]

Ok one last question from your quote:

"1. How wide (how many bytes) is one returned record for the most commonly used query?"

How are you measuring this? Are you counting the number of returned characters per line? ie. 1 character = 1 byte.

My understanding of your reply would be that a join or index problem could multiply the lag exponentially. Correct?

Thanks for the help.

Oh yeah, "combinatorial math"? I had to look that up!