RE: COUNT(*) with UNIQUE keyword ✗ Answered: red (solid confidence) — Poster confirms a colleague's report that SELECT UNIQUE...INTO TEMP + COUNT(*) is much faster than COUNT(DISTINCT...), and explicitly asks for an explanation; no reply with an explanation is captured in the archive.

Advisory only.

Jack Parker wrote: >Interesting that this should come up. I ran into this one recently, I >used to do this by SELECT UNIQUE xxx INTO TEMP xxx then check the >SQLCA.SQLERRD[3] value. Then I 'discovered' the COUNT(DISTINCT xxx) and >tried to replace my old code.... It hung. What? So I did some tests. >The SELECT UNIQUE xxx INTO TEMP xxx ran as fast or FASTER than the COUNT, >making the COUNT appear to hang because it took so long to return. >Others? >cheers >j. >_____________________________________________________________________________ >Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA >jparker@hpbs3645.boi.hp.com I have run into a similiar experience about 2 weeks ago. My original query was "SELECT COUNT(DISTINCT xxx)..." and it took about THIRTY minutes. I changed it to "SELECT UNIQUE xxx INTO TEMP XXX " and "SELECT COUNT(*) FROM TEMP XXX" and it took ONLY 7 minutes. Any good explanation, any one?? Regards, Francis Chue fsc0@tritech.com