Skip to main content
Known Participant
August 12, 2009
Question

Is Query of Queries faster than multiple WHERE criteria?

  • August 12, 2009
  • 3 replies
  • 1164 views

If I need a recordset based on two table column fields (let's say firstName and lastName), is it better to run one query with a WHERE firstName = x AND lastName = y, or is it better to query of queries and find all records with a specified firstName, and then query that query to filter only records with a specified last name?  Which is the better/faster method?

<cfquery name="somequery" datasource="someds">

SELECT *

FROM sometable

WHERE firstName = 'John' AND lastName = 'Smith'

</cfquery>

or....

<cfquery name="somequery" datasource="someds">

SELECT *

FROM sometable

WHERE firstName = 'John'

</cfquery>

<cfquery name="somequery2" dbtype="query">

SELECT *

FROM somequery

WHERE lastName = 'Smith'

</cfquery>

This topic has been closed for replies.

3 replies

Known Participant
August 13, 2009

Thanks guys.

What might be an example of when I'd like to use QoQ?  If anyone knows off-hand.

Inspiring
August 13, 2009

When combining data from more than one datasource.

When cfoutput query="yourquery" group="somefield" isn't going to get the job done..

Inspiring
August 13, 2009

As a rule of thumb, make the database server do all the work... i.e. locally.  If what you are interested is "first name = x and last name = y," then those are the only records that you want to be "sent over the little wire" to you.  "The little wire" is always the bottleneck.

Inspiring
August 12, 2009

It depends on a variety of things, but usually the fewer trips you make to the db, the better.  Also, the less data you bring back, the better.