Are huge queries really slow to search?

[Sharky II]

Hi guys

I've made a db which stores customers... each customer can do lots of things... they can do trianign courses(2 tables, many to many here), they can go on trips abroad(2 tables, many to many here), they also have other stuff.... basically to cut a logn story short, i'm searching for records usign hre query by form method - the user wants the ability to search for " all customers who have done x or y trainign course, been to spain, have x or y skills etc etc" - basically a search which searches for mathing info from several tables.

if i create a huge query with all the customer details, all the trip details, all the training details, all the special skills etc etc, and ther are many records, will this just be really slow when searching? will it crash the system? apparently there's gonna be loads (thousands) of customers and so i'm just worried that a large query like that would mess things up...

what do you think guys? i've already got searches for individual stuff (like if you just wanna search for a trip, or a training course, or a customer) but htis one joins them all...

Cheers

Edd

 

[Treason]

this really depends on how many records and fields you are talking about??
1000? 10,000? 100,000? 1,000,000?

Any what do you mean by search?? The query will take longer to run depending on the PC's specs and the # of records in the source table. I run a shared db with at least 200,000 records and the queries dont take too long to run...

 

[Cosmos75]

Are you pulling the all the fields you want into just one query or do you have intermediate queries?

I know that if you have certain criteria it is better to have several intermediate queries. The first query should first query the table you are performing the criteria on. Then the first query shuold be used in another query to join that filters data with the other tables of interest. Doing all of that in just one query will really slow you down. But that is when you have WHERE conditions in the query.

I am not sure if you having intermediate queries will help if you are querying for ALL the information. I would just do it both ways and see for yourself which one is faster or even open the queries (All-In-One query and the Final query that uses intermediate queries) using VBA and time the difference between the two.

 

[Sharky II]

i mean like a few thousand records (Eventually).. there'll be like 70 fields from different tables composing the query!!!!!

by search i mean the query by form record, with the query named above as the source of search.

cheers

 

[Sharky II]

i mean taking all the fields from tables, and putting them in a (permanment) query by the query wizard.

this query then acts as a 'source' for the 'search' (qbf method), which generates a dynamic query of it's own which contains the 'results'

it will always be quick on my machine cos i only have about 6 records inputted! i'm worried about he future it getting clogged up etc.

cheers