Which is faster - Query based on table or Query based on another query

[Cosmos75]

Which is faster

1) Query based on another query that has joined info back together from tables?

2) Query based on tables?

3) Depends?

Other than using some sort of timer on the queries is there a better rule of thumb?


I have a database based with close to 80 queries and am having a hard time keeping track of what query does what. Am wondering if it is better to have ONE query that pulls data from many tables or have a few intermediate ones and one final one pulling the info from the intermediate queries? What effect does having criteria or formulas have on making that determination?

 

[llkhoutx]

A series of queries runs must faster than one with multiple joins.

 

[The_Doc_Man]

The best answer I can give is "It depends" - but at least I can tell you on what.

If you have the ability to place indexes on each involved table and the indexes match the fields named in the WHERE criteria, your performance can be greatly increased for EITHER case. So the answer depends on which WHERE elements are taking advantage of existing indexes.

The second issue is that if you have a humongous table, the longer the record, the longer your operation will take. One query based on a long record is far slower (usually) than a set of queries based on joining much shorter tables, when possible. This is strictly because of buffer sizes. If your choice is one big query vs. several smaller queries but both are from the same table, then a different factor becomes the issue.

In general, when you have a series of WHERE criteria, they are evaluated in the order that they appear, though sometimes they might not be. If they all appear in the same WHERE clause because it is a single table query, then the order doesn't matter. All of the criteria are applied simultaneously, in effect.

But if you are using multiple tables and want to do the best possible optimization to FORCE the issue, do a series of queries with the most restrictive query innermost, as long as that query is based on an existing index. Queries based on other queries have to be evaluated like parentheses, innermost first. If your innermost query is based on the lowest cardinality, you make the outer queries faster because they must handle less data as the queries are evaluated outward.

Cardinality in some database engines is the expected number of records you will return, on the average, for each valid key. For a primary key (No Dup), cardinality is 1. For a key such as Sex (Male/Female), if there is no gender bias in the population, the cardinality of that key is about 50% of the total number of records. Optimizing a multi-criteria, multi-layered query means making the most restrictive portion of the query run first so the greatest number of elements are eliminated first, leaving less behind for the remaining query parts.

So you see, the best answer is, "It depends."

 

[Cosmos75]

The best answer I can give is "It depends"

The_Doc_Man,

I figured that would be the answer. I was just wondering if it was better to have a query to pull ALL the data from multiple tables than have other queries pull the data based on criteria from this base query but it sounds to me that as more data is entered into the database that the base query would become very slow and that it would be better to have basically the same query over and over but with different criteria so that it won't open the entire recordset.

 

[Cosmos75]

do a series of queries with the most restrictive query innermost, as long as that query is based on an existing index. ... you make the outer queries faster because they must handle less data as the queries are evaluated outward

The_Doc_Man,

I want to make sure I understand this correctly. Do you mean that having a base query (outermost?) that pulls together all the info from multiple table and the having another query (innermost) that pulls from the base query with criteria, makes the base query run faster? Doesn't the base query still have to get ALL the records and then the criteria query filters out from the base query?????



Here another thing I am not sure I am doing correctly. When I pull info back together from multiple tables I include the Primary Keys AND the fields I want to use in a report from the tables, but in queries base on that base query I only include the fields. Is this the wrong way to go about it? My reports are almost always based on the final query (that only has fields) with the records and calculations that fit the criteria in each progressive query.

This works but am I doing it wrong?

 

[Cosmos75]

Still not sure....

I still have no idea which is faster. I think I will just have a base query pull everything and the have progressive queries pull out records based on criteria for now. I plan to give this to the user as a front end/back end splitted database. I'll place timers on the reports to tell me how long they took to pull up.

Probably once it's been populated with a bunch of data, I'll ask it if starts slowing down and then give them a new front end with a different query structure and see if that doesn't improve the situation.

 

[Cosmos75]

Pat,

Good News
That makes perfect sense!


Bad News
I guess I will have to spend most of this weekend re-doing my queries and reports!

 

[arvindn]

Reading this thread, i decided to post my experience.

1) I had a query NAMED "REPBILLS" OF THE SORT

SELECT BILLS.*,CUSTOMER.CITY,CUSTOMER.STATE,CUSTOMER.PHONE1 FROM BILLS INNER join CUSTOMER ON BILLS.CODE=CUSTOMER.CODE


2) WHEN I EXECUTED QUERY (THRU UI) OF THE SORT

SELECT * FROM REPBILLS WHERE [ORDER_DATE]>=#4/4/4#

I TURNED ON THE SHOWPLAN OPTION ON AND THE SHOWPLAN.OUT FILE SHOWED THAT A SCAN OF BILLS WAS PERFORMED FIRST TO RESTRICT AND THEN JOINING WAS DONE.

SO, MAY BE YOU HAVE TO TEST IT OUT YOURSELF ???