Access workings

[Shrek]

Does anyone know if building queries from queries, rather than tables poses any sort of performance problems? If I have a query that calls in 3 others, is access forced to run the 3 other queries before actually running the current one?

[General Kenobi (ret.)]

Running a make-table query is usually faster. I just discovered (the hard way) that this does create problems in a multi-user environment, but if you're the only one using the database.

[Shrek]

Thank you, jedi master. I guessed that was the case. I prefer the make table myself since it gives me a cleaner audit trail.

[Minerva]

Primarily stylistic, but I find that nested queries rarely create performance problems,causes fewer error situations and is ultimately much less work for me.

My standard practice is to create a series of simple queries (Access sometimes gets confused if the query is too complex), usually one or two select queries first to cut down the size of the file (both by number of fields and by number of records).

The reasons I have found nested queries to be less work and less prone to error are: because I don't have to go back to the beginning and re-run everything (and re-create a series of tables, and might skip one) if I want to change criteria, add a field or run the same query with a different group of data (e.g., the next month). To answer part of the question - yes Access has to run all of the subordinate queries (although somehow I think it may save some stuff in memory somewhere, because it is usually faster if it's been run recently - don't know how). To give my 2 cents on another question/comment - I think tracking is easier with nested queries - with the series of tables, you rely on your memory or naming conventions to quickly trace back to find "OK - I created TABLE29, what query / logic / source did I use? - or, even more so - what query / logic / source did the person who originally created TABLE29 that I now have to identify by sex as well as age use?". It also cuts down on the size of your database, if that is a consideration.

Gotta get back to my queries!

[hactuary]

I've used nested queries extensively, especially to gain the audit trail. But another reason to use them is that each time a table is recreated, Access adds that much to the database size--it doesn't release the memory used in the prior version of the table. This doesn't happen with nested queries.
This became important when I was working with a very large database. After about 3-5 "table creations" I had to compact the db or hit the 2 gig size limitation (and that locked up the db and sent me to the backup--until I learned to create a new db and attach to the locked-up db to draw off its data--not elegant, but the goal is the correct result, not the "in between").
The downsize to nesting is that the speed is compromised. But I judged the extra computing time due to nesting to be small compared with the extra work I would have had to put in to auditing, compacting, wondering if I SHOULD be compacting at a certain time. Also, nested queries mean I don't have to run the subordinate queries, only the end query.
My situation took about ten steps to get to the required result. I found things MUCH too slow if I nested all the steps. As well, there were times that I functionally couldn't do what I wanted without creating intermediate tables, not just queries (e.g., appending data onto other data). So I would nest, say, three queries, then do a make-table query, then nest some more or do appends, etc.
At this point, I was asking myself if I had the right tool for the job! But it is the only tool I had and it did the job adequately. Also, I didn't want to learn another tool (e.g., SAS). As always, we take into consideration all the issues and then jump in. The process worked and others are now working successfully off the legacy I left. Access isn't too hard to understand, so training someone else was a big issue that kept me with Access.

[Minerva]

hactuary - are you one of my other personalities? (:D)) Seriously - I do exactly the same thing - nest the queries until I can't, and then do a make-table, and do "nested" databases with linked tables. (A project I'm working on right now has about 8 interrelated databases, a new record!)

One more hint if queries are running slowly - look at the indices on your tables - if there aren't any, create them to make matching and grouping quicker.)

[General Kenobi (ret.)]

Combining two ideas:
If you use a make-table query, considering using a delete query and an append query instead. That way, you can index fields in the made-table.

Unless there's a way to index fields when they're created, and I'm not aware there is.

[snafu]

FYI, using delete and append is much slower than using drop table and make table queries. On large tables with complex indexes possibly 100 time slower.

Use straight SQL queries within MSAccess to accomplish the same thing.

New Query
Don't add any tables
From the menu, Query, SQL Specific, Data Definition

In raw SQL, use the CREATE INDEX statement.
General syntax is CREATE INDEX myIndexName ON MyTableName (myfield1, myfield2)

See help topic CREATE INDEX Statement for full details and examples within VBA and within the Query window.

Also check out the DROP TABLE and DROP INDEX statements and examples as they are also pretty useful.


_________________
Peace,
snafu
_________________________________________________
Coming soon to an ACAS near you!!!

<font size=-1>[ This Message was edited by: snafu on 2002-02-14 17:38 ]</font>

[Gates Is Antichrist]

Thanks - that is useful and extremely timely. Funny - my D.P. department said "No can do," and an actuary solved it. (Who'da thunkit, lol)

[Wino In Training]

Just a technical note, not of much use probably: I seem to remember from some investigation done a few years ago, that when you're nesting queries, Access actually builds the SQL necessary to run the whole thing in one shot, and then tries to run it all at once. If there are crazy joins going on, that's one reason why it gets too complex (and slow, if it can run at all).

[Dr T Non-Fan]

Easier just to run the SQL or Oracle/Sybase. From what I recall, using the database server's memory, instead of your own PC's, will make things a bit easier. No temporary data on your hard drive, moving up and down through the LAN cable. (N/A for databases on your hard drive.)

Also, note that you'll want your nested queries to make the joins LESS complicated, not more.
For example, if you only want information on 35-year-olds, then put your "WHERE" age=35 statement inside the nested query, instead of on the outside.

This might be obvious for some. I apologize for inadvertantly patronizing you. To the rest, I hope it helps.

[Anonymous]

DTNF - How and when do you use Oracle/Sybase in your actuarial work? or was the reference solely from IT knowledge.

[Dr T Non-Fan]

Someone has already made the database, which contains all the data required to do actuarial analysis in an easy (read, no waiting in the mainframe queue and learning any mainframe language) format. I create a summarizing extract from that, using joined tables and such, to get a small enough output to use in Excel. Make things look fancy for those who require aesthetics to believe the numbers.