Q
Problem solve Get help with specific problems with your technologies, process and projects.

Sub-query speed, part 1

Which one of the following statements take less time?

Query 1

SELECT  CONCAT ( CONCAT ( '

', r.name ), CONCAT ( '

', r.description ) ) AS named, r.room_id FROM rooms r WHERE NOT EXISTS ( SELECT 1 FROM room_res rr WHERE TO_DATE ( :upto_date, 'YYYY-MM-DD HH24:MI:SS' ) > rr.res_from AND TO_DATE ( :from_date, 'YYYY-MM-DD HH24:MI:SS' ) < rr.res_upto AND rr.room_id = r.room_id ) AND :ppl <= r.capacity;

Query 2

SELECT  CONCAT ( CONCAT ( '

', r.name ), CONCAT ( '

', r.description ) ) AS named, r.room_id FROM rooms r WHERE NOT EXISTS ( SELECT 1 FROM room_res rr, rooms x -- Changed WHERE TO_DATE ( :upto_date, 'YYYY-MM-DD HH24:MI:SS' ) > rr.res_from AND TO_DATE ( :from_date, 'YYYY-MM-DD HH24:MI:SS' ) < rr.res_upto AND rr.room_id = x.room_id -- Changed ) AND :ppl <= r.capacity;

What I can do in a forum like this is point out large-scale differences; for example, I can point out that the two queries above do not (in general) give the same results. Query 1 says "Describe each room that is big enough, if it is available". Query 2 says, "Describe all rooms that are big enough, if all of them are available." Notice that the sub-query in Query 2 is not correlated to the main query (meaning there is nothing in the sub-query that refers to the table in the main query).

Therefore, Oracle will evaluate that sub-query once and apply that one result to all rows of the main query. See page 2 for a correlated sub-query.

You might save a little time by converting the strings :from_date and :upto_date to DATEs before executing the query.

Click for page 2.

In a forum like this site, I can only guess at which query is faster. If you're concerned about speed, you should learn how to use EXPLAIN PLAN, which will take into account what indexes you've created, how selective those indexes are, etc., all based on the actual data in the tables when they were analyzed. The chances of me re-creating all those variables are very small.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close