SQL Anywhere database - SQL Question (long)

Author

SQL Question (long)

Steven J. Serenska

2005-05-20, 3:23 am

Hello:

I am writing a query that will retrieve up to 10,000 records, but
normally around 2,000 or so. It is for a desktop application, so
performance needs to be kind of zippy.

The query needs to select a set of records from a parent table, another
set from a child table, and then display the records together in a given
order. I had been using a UNION query to do this. Something like

SELECT
ParentID
, Description
, Price
, Sequence
FROM
ParentTable
UNION ALL
SELECT
P.ParentID
, C.Description
, NULL AS Price
, C.Sequence
FROM
Child C
INNER JOIN
Parent P ON (C.ParentID = P.ParentID)
ORDER BY
Sequence;

The purpose of this query is to present the datset as though it was
presenting items on an invoice and only *some* of those items had
detailed description lines that went with them. (I am simplifying
things a bit here to get to my point as quickly as I can.)

Here's what I can't figure out: I would like to be able to let the user
specify a filter for the first SELECT in the query (e.g., return a list
of invoice parent items with a filter such as "WHERE Description LIKE
'%Large%'"). When I do that, however, I run the risk of *not* filtering
out the corresponding the Child items in the second SELECT statement.

I have tried adding a subselect into the second SELECT statement as follows:

SELECT
[Upper Half as written above]
WHERE
Description LIKE '%Large%'
UNION ALL
SELECT
[Lower Half as written above]
WHERE
C.ParentID IN
(SELECT
ParentID
FROM
Parent
WHERE
Description LIKE '%Large')

In other words, I duplicated the Parent filter so that SELECTs into the
Child table first make sure that the ParentID has not been filtered out.

Unfortunately, this approach slows performance considerably. I had been
hoping that the Plan would "notice" that the same SELECT was being run
twice, but there is no efficiency in doing it this way.

I am not a SQL expert by a long shot. Is there some native Grouping or
Parent/Child subquery or even a JOIN technique that I could use to make
sure that both halves of a Parent/Child UNION query return the
appropriate dataset (i.e., when the parent records are filtered, the
child records should be filtered as well?

Thanks in advance for your help.

Steven J. Serenska

Scott Holman

2005-05-20, 9:23 am

Did you try adding the search condition to the join of the child select?

SELECT
ParentID
, Description
, Price
, Sequence
FROM
ParentTable
WHERE
Description LIKE '%Large%'
UNION ALL
SELECT
P.ParentID
, C.Description
, NULL AS Price
, C.Sequence
FROM
Child C
INNER JOIN
Parent P ON (C.ParentID = P.ParentID and P.Description LIKE '%Large%')
ORDER BY
Sequence;

"Steven J. Serenska" < sjs@RemoveTheseWords
Banksurance.com> wrote in message
news:428d4413$1@foru
ms-1-dub...
> Hello:
>
> I am writing a query that will retrieve up to 10,000 records, but normally
> around 2,000 or so. It is for a desktop application, so performance needs
> to be kind of zippy.
>
> The query needs to select a set of records from a parent table, another
> set from a child table, and then display the records together in a given
> order. I had been using a UNION query to do this. Something like
>
> SELECT
> ParentID
> , Description
> , Price
> , Sequence
> FROM
> ParentTable
> UNION ALL
> SELECT
> P.ParentID
> , C.Description
> , NULL AS Price
> , C.Sequence
> FROM
> Child C
> INNER JOIN
> Parent P ON (C.ParentID = P.ParentID)
> ORDER BY
> Sequence;
>
> The purpose of this query is to present the datset as though it was
> presenting items on an invoice and only *some* of those items had detailed
> description lines that went with them. (I am simplifying things a bit
> here to get to my point as quickly as I can.)
>
> Here's what I can't figure out: I would like to be able to let the user
> specify a filter for the first SELECT in the query (e.g., return a list of
> invoice parent items with a filter such as "WHERE Description LIKE
> '%Large%'"). When I do that, however, I run the risk of *not* filtering
> out the corresponding the Child items in the second SELECT statement.
>
> I have tried adding a subselect into the second SELECT statement as
> follows:
>
> SELECT
> [Upper Half as written above]
> WHERE
> Description LIKE '%Large%'
> UNION ALL
> SELECT
> [Lower Half as written above]
> WHERE
> C.ParentID IN
> (SELECT
> ParentID
> FROM
> Parent
> WHERE
> Description LIKE '%Large')
>
> In other words, I duplicated the Parent filter so that SELECTs into the
> Child table first make sure that the ParentID has not been filtered out.
>
> Unfortunately, this approach slows performance considerably. I had been
> hoping that the Plan would "notice" that the same SELECT was being run
> twice, but there is no efficiency in doing it this way.
>
> I am not a SQL expert by a long shot. Is there some native Grouping or
> Parent/Child subquery or even a JOIN technique that I could use to make
> sure that both halves of a Parent/Child UNION query return the appropriate
> dataset (i.e., when the parent records are filtered, the child records
> should be filtered as well?
>
> Thanks in advance for your help.
>
> Steven J. Serenska

Steven J. Serenska

2005-05-20, 9:23 am

Scott:

> Did you try adding the search condition to the join of the child select?

Well, no, I didn't. DUH!!

Thanks very much for your help. The Internet is an amazing tool. In
the old days, I would have stared at this for 3-4 days before coming up
with what you came up with in minutes.

I knew when I was writing this late last night that it was time for me
to quit and/or get some outside assistance. At least I got that part
right...

Thanks again.

Steven J. Serenska

Sponsored Links