Sql query slow when we cast date column as datetime.

Always try to avoid complex functions in WHERE clause.
Think like this, on each row, it tries converting date column to DATE type twice for CreatedOn and two more times for static date( 07/17/2017). It really depends on the number of rows in your table (i.e. how bad it can screw up your query)
Your current approach is correct with temp tables but you can also do something like below. I have created just a subquery to include date field as DATE and later filter based on two date variables.

 

 

User Convert Instead of Cast Date

 

Check Relationship & Constraint of table 

 

why are use same table in from & In Sub query account_transaction 

 

avoid to use of NOT IN  & IN Query 

 

Try to Use NOT EXISTS or EXISTS for Fast Performace  

Hi,

 

 https://stackoverflow.com/questions/24659476/castdatetime-as-date-over-where-clause

 

check thish this link. 

You said that you have index on the table. But what are the columns. Is there an index on column CreatedOn. I believe not.

 

You have two options: 1 Create a non-clusted index on CreatedOn or 2 Include the CreatedOn column in any of the existing non-clusted indexes which are being used while executing this query.

 

If you are using SQL Server and SQL Server Management Studio, it is easy to see which indexes used using 'Include Execution Plan' button.

Avoid function on left side i.e on column. Your query can be written as

 

and CreatedOn  >= CAST('07/17/2017' AS DATE) AND CreatedOn  < CAST('07/18/2017' AS DATE)) 

 

In yellow part i have replaced 17 by 18, mean to say when you converting datetime column in date data type it will ignore secs i.e it will return value date record less then 18th July.

 

Also avoid subquery that you have written.You can use join 

Hi puneet,

i am using convert instead of cast but it is taking time to execute and i am using between instead of <=  operator.

if you know any other solution plz let me know.

i have resolved this issue , we have taken a temp table , in this we store the data after that

we are applying filtering with date so it is working fine but it is temporary solution. 

we have already use indexing on my table. but my question is different.

without date field it is working fine but when we use casting then it execute slow.

why.