Re: SQL performance problem

[M D]

Hi !

Somme précisions about this performance issue.

This request costs about 14 seconds:

select DISTINCT a.CODEFCT, t.TITRE from hopafct a, hoptext t
where LANGUE='F' and ID=' ' and CODE like 'FCT_%' and CODEFCT=SUBSTRING(CODE, 5 ,3) and CODEFCT > ' '
order by a.CODEFCT LIMIT 0,14


If I rewrite it in the following way, it costs only 0,5 second (order by replaced by group by) :

select DISTINCT a.CODEFCT, t.TITRE from hopafct a, hoptext t
where LANGUE='F' and ID=' ' and CODE like 'FCT_%' and CODEFCT=SUBSTRING(CODE, 5 ,3) and CODEFCT > ' '
group by a.CODEFCT, t.TITRE LIMIT 0,14


And if I rewrite it like below the excution time is about 0,2 second :

select x.codefct, x.titre from
(
select DISTINCT a.CODEFCT, t.TITRE from hopafct a, hoptext t
where LANGUE='F' and ID=' ' and CODE like 'FCT_%' and CODEFCT=SUBSTRING(CODE, 5 ,3) and CODEFCT > ' '
) x
order by x.CODEFCT LIMIT 0,14


Ca anymore tell me why I get such differences ???

Thanks for help.
Michel.