February 11, 2016 at 10:30 am
Is sql function such as Month, Year or day sucks the query performance if we use these in where clause.
If yes please suggest an alternative options???
I am using sql server 2008 r2
Thanks
February 11, 2016 at 10:40 am
itsgaurav (2/11/2016)
Is sql function such as Month, Year or day sucks the query performance if we use these in where clause.If yes please suggest an alternative options???
I am using sql server 2008 r2
Thanks
Not sure why you have this question in the section you do. It belongs in a sql location.
That being said, your question is extremely vague. Yes functions with columns as arguments in a where clause will render your query nonSARGable and as such the performance will suffer. We can help you find an alternative but you have to provide some actual details first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2016 at 10:52 am
Select Jcdtl.mdoc_no,Jcdtl.doc,no,jcdtl.doc_dt,jcdtl.del_dt from jcdtl
Where MONTH(jcdtl.doc_dt)=3 AND YEAR(jcdtl.doc_dt)=2014
In this table have approx 10 lacks rcords
February 11, 2016 at 11:06 am
itsgaurav (2/11/2016)
Select Jcdtl.mdoc_no,Jcdtl.doc,no,jcdtl.doc_dt,jcdtl.del_dt from jcdtlWhere MONTH(jcdtl.doc_dt)=3 AND YEAR(jcdtl.doc_dt)=2014
In this table have approx 10 lacks rcords
Simple to remove these functions and add some range logic.
Select j.mdoc_no
, j.doc_no
, j.doc_dt
, j.del_dt
from jcdtl j
Where j.doc_dt >= '20140301'
and j.doc_dt < '20140401'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2016 at 11:21 am
Sean Lange (2/11/2016)
itsgaurav (2/11/2016)
Select Jcdtl.mdoc_no,Jcdtl.doc,no,jcdtl.doc_dt,jcdtl.del_dt from jcdtlWhere MONTH(jcdtl.doc_dt)=3 AND YEAR(jcdtl.doc_dt)=2014
In this table have approx 10 lacks rcords
Simple to remove these functions and add some range logic.
Select j.mdoc_no
, j.doc_no
, j.doc_dt
, j.del_dt
from jcdtl j
Where j.doc_dt >= '20140301'
and j.doc_dt < '20140401'
That'll do it. And if you have an index on doc_dt, the query will be able to use it. Include doc_no and mdoc_no and it's a covering index. Of course, you'll want to design your indexes so they're used efficiently, which depends on how you use the table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy