ambiguity performance problem

The first query execution time less than 1 second
But the second query takes around one minute

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST in (5,-999)) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

Note 1 : value -999 deos not exist in the table field ACC_STATUS_LAST
Note 2 : value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 3 : the number of rows in each table around 15000000

Hi,

didi you have a look at the execution plan ? The execution plan can be seen in QA (using SQL 2k) or Query Pane (using SQL2k5) graphically or by using the statement SET SHOWPLAN ON before isseing the statement.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Yeah, and then post it. I would love to see how an equality query is beat out by an OR query. This looks very much like it is probably an optimizer issue, unless the plan shows something obvious...|||

thank you for this notice ,

because there is a big difference between the access plan for both queries

and I don't now why this happen ,