Slow query

[DFS]

This UNION query is very slow. With only 3,000 records in the Parent table
and 7,000 records in the Child table, it takes about 60 seconds to run and
returns about 2200 records.

Any ideas on speeding it up? Thanks.
-- PART 1: HAS NO CHILD RECORDS

SELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPE
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL

UNION

-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE Z

SELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPE
FROM PROJECTS
WHERE PROJECT_ID NOT IN

(
(SELECT PROJECT_ID
FROM PROJECTS_CHILDREN
WHERE CHILD_TYPE Like "Z*")

AND

PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL)
);

[Simon Hayes]

"DFS" <no****@nospam.com> wrote in message news:<10************@corp.supernews.com>...

This UNION query is very slow. With only 3,000 records in the Parent table
and 7,000 records in the Child table, it takes about 60 seconds to run and
returns about 2200 records.

Any ideas on speeding it up? Thanks.
-- PART 1: HAS NO CHILD RECORDS

SELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPE
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL

UNION

-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE Z

SELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPE
FROM PROJECTS
WHERE PROJECT_ID NOT IN

(
(SELECT PROJECT_ID
FROM PROJECTS_CHILDREN
WHERE CHILD_TYPE Like "Z*")

AND

PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL)
);

Without table DDL and sample data, this is a guess, but it looks like
you could simplify the query to something along these lines:

select
p.project_id,
case when c.child_type is null then 'No Child Data' else 'Child Data,
Not type Z' end as sub_type
from
projects p
left outer join projects_children c
on p.project_id = c.project_id
where
c.child_type not like 'Z*'

If this isn't correct, please consider posting the DDL and sample
data.

Simon

[lucjan]

Hi, looks like you have 'not exists ' there, which results in table scans,
and essentially cartesian product twice for both 'not exists' in your lower
UNION.

Usually with some indexes you should be able to help query like that, but in
your case WHERE clauses 'not null' and 'CHILD_TYPE Like "Z*"' don't look
like good candidates for index, due to low selectivity.

But, you could try use #table or @table to limit your I/O cost.

That is , take you first sub-select

SELECT PROJECT_ID
FROM PROJECTS_CHILDREN
WHERE CHILD_TYPE Like "Z*"

and put it into temp table @t1.

take second,
SELECT P.PROJECT_ID
FROM PROJECTS P
WHERE P.PROJECT_ID IS NULL

in put it into second table @t2

now , instead of having not exists agaist PROJECTS_CHILDREN and PROJECTS ,
use @t1 and @t2 tables.

Make sure you have index on PROJECTS (PROJECT_ID).

It's good habit to test something like that in Query Analyzer with 'set
statistics io on'. This tells you number of I/O you are doing. What you are
trying to achieve is , lower number of I/O. So basically, look at the total
I/O and make sure this number is decreasing as you are making your sql
changes.

Thing to remember though, whatever query you run on such small tables should
not take so long. (assume no blocking, or hardware problems,etc.. ..)

Hope this helps,

Lucjan


"Simon Hayes" <sq*@hayes.ch> wrote in message
news:60**************************@posting.google.c om...

"DFS" <no****@nospam.com> wrote in message

news:<10************@corp.supernews.com>...

This UNION query is very slow. With only 3,000 records in the Parent table and 7,000 records in the Child table, it takes about 60 seconds to run and returns about 2200 records.

Any ideas on speeding it up? Thanks.
-- PART 1: HAS NO CHILD RECORDS

SELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPE
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL

UNION

-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE Z

SELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPE
FROM PROJECTS
WHERE PROJECT_ID NOT IN

(
(SELECT PROJECT_ID
FROM PROJECTS_CHILDREN
WHERE CHILD_TYPE Like "Z*")

AND

PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL)
);

Without table DDL and sample data, this is a guess, but it looks like
you could simplify the query to something along these lines:

select
p.project_id,
case when c.child_type is null then 'No Child Data' else 'Child Data,
Not type Z' end as sub_type
from
projects p
left outer join projects_children c
on p.project_id = c.project_id
where
c.child_type not like 'Z*'

If this isn't correct, please consider posting the DDL and sample
data.

