A QUERY THAT RUN ON DB2 THAT HAVE MORE PERFORMANCE THAN SQL SERVER 2000

The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second

so how i can incease the performance for SQL server

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 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index

Um, why are you using strings to store the ACC_KEY1? Numeric fields are much faster.

I would suggest that you drop all your indexes that relate to that query. Then run the Database Engine Tuning Advisor (or whatever its called in SQL 2000) to determine what the right indexes are. Unless you know SQL Server intimately, it can generate better indexes than you can by hand.

Jonathan

|||

thank you for you advice , i use the tuuning wizard but it did not improve the performance

- and acc_key1 could contain a letter so it must be a string

|||

You use the same indexes, but what does those indexes look like?

What is the volume to be returned? Is the expected output close to a million rows? (all the '5's)

How do you measure the time? Do you look at the server for the time it takes to resolve the query, or do you measure at the 'end-point'? (ie if you select... and wait until a million rows has been drawn on the screen, or similar)

/Kenneth