Select Best Indexes For Run a Query-CodePudding

How can I get better performance in running the following query?

WITH cte AS
(
    SELECT
         CustomerId
        ,DocumentNumber
        ,IsCash
        ,date 
    FROM Table1
    WHERE date > '2022-05-21' AND date < '2022-06-21'
)
SELECT DISTINCT
Customerid
FROM cte A
WHERE EXISTS
(
    SELECT
        1
    FROM cte B ON A.DocumentNumber = B.DocumentNumber
    WHERE B.IsCash = 1
)
AND A.IsCash = 0

Table1 stores data in daily basis and by this query I want to retrieve data of last month.
IsCash hast bit datatype, and because of bit datatype, I don't create index on it. Should I do that?

I just have nonclustered index on date and in execution plan index seek happen, but it is so slow.

CodePudding user response：

SQL Server is probably doing a scan (reading the entire table) on the CTE because of the condition a.IsCash being searched and does table1 and table 2 have indexes on Document Number? You want to avoid scans.

Look at the query plan and see what kind of joins SQL Server is using and try to convert the joins to a seek. A seek on a primary key is ideal but usually, any kind of seek (where a limited number of rows are read) is preferable to a scan.

You mentioned that you are trying to search for the last months data but you are searching ten days worth. You may want to explore using the datediff functions and with the getdate() function - something like this:

select * from table where   datediff(day,getdate()-31, date ) < 30

Please play with the datediff function to make sure it works the way you want it to.

CodePudding user response：

If I understand correctly you can replace the exists with window functions:

with cte as (
    select customerid
         , iscash
         , max(iscash) over (partition by documentnumber) as max_iscash
    from t
    where date > '2022-05-21' AND date < '2022-06-01'
)
select distinct customerid
from cte
where iscash = 0
and max_iscash = 1