Click here to Skip to main content
14,696,499 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I Have a problem with my query ,


It was fast when i using constant data but slow when i use a variable


example

Select * from T_report where convert(date,created_date) >='2020-05-01'
and convert(date,created_date)<='2020-05-30'

this query run fast ,


but when i use like this


declare @startdate as date
declare @enddate as date

set @startdate ='2020-05-01'
set @enddate='2020-05-30'

Select * from T_report where convert(date,created_date) >=@startdate
and convert(date,created_date)<=@enddate

What I have tried:

how i can solve this?

I have try using option(recompile)

but still slow
Posted
Updated 11-Jun-20 3:23am
Comments
Patrice T 10-Jun-20 22:31pm
   
Show table structure.

Select * from T_report where convert(date,created_date) >='2020-05-01'
and convert(date,created_date)<='2020-05-30'

The problem I see is that conversion is done on wrong side of conditions.
Conversion must be done on constant, not on datafield (created_date).
The difference is that SQL compiler can fully optimize query when datafield is unchanged in condition.
   
In addition to Solution 1 - why are you converting column created_date - that column should be of type Date or DateTime
. . . always use the right data type for the job. . . It can have a significant impact on efficiency, performance, storage and further database development.
Change your table schema to make create_date a Date and your query becomes
Select * from T_report where date >= @startdate and created_date <= @enddate
Taking the convert function calls out of the WHERE clause will make a big difference to the speed.
If you already have created_date declared as a DateTime then instead of casting/converting the column in the WHERE clause, get clever with your local variables i.e.
set @startdate ='2020-05-01'
set @enddate='2020-05-31' -- Note this is the day after the end date!

Select * from T_report where created_date >=@startdate
and created_date<@enddate  -- Note only less than not less than or equal 
One last final note - it's good practice to list the columns you want in your query rather than using * - it stops you getting caught out if the schema changes down the line
   
Comments
Patrice T 11-Jun-20 8:30am
   
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900