Which method of retrieving the Case Name is faster (using a large dataset).  I'm using this query to populate a grid which at most will retrieve about 40 rows at a time.  It seems to me that the first query will only bother to retrieve _CaseName as it is needed by the grid.  The second one however would have to process the entire query in order to satisfy the second join condition.  Is that how it really works or is this pure nonsense on my part.

SELECT
 li.liDate,
 it.itItemName,
 (SELECT ca.caCaseName FROM Cases ca WHERE li.liCaseKey = ca.caCaseKey) AS _CaseName
FROM LineItems li
 LEFT OUTER JOIN Items it ON li.liItemKey = it.itItemKey


SELECT
 li.liDate,
 it.itItemName,
 ca.caCaseName AS _CaseName
FROM LineItems li
 LEFT OUTER JOIN Items it ON li.liItemKey = it.itItemKey
 LEFT OUTER JOIN Cases ca on li.liCaseKey = ca.caCaseKey


Dale

Dale


I have no idea as to the answer to your question but I suspect the latter will be faster. Why not do the obvious and test them both in EDBManager?

Roy Lambert

Dale

> Which method of retrieving the Case Name is faster (using a large dataset).  I'm using this query to populate a grid
which at most will retrieve about 40 rows at a time.  It seems to me that the first query will only bother to retrieve
_CaseName as it is needed by the grid.  The second one however would have to process the entire query in order to
satisfy the second join condition.  Is that how it really works or is this pure nonsense on my part.
>
> SELECT
>   li.liDate,
>   it.itItemName,
>   (SELECT ca.caCaseName FROM Cases ca WHERE li.liCaseKey = ca.caCaseKey) AS _CaseName
> FROM LineItems li
>   LEFT OUTER JOIN Items it ON li.liItemKey = it.itItemKey
>
>
> SELECT
>   li.liDate,
>   it.itItemName,
>   ca.caCaseName AS _CaseName
> FROM LineItems li
>   LEFT OUTER JOIN Items it ON li.liItemKey = it.itItemKey
>   LEFT OUTER JOIN Cases ca on li.liCaseKey = ca.caCaseKey

As neither query can return a sensitive result set I would suspect the correlated subquery might even be slower than the
join.

With a large dataset if you can make the query sensitive you will get the best speed.  For example the following with
request sensitive result set.

SELECT
 li.liDate,
 (SELECT it.itItemName FROM Items it WHERE li.liItemKey = it.itItemKey) AS itItemName,
 (SELECT ca.caCaseName FROM Cases ca WHERE li.liCaseKey = ca.caCaseKey) AS _CaseName
FROM LineItems li

John

The results are in (using dataset of 50,000 records):

The first one is the slowest at 11.7 seconds.

The second one is next at 8.6 seconds

John... yours was the winner hands down at 0 seconds... Very Cool.


Dale