By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,213 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,213 IT Pros & Developers. It's quick & easy.

slow query in access and in vb datareport

P: n/a
Hello,
I need to extract the names of the employees which have not been active
during the last 3 months from the following tables

EMPLOYEES:
employee_id
name

COOPERATIVE
cooperative_id
company_name

HOURS
employee_id
customer_id
year_month <-- e.g. 06/2006 (Date/Time field)
regular_hours
nightly_hours

I need to know the names of the employees which either do not appear in the
HOURS table in the last three months or do appear but have 0 total hours
(regular+nightly) in the three months.
The following is the way I got the result, but it is definitely too slow: it
starts displaying the results in the query results, but it hangs when I
change record page.

SELECT distinct name, company_name from employees a, cooperative b
where a.cooperative_id=b.cooperative_id AND a.employee_id not in
(SELECT distinct hours.employee_id FROM hours where year_month>#06/2006#
group by employee_id HAVING sum(regular_hours+nightly_hours)=0)

Why is this query so slow? I have:
16000 rows in HOURS,
2000 rows in employees
10 rows in cooperative

How may I get the same result with a faster query ?
Thank you
Regards

Sep 30 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Generale Cluster" <al***************@unsampdorianosoftmasters.neth a
scritto nel messaggio news:ef**********@newsreader.mailgate.org...
Hello,
I need to extract the names of the employees which have not been active
during the last 3 months from the following tables

EMPLOYEES:
employee_id
name

COOPERATIVE
cooperative_id
company_name

HOURS
employee_id
customer_id
year_month <-- e.g. 06/2006 (Date/Time field)
regular_hours
nightly_hours

I need to know the names of the employees which either do not appear in
the
HOURS table in the last three months or do appear but have 0 total hours
(regular+nightly) in the three months.
The following is the way I got the result, but it is definitely too slow:
it
starts displaying the results in the query results, but it hangs when I
change record page.
Finally I got it:
I also had a mistake in the having condition, but I've found a faster
construct instead of NOT IN:

select a.employee_id, name from employyes a left join (SELECT distinct
hours.employee_id FROM hours where year_month>#06/2006#
group by employee_id HAVING sum(regular_hours+nightly_hours)<>0) AS b on
a.lavoratore_id=b.lavoratore_id WHERE b.lavoratore_id is null order by name

This is extremely faster.
Regards




Sep 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.