Simon

[Gert-Jan Strik]

There are several ways to rewrite your query. The performance they will
give partly depends on the size of your tables, and definitely depends
on the available indexes.

Here is one way that might perform better if there are many projects of
child-type "Z%". Also, the performance is likely to improve by changing
UNION to UNION ALL. This can be safely done here, because the joined
sets are mutually exclusive.

SELECT P.Project_ID
, 'No Child Data' AS Sub_Type
FROM Projects P
WHERE NOT EXISTS (
SELECT 1
FROM Projects_Children C
WHERE C.Project_ID = P.Project_ID
)

UNION ALL

-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE Z

SELECT DISTINCT P.Project_ID
, 'Child Data, Not type Z' AS Sub_Type
FROM Projects P
INNER JOIN Projects_Children C
WHERE C.Project_ID = P.Project_ID
AND C.Child_Type NOT LIKE 'Z%'

Hope this helps,
Gert-Jan
DFS wrote:

This UNION query is very slow. With only 3,000 records in the Parent table
and 7,000 records in the Child table, it takes about 60 seconds to run and
returns about 2200 records.

Any ideas on speeding it up? Thanks.

-- PART 1: HAS NO CHILD RECORDS

SELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPE
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL

UNION

-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE Z

SELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPE
FROM PROJECTS
WHERE PROJECT_ID NOT IN

(
(SELECT PROJECT_ID
FROM PROJECTS_CHILDREN
WHERE CHILD_TYPE Like "Z*")

AND

PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL)
);

[DFS]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:60**************************@posting.google.c om...

"DFS" <no****@nospam.com> wrote in message

news:<10************@corp.supernews.com>...

This UNION query is very slow. With only 3,000 records in the Parent table and 7,000 records in the Child table, it takes about 60 seconds to run and returns about 2200 records.

Any ideas on speeding it up? Thanks.
-- PART 1: HAS NO CHILD RECORDS

SELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPE
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL

UNION

-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE Z

SELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPE
FROM PROJECTS
WHERE PROJECT_ID NOT IN

(
(SELECT PROJECT_ID
FROM PROJECTS_CHILDREN
WHERE CHILD_TYPE Like "Z*")

AND

PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN C
ON P.PROJECT_ID = C.PROJECT_ID
WHERE P.PROJECT_ID IS NULL)
);

Without table DDL and sample data, this is a guess, but it looks like
you could simplify the query to something along these lines:

select
p.project_id,
case when c.child_type is null then 'No Child Data' else 'Child Data,
Not type Z' end as sub_type
from
projects p
left outer join projects_children c
on p.project_id = c.project_id
where
c.child_type not like 'Z*'

If this isn't correct, please consider posting the DDL and sample
data.

Simon,

Thanks, but that didn't work. I got an answer on the Access newsgroup which
worked well - 1 second data return.

Here's the actual SQL (I used aliases when I posted the question here)
-- This section produces parent records that have no children

SELECT P.PROJECT_ID, 'NO SUBMITTALS' AS SUB_TYPE
FROM PROJECTS P LEFT JOIN PROJECTS_SUBMITTALS S
ON P.PROJECT_ID = S.PROJECT_ID
WHERE S.SUBMITTAL_ID IS NULL

-- This UNION section produces parent records having children,
-- but the child records are not of type 'drawing*'

UNION SELECT P.PROJECT_ID, 'SUBMITTALS, BUT NO DRAWINGS' AS SUB_TYPE
FROM PROJECTS P
WHERE

-- This section makes sure the child records are not of type 'drawing'
(
P.PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P INNER JOIN PROJECTS_SUBMITTALS S
ON P.PROJECT_ID = S.PROJECT_ID
WHERE S.SUBMITTAL_TYPE Like "drawing*") AND

-- This section makes sure to return only parent records having child
records
P.PROJECT_ID NOT IN (
SELECT P.PROJECT_ID
FROM PROJECTS P LEFT JOIN PROJECTS_SUBMITTALS S
ON P.PROJECT_ID = S.PROJECT_ID
WHERE S.SUBMITTAL_ID IS NULL)
);