0

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.

s ab
  • 7
  • 2
  • You need to enclose the date literals in single quotes. Note that CAST(2022-05-21 as datetime) returns `1905-06-20 00:00:00.000`. – Dan Guzman Jun 21 '22 at 13:39
  • please post query plans and full repros if you want help on plan performance. – Conor Cunningham MSFT Jun 21 '22 at 14:19
  • "IsCash hast bit datatype, and because of bit datatype, I don't create index on it." - it depends. Of the rows in your table, how many of them have IsCash = 1? If it's small in proportion to the whole table, it could be worth creating a filtered index. – Ben Thul Jun 21 '22 at 16:28

1 Answers1

0

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.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • You don't want to do where function(col) = const in SQL - it is an anti-pattern. You would be better to do where day > datediff(day, getdate()-30) or similar. This can match indexes. That said, the OP should probably post schema and indexes since that is more likely their problem – Conor Cunningham MSFT Jun 21 '22 at 14:22
  • Is there a reference document, or book, that establishes SQL Server patterns? – benjamin moskovits Jun 21 '22 at 14:46
  • This isn't specific to SQL Server, but here's a SO post that covers this particular pattern: https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable – Conor Cunningham MSFT Jun 21 '22 at 17:45
  • I know what Sargable is. Is there a reference or document that generally lists SQL Server patterns? – benjamin moskovits Jun 22 '22 at 02:54