Hi,

At August 11, 2004, 18:02, EvelyneGirard wrote:

> wrote:
>>
>> It is as designed. Because the second WHERE clause is transformed
>> internally to: (e.id_visite=8) or (e.id_visite=10) or
> (e.id_visite=10546)
>> Which will use an index. And, with a subquery, it's hard to use an
>> index in the same manner.
>>
>> But, if I'm not mistaken the IN clause with a subquery should be be
>> transformed to:
>>
>> where exists(select 1 FROM Patient
>> left join Visite on (Patient.ID_Patient = Visite.ID_Patient)
>> WHERE (Patient.ID_Patient = '45691')
>> (Patient.id_visite=e.id_visite))
>>
>> So what happens if you change the first WHERE clause to what I've
>> written above? Do you have an index on Patient.id_visite?
>>

> There is no field patient.id_visite.

Sorry, I couldn't tell by viewing your UPDATE statement, I simply
guessed. So, I presume that it should be:

where exists(select 1 FROM Patient
left join Visite on (Patient.ID_Patient = Visite.ID_Patient)
WHERE (Patient.ID_Patient = '45691')
(Visite.id_visite=e.id_visite))

And, what happens, with the above WHERE clause? And, I'm presuming
that there is an index defined on Visite.id_visite.

> But if there was one, I'd have
> the same result ... examen would be scanned completely... I realise I
> will have no other choice and have to do this in Delphi instead of
> doing it with one SQL...

It can be done in one DML statement, but sometimes it can require some
experimentation to get the most optimum performance.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)