Query Theory

[Tarnish]

Whenever I need something 'more' in terms of data recall from my apps, I never know what to do. I have queries that populate forms and provide the data for reports and such, but as things 'develop' I can't figure out if/when I should make a new query or just add to an old one.

For example, one app I work on has what amounts to a 'main' query that a couple or three medium-sized tables (20-30 fields) and a bunch of other tables from small ones (~10 fields or less) to ones just used to populate combo boxes.

That query basically populates my main data input/record browse form as well as most of my reports.

So the client asks if I can add something to the application that will require an additional table, etc, and then create some related reports which will need information that I now get from my main query. Certainly not all the information in my main query is needed. In fact, I probably only need about half the fields out of it or even less (but, that's true for most of my reports if you look at them individually).

So what do I do? What's the best for performance? Should I:

1. Just add the new table to the existing query and likely slow down everything done in the app since the main query is almost always being used.

2. Make a copy of the main query, delete out as many unnecessary fields as possible for my added needs.

3. Start fresh with a new query (probably just a variation of #2, really).

I guess I just don't understand the 'theory' behind when you should make a 'new' query and when it's better to just 'add onto' an existing one.

If anyone can provide a link or just give me a few pointers to broaden my understanding of this topic, I'd really appreciate it!

T

 

[fneily]

I never knew there was a "theory" behind how many queries you can have. Obviously, if you can use one query for more then one other object, less development on your part. The actual query doesn't take up that much room. Tables and forms do. You didn't tell us the size of your database. Also, do you do compact and repairs? Deleting and creating really expands a db size and compact and repairs will recover some of that space.
Also, Access doesn't use the query you create. When you run a query the first time, Access creates it's own version of it in the background which it tries to optimized. You can see this by running a query and then checking its' SQL. In future runs, it uses this "copy". If you change a query, Access must then make a new copy or version.
Maybe other readers have an opinion or some facts on this. But good question.
One more thing. If someone else in the future will maintain the database, and if one query is used for 100 forms and reports, it may be confusing to the person. Some developers like to match a query name with the name of the form or report that will use it. Or maybe not.

 

[Tarnish]

Hi fneily,

Thanks for the reply. Sometimes I feel apprehensive about asking these questions since they are so general, but I find it easier to learn more in less time if I have some broad 'vision' to what I'm trying to accomplish. I've been working with Access for about 10 months now and I can accomplish a number of things, but I often wonder if I'm doing them the most efficient way.

As to the size of this app, it's only around 10 to 12 megs on the front end right now, and I doubt it will get that much bigger though it will get some bigger. I'd say 15 megs tops before I'm through with it, and that's leaning toward over-estimation rather than under.

I do campact/repair often, both the front end and the back end even though I know (I think I know!) compacting is more of a back-end operation. I've made it as easy as possible for users to do the same, and when I pass the app on for good, I've set up some admin features that should make it very painless for someone else to administer (ability to boot users out of backend, ability to compact/repair/backup backend from frontend, automatic frontend updating, etc).

I guess what got me started thinking about this is some increased network traffic that's slowed the app up in recent days on my testing setup. Even though I don't think it's a long term issue where the modified app will go back into use, it's made me more interested in making sure my designs are as efficient as possible. Along those lines, I started wondering if I used a query that pulled in, for example, 40 fields worth of data per record, but I only needed 20 fields for a particular use, am I basically waiting for all the additional info to travel across the network when I don't really need it all..just a subset of the fields in a query?

Anyway, thanks again for the input. I'd just like to feel more confident that my usage of queries wasn't completely inefficient, and know for the future more what I SHOULD do to address a particular situation instead of mostly just knowing what I CAN do to accomplish a particular task.

T

 

[AlexCuse]

Tarnish -

I would not necessarily say that less queries is better. If you do not need all the data returned to your front end app for processing at all times, you would be better off having more queries, that only return the needed data. THis will limit what is being sent back and forth on your network, and with properly optimized queries probably give you a performance gain as well.

Just my 2 cents.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Tarnish]

Thanks Alex,

That's kind of what I was thinking.....if I had more queries, each tailored to a specific use, it would be better than using the same query for all uses (that the query can handle).

The query in question has just grown over time, and could continue to grow if I don't rethink my process a little. I think at this point there's nothing it's used for that it actually 'fits' anymore. Now, it's just a question of 'how much' extra data it contains over what I need for any given use.

Thanks again for the reply.
T

 

[fneily]

Your original post did not mention FE/BE or any network activity. This brings in another dimension. You're using the JET engine which is a file server system - it simply stores and retrieves data. This means it will move the WHOLE table to the client no matter what fields are specified in the query. The processing is done on the client side. Since you have a small database, this shouldn't be much of a problem if the network admins keep the network efficient.
On the other hand, the MSDE engine (Access Project) does the processing of the query (view) on the server and just returns the result so less network traffic.

 

[Tarnish]

Thanks again, fneily,

Sorry for not being more informative in my original post. Everything I've done in access has been split. I didn't realize there was a different engine used for the adp files. I have ZERO experience working with them.

I'll look into what it would take to use adp instead of mdb. It's too late for this project, but there's always a next one!

Thanks again,
T

 

[PHV]

it will move the WHOLE table to the client no matter what fields are specified in the query

You have some authenticated source of infos ?