Query design question?

[JimP]

Query1 retrieves data for 2,000 records from a series of linked tables that
have about 100,000 recordset

Query2 retrieves data for 5,000 records from another series of linked tables
that has about a 100,000 recordset as well.

Query1 and Query2 are linked on a customer ID (e.g.)

Should there be a simple join between Query1 and Query2 on Customer ID, or
would there there be a benefit to limiting the records in Query2 first?

 

[Guest]

I have an old-fashion tool in my desk drawer to answer such questions: a
stopwatch. When it comes to queries, accuracy first then worry about
performance.

It's also possible that using Showplan might help decide. Google showplan
for info on it.

Now on a large, multi-user database such as Oracle, I'd also pay attention
to things like disk hits and costs. If making one query fast slows down 99
other users, that's not a good thing. Are these linked tables in something
like SQL server? You might want to check what happens there.

 

[JimP]

Yes, SQL Server app - have an old stopwatch, but didn't know if some general
guidelines applied.