how can i improve this query?

[noober]

The following query is VERY slow, anyone help optimize?

SELECT support_requests.d_submit, support_requests.req_user,
support_requests.priority, support_requests.proc_user,
files.file_no, client.clnt_code,
files.file_stat, groups.grp_no,
support_requests.report_id, support_requests.sup_req_id
FROM files, support_requests, client, OUTER groups
WHERE support_requests.req_type = 'H'
AND support_requests.d_complete IS NULL
AND support_requests.d_deleted IS NULL
AND support_requests.d_rejected IS NULL
AND client.clnt_id IN
(SELECT client.clnt_id
FROM client
WHERE client.parent_id =
(SELECT client.clnt_id
FROM client
WHERE client.clnt_code = 'ABC'
AND client.parent_id IS NULL))
AND files.file_key = support_requests.file_key
AND files.clnt_id = client.clnt_id
AND client.parent_id IS NOT NULL
AND files.grp_id = groups.grp_id

 

[olded]

This query is slow because of the sub select. a sub-select will always force a full table scan. I don't know your data as well as you, but can't you eliminate the second sub-select with something like this:

.
.
AND client.clnt_id IN
(SELECT client.clnt_id
FROM client
WHERE client.parent_id = client.clnt_id
AND client.clnt_code = 'ABC'
AND client.parent_id IS NULL)
AND files.file_key = support_requests.file_key
AND files.clnt_id = client.clnt_id
AND client.parent_id IS NOT NULL
AND files.grp_id = groups.grp_id

Also, if your client table is large, you might try reading the client subset into a temp table first and using that in your main select.

Regards,

Ed

 

[noober]

Hi olded,
I like using temp tables as well but I am working with a programmer who is using vb/ado and she insists that ado will not work properly with temp tables. My b.s. meter is buzzing but its hard to know due to our environment and I dont have the vb and ado skills to test myself.

 

[olded]

noober:

I'm like you. I don't have the vb.ado skills myself. My vb expert is gone until Jan. 9. When he gets back I'll ask him about using temp tables with VB against an Informix database.

Regards,

Ed