query working very very slow

  • Thread starter Thread starter cliff
  • Start date Start date
C

cliff

Hi , I have the following query to find employees who took part in current
training were not attended any previous five training programmes.
Selected employees sent for several training in batches. My following
query is working correctly but very very slow. In process it shows message
'recordset is not updateable' and if you try to move between records it is
also very very slow. Is there any way to work fast

SELECT [kenomer no].sr, [kenomer no].en1
FROM [keno mer no]
WHERE not Exists (select [Kenonumcount5].sr,kenonumcount5.en1
from [kenonumcount5]
where [kenomer no].en1=[kenonumcount5].en1 and [kenomer
no].sr=[kenonumcount5].sr and [keno mer no].sr is not null
);


[kenmer no] table
sr
cdate
en1

here sr is training no. cdate is training date, en1 is employee nos.
attended the training

[kenonumcount5] query is query of all those attended previous five trainings

SELECT a.sr, B.en1, (count(a.en1)/5) AS Numcount, max(b.cdate) AS latest,
max(b.sr) AS lastsr
FROM [kenomer no] AS a INNER JOIN [kenomer no] AS B ON (b.sr<a.sr) AND
(b.sr-a.sr<=5) AND (B.SR+5>=A.SR)
GROUP BY a.sr, B.eN1
ORDER BY a.sr, (count(a.eN1)/20) DESC , max(b.cdate) DESC , b.en1;

please help to solve this.

thanks in advance

t
 
Cdate is a function and therefore a reserved word so make sure to put square
brackets around your cdate field like [cdate] in the query. I doubt that it's
causing slowdowns; however, it could cause some strange errors.

You query certainly is updateable. The group by in kenonumcount5 alone will
cause that. It just means that you can change any of the records returned by
the query from within the query.

Make sure that the [kenomer no].sr field is indexed at the table.

You could try rewriting the query from the Not Exists statement to a Not In.
Often when an Exists is slow, I convert it to an In. Same goes for a slow In
statement.

Without knowing the specifics of the database, I can't be sure; however, it
seems to me that there could be a better way of finding who has completed the
training rather than a self join. Possibly the data needs to be normalized
better.
 
Back
Top