Regular SELECT query vs. sql pass through query 1

[MLZ]

Hi,

Are there cases when a regular SELECT query would run faster than if it were run as sql pass through query?
If so what are those cases?

I have a query that has 2 outer joins was originally designed as sql pass through query. It ran for over 4 minutes but when I converted it to an SELECT query, it ran in 1 minute. Is there an explanation why this has happened?

Thanks,
mlz

 

[cheerio]

Generally a pass through query should be faster and there are two reasons for this.

Suppose you have a list of people in a table where the Surname field is not indexed and you want all people called Smith. A pass through query will run on the server and return just those rows. An Access query using linked tables will fetch every record across the network to your pc and inspect that record to see if it is one you want. So the Access query can be very easily constrained by Network bandwidth. (assuming SQL Server is running on a different machine).

The second reason is that the database engines of SQL Server or Oracle have much stronger query optimisers so they are better able to find the optimum strategy.

If you have a strong counter example and you want a view you need to post both versions of the SQL, explain which fields are indexed and indicate the number of records in each table.

 

[BNPMike]

What were you passing through to, eg MS SQL Server?

 

[dhookom]

Cheerio,
Great reply. Deserves a star.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

Wait a minute! the question is why isn't it running faster. In fact why is it 4 times slower. That is a difficult one. At this rate somebody could get a lot of stars...

 

[MLZ]

Thanks everyone for the reply. Thanks cheerio for the explanation why the sql pass through query should run faster.

In response to the question as to what server I'm sending my sql pass through query, it is to an Oracle database - Documentum. Here is the sql pass through query:

SELECT
cd_object_name as Prefix,
cd_std_revision as revision,
c.document_class as Doc_Type,
ccd_object_name As Change_Document,
ccd_std_revision As Change_Revision,
to_char(a.date_on_document,'MM/DD/YYYY') as Change_Doc_Date
from ccd_view a, ccd_affects_cd_v b, cd_view c
where c.cd_oid = b.child_id (+)
and b.parent_id = a.ccd_oid (+)

You will find that FROM clause includes views. For security reasons, I chose to retrieve the data from views instead of actual base tables. Supposedly, the columns "cd_oid","child_id","parent_id" are indexed.

When I changed the query to a regular select, I linked to the same views and the regular select used them:

SELECT cd_object_name AS Prefix, cd_std_revision AS revision, CD_VIEW.DOCUMENT_CLASS AS Doc_Type, ccd_object_name AS Change_Document, ccd_std_revision AS Change_Revision, format(CCD_VIEW.date_on_document,'MM/DD/YYYY') AS Change_Doc_Date
FROM (CD_VIEW LEFT JOIN CCD_AFFECTS_CD_V ON CD_VIEW.CD_OID = CCD_AFFECTS_CD_V.CHILD_ID) LEFT JOIN CCD_VIEW ON CCD_AFFECTS_CD_V.PARENT_ID = CCD_VIEW.CCD_OID;

Also, the report that uses the above query accepts a user parameter. So via a module, I set the record source to something like this:

"select from query_name WHERE field = " & user_parameter

Cheerio, if the Oracle optimizer somehow ignores the indexes, would this explain why the regular select runs faster than the sql pass through query?

Please advise.

Thanks,
mlz

 

[cheerio]

My main point is that if pass through is running many times slower we would all like to see the SQL, the indexes and the volumes of data so that we can suggest the cause.

For example, if the queries are returning most of the rows ("give me a list of all employees and their years of service" say) then there's not going to be a saving on network traffic. Similarly, such a query may not benefit from an indexing strategy.

The reason for explaining why pass through should run faster is that with that understanding we can begin to understand why those benefits are being frustrated in this case.

Whilst I am really grateful for the star I really would like to know what's happening here. Then I might learn something.

 

[BNPMike]

When you say 'regular select' does that mean Access is using ODBC or something?

In each case Oracle will construct the retrieval using its Optimiser. The queries look the same to me, just glancing. Oracle can be influenced by the order you say things but it's not obvious what is different between the selects.

Tricky one, this.

 

[dhookom]

I think there is a difference between entering a parameter/criteria vs not. Apparently the P-T doesn't use a user entered parameter. I would be interested in the results if the SQL of the P-T was modified to include the dynamic parameter. I think this would be comparing apples to apples.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cmmrfrds]

I see you changed the order of the joins on the second query. On outer joins the table with the largest number of records should be listed first, if possible, for efficiency. Try putting the joins in the same order on the 2nd query to see if it makes a difference. The other thing is the 2nd query uses the newer ANSI syntax and may trigger a different optimization from Oracle.

Oracle will use ccd_view as the driver table here.

from ccd_view a, ccd_affects_cd_v b, cd_view c
where c.cd_oid = b.child_id (+) '- old left join syntax
and b.parent_id = a.ccd_oid (+)

CD_VIEW is the driver table here.

FROM (CD_VIEW LEFT JOIN CCD_AFFECTS_CD_V ON CD_VIEW.CD_OID = CCD_AFFECTS_CD_V.CHILD_ID) LEFT JOIN CCD_VIEW ON CCD_AFFECTS_CD_V.PARENT_ID = CCD_VIEW.CCD_OID

 

[BNPMike]

Oracle doesn't necessarily use tables in way dependant upon how you write the SQL. I think that is called 'rule-based' but you can now set it up to be 'cost-based' (I think that's the term) in which case it will calculate a number of possible strategies and pick the most economical.

Also in the second instance I assume the select has gone to Access. MLZ has not revealed how he is connecting to Oracle (there is more than one option). Let's assume it's ODBC, then some Access component is translating the SQL into ODBC. I don't know how that works but you'd guess it would be issuing something similar to, and in the same order as, the input SQL but it would be interesting to see exactly what it is requesting.

 

[cmmrfrds]

I have been optimizing some Oracle SQL Statements (version 9) lately, and acccording to the documentation and what I have discovered the order of the tables in the From Clause does affect the way Oracle optimizes. Apparently, the rule-based optimzer works in the opposite order of the cost-based optimizer. Also, the cost-based optimizer is heavily reliant on statistics being produced on the table. If the statistics are not current or non-existent then the optimizing is inefficient. I think it is worth a try to reorder the tables in the From Clause as a test, at least make both queries the same for a valid comparison.

 

[MLZ]

Thanks everyone for your responses and analysis.

I'm going to try cmmrfrds's suggestion to reorder the tables in the from clause to match the query2.

Indeed, CD_VIEW is the largest of the 3 views. I'm connecting to the Oracle database using Oracle ODBC Driver 8.01.76.

It looks like I just need to tune my SQL pass through query. So for now, I will treat this discussion closed.

Thanks again for everyone's input.

-mlz