>Hi everyone,
>
>Here is my problem:
>
>I want to find when duplicated records are mistakenly created by my application.
>
>So I plan to check my database before the user gets into the application and after he/she exists.
>
>The database contains employee schedules. Each record contains the position number (PosNum), the date of shift (Djh) and a number indicating which day was used to generate that particular record (Njh). I have about 50,000 records.
>
>And this is the SELECT statement:
>

>    SELECT cHorPosNum, dHorDjh, nHorNjh, COUNT(*) AS CNT ;
>      FROM Horaire ;
>     GROUP BY cHorPosNum, dHorDjh, nHorNjh ;
>    HAVING CNT > 1
>

>
>The combinaison of cHorPosNum+dtos(dHorDjh)+str(nHorNjh,3) should be unique.
>
>I already have the following index tags:
>DHORDJH,
>CHORPOSNUM,
>CHORPOSNUM+DTOS(DHORDJH)+STR(NHORNJH,3)
>
>On a my pc it takes about 1.5 seconds to complete
>for my users are on a 10Bt network it will be longer but acceptable
>but for other users who are on a RNIS network it may be too long.
>
>Does anyone have an idea on how I can optimize this search.

You've basically created an insoluble problem, since the HAVING clause requires the initial selection to take place before the HAVING clause can filter the desired result set. In order to do the GROUP BY, you have to pull across every single record to create the result set, since no filtering takes place in the WHERE clause (there is no WHERE clause, so every single record is considered as a part of the result set.)

Rushmore optimization isn't going to help here. Your best bet would be to rethink the problem so that a summary record that kept a count of records matching the key for summarization exited, which could be used as a filter in a WHERE clause, which takes place before the set of records needed to perform the GROUP by is performed, rather than the filtering occuring in the HAVING clause, which occurs only after the GROUP BY operation occurs, reducing the number of records passed over the wire.

If this were not 2.6, I'd strongly consider the use of a database backend, so that only the solution set had to pass over the wire.

>
>Remember this this is fpw 2.6 code.
>
>Thanks in advance