Hi,

my table has about 200,000 records. The following statement takes forever:

SELECT DISTINCT A, B, C FROM table WHERE
DATA BETWEEN DATE '2007-05-01'AND DATE '2007-05-31' AND
PR_CODE = 'BHZ' AND
EM_CODE IN ('02','04','05','07','12') AND
VALUED = 'S'
ORDER BY A, B, C

But this one executes fast:

SELECT A, B, C, COUNT(*) FROM table WHERE
DATA BETWEEN DATE '2007-05-01'AND DATE '2007-05-31' AND
PR_CODE = 'BHZ' AND
EM_CODE IN ('02','04','05','07','12') AND
VALUED = 'S'
GROUP BY A, B, C

There are index for the fields PR_CODE, EM_CODE and DATA.

ElevateDB 1.05

Thank you

Sergei Safar

Sergei


I bet Tim asks you to post the two query plans.....

Roy Lambert

Hi all,

sorry for this post. I really don´t know what happened. The SQL statement was too slow inside my app so I copied the SQL statement to ElevateDb
Manager and executed it. It was too slow too so I had to ask windows task manager to shut ElevateDB Manager down and this happened many times.

But now it´s working fine, even inside my app. I was using local access.

Thank you

Sergei Safar

Sergei,

<< sorry for this post. I really don´t know what happened. The SQL statement
was too slow inside my app so I copied the SQL statement to ElevateDb
Manager and executed it. It was too slow too so I had to ask windows task
manager to shut ElevateDB Manager down and this happened many times.

But now it´s working fine, even inside my app. I was using local access. >>

My guess is that you were using a different database, a different query, or
else the machine was taxed by some other process, thus causing the slowdown.

--
Tim Young
Elevate Software
www.elevatesoft.com