Sql query slow when we cast date column as datetime.

Question

Hi Friends Today i am facing some problem regarding sql query define below. select at.*currencytbl.currencyam.account_name(case when at.isauthorized is null then 'not approved' else 'approved' end) as [status] (um.First_Name + ' ' + um.Last_Name) as username from account_transaction AT inner join (select c.currencyc.id from currency c inner join currency_master cm on c.id=cm.currency_shortname) as currencytbl on at.currencyid= currencytbl.id inner join account_master AM on AM.account_id=at.accountid inner join USERS_DETAIL ud on am.Account_Code = ud.UserFCStockAccNo inner join USERS_MASTER um on ud.User_Id = um.User_Id where AT.dno in(select dno from account_transaction where transtypeid=11and creditdebit='D') and AT.creditdebit='C' and AT.currencyid != 62 and (CAST(CreatedOn AS DATE) >= CAST('07/17/2017' AS DATE) AND CAST(CreatedOn AS DATE) this query work fine when we does not take any date field column then it executes fine with in 1 sec but when we add date column with casting then it takes very long time to execute. Plz Resolve.

Puneet Kankar · Answer

you use convert in place of cast and create cluster and non cluster indexing in your table.

Tapan Patel · Answer

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. DELCARE @DtDate1 DATE = '07/17/2017' DELCARE @DtDate2 DATE = '07/18/2017' SELECT * FROM ( SELECT AT .*  currencytbl.currency  AM.account_name  ( CASE WHEN AT .isauthorized IS NULL THEN 'not approved' ELSE 'approved' END ) AS [status]  ( um.First_Name + ' ' + um.Last_Name ) AS username Cast (CreadedOn as DATE ) AS CreadedOn FROM account_transaction AT INNER JOIN ( SELECT c.currency  c.id FROM currency c INNER JOIN currency_master cm ON c.id = cm.currency_shortname ) AS currencytbl ON AT .currencyid = currencytbl.id INNER JOIN account_master AM ON AM.account_id = AT .accountid INNER JOIN USERS_DETAIL ud ON AM.Account_Code = ud.UserFCStockAccNo INNER JOIN USERS_MASTER um ON ud.User_Id = um.User_Id WHERE AT .dno IN ( SELECT dno FROM account_transaction WHERE transtypeid = 11 AND creditdebit = 'D' ) AND AT .creditdebit = 'C' AND AT .currencyid != 62 ) AS CE WHERE (CE.CreadedOn>=@DtDate1 AND CE.CreadedOn

Nilesh Patel · Answer

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

Jothish Kumar · Answer

Hi https://stackoverflow.com/questions/24659476/castdatetime-as-date-over-where-clause check thish this link.

Nitin Sontakke · Answer

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.

Manish Kumar · Answer

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 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

amit shukla · Answer

Hi puneet i am using convert instead of cast but it is taking time to execute and i am using between instead of 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.

amit shukla · Answer

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.