-9

I have this query it is taking a lot of time. Please help me to optimise this query.

select * 
from t_Packages 
where PackageTypeID = 4  
  and dateadd(day, 60 + ctstracker.dbo.fn_getNonBankHolidaysCount(receiveddatetime, dateadd(day, 60, receiveddatetime)), receiveddatetime) >= GETDATE()
jarlh
  • 42,561
  • 8
  • 45
  • 63
Waqar Laghari
  • 1
  • 1
  • 3
    You might want to provide some information about your table structure and data. – Steve Dowling Nov 25 '15 at 05:23
  • 1
    Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Nov 25 '15 at 05:35
  • I am using sql server database. – Waqar Laghari Nov 25 '15 at 05:47
  • 1
    Sir I t is very simple table just contains some columns. the actual problem is function in where clause. – Waqar Laghari Nov 25 '15 at 05:48
  • 1
    First, do not use "*" to select columns, the table won't be indexed. Second, what's fn_getNonBankHolidaysCount?. Third, please post table structure as well as the indexes and FK applied. Forth, what do you mean by a lot of time? how many records? How long it took? – User2012384 Nov 25 '15 at 06:08
  • Somebody said select * won't cause performance, here's some article related: http://stackoverflow.com/questions/487578/performance-issue-in-using-select http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc – User2012384 Nov 25 '15 at 07:58
  • As I already told you that time delay is not due to records are table structure. we have done index on PackageTypeID and receiveddatetime so there is no issue of indexing. Problem is when we call function in where clause then it run in more then 10 minutes. now I am all asking that how can I Call function in where are without to get same out put which I am getting using this function in where clause. – Waqar Laghari Nov 25 '15 at 08:06
  • You say that the problem is the function, but you don't include its code. How is anyone supposed to be able to help you? – James Z Nov 29 '15 at 10:44

1 Answers1

0

As you have determined, calling a function in your where clause as you're doing kills performance. Essentially, the db has no option but to evaluate that function for every row in the table to see if that predicate evaluates to true. The general concept is called "SARGability" ("SARG" being short for "search argument").

Ben Thul
  • 31,080
  • 4
  • 45
  • 68