10774 - function in where

Hi experts:

i have this query, but is very slow
SELECT dbo.f_semana(fecha_consumo)AS SEMANA,
       TIP_TRABAJADOR,
         COUNT (TIP_TRAbAJADOR) AS CANTIDAD,
         cod_concepto
        from tbcom_ConsumoComedor1 a
            --inner join tbcom_consumosProgramados a.cod_concepto=b.cod_concepto
           WHERE dbo.f_ANO(fecha_consumo)='2014'
             AND COD_CONCEPTO='2'
             and tip_trabajador='o'
             GROUP BY dbo.f_semana(fecha_consumo),TIP_TRAbAJADOR,cod_concepto
            
1. this is the table: view file _strucTABLE.png
2. THis is the index
indices tabla tg_anosem:
index_name      index_description                                      index_keys
PK_Tg_AnoSem      clustered, unique, primary key located on PRIMARY      Ano, Semana

3.
indices tabla tb_consumocomedor1
index_name                      index_description                                      index_keys
PK__tbcom_Co__48E896052C2F1D91      clustered, unique, primary key located on PRIMARY      ID_CONSU
PRUEBA_1                      nonclustered located on PRIMARY                              TIP_TRABAJADOR, COD_CONCEPTO

4. This is the function
funcion f_ANO:
  ALTER FUNCTION  [dbo].[F_ANO] (@FECHA DATETIME)  
returns ANO
as
begin
declare @ANO CHAR(4)
 select @ANO=ANO from Tg_AnoSem
      where @FECHA between Fec_Inicio and Fec_Fin
return (@ANO)
end      

fruncion f_semana:
  ALTER FUNCTION  [dbo].[F_SEMANA] (@FECHA DATETIME)  
returns SEMANA
as
begin
declare @SEMANA CHAR(2)
 select @semana=semana from Tg_AnoSem
      where @FECHA between Fec_Inicio and Fec_Fin
return (@SEMANA)
end

5. attched sample data: tabla tg_anosem and tbcom_consumocomedor1.xlsx
-strucTABLE.png
tabla-tg-anosem-and-tbcom-consumocomedor
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Try it this way:
SELECT  dbo.f_semana(fecha_consumo) AS SEMANA,
        a.TIP_TRABAJADOR,
        COUNT(a.TIP_TRAbAJADOR) AS CANTIDAD,
        a.cod_concepto
FROM    tbcom_ConsumoComedor1 a
        --inner join tbcom_consumosProgramados a.cod_concepto=b.cod_concepto
WHERE   a.fecha_consumo >= '20140101'
		and a.fecha_consumo < '20150101'
        AND a.COD_CONCEPTO = '2'
        AND a.tip_trabajador = 'o'
GROUP BY dbo.f_semana(a.fecha_consumo),
        a.TIP_TRAbAJADOR,
        a.cod_concepto

Open in new window

0
Anthony PerkinsCommented:
See if this is any better:
SELECT  sa.semana as SEMANA,
        a.TIP_TRABAJADOR,
        COUNT(a.TIP_TRAbAJADOR) AS CANTIDAD,
        a.cod_concepto
FROM    tbcom_ConsumoComedor1 a
        --inner join tbcom_consumosProgramados a.cod_concepto=b.cod_concepto
		OUTER APPLY (
			SELECT TOP (1) s.semana
			FROM	Tg_AnoSem s
			WHERE	a.fecha_consumo BETWEEN s.Fec_Inicio and s.Fec_Fin) sa 
WHERE   a.fecha_consumo >= '20140101'
		and a.fecha_consumo < '20150101'
        AND a.COD_CONCEPTO = '2'
        AND a.tip_trabajador = 'o'
GROUP BY sa.semana,
        a.TIP_TRAbAJADOR,
        a.cod_concepto

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
no points please. Great suggestions Anthony!

@enrique_aeo

I'm pretty certain your on-going studies have covered "sargable predicates" and the following is NOT sargable:

WHERE dbo.f_ANO(fecha_consumo)='2014'

"Rule of Thumb:
Avoid applying functions on data values in a sql condition.
"


Anthony has also (in the second suggestion) provided an alternative to grouping by the result of a function. That suggestion is good but when using TOP (n) an order is required otherwise the results are unpredictable.  Your existing function doesn't supply this but an ORDER BY should be included in the OUTER APPLY

{+ edit}
perhaps you are assuming there is only 1 record that will meet this query
 select @semana=semana from Tg_AnoSem
      where @FECHA between Fec_Inicio and Fec_Fin
return (@SEMANA)

but if that is not true you need top (1) and an order
0
Anthony PerkinsCommented:
suggestion is good but when using TOP (n) an order is required otherwise the results are unpredictable.
True.  However, I was emulating the code given:
declare @SEMANA CHAR(2)
 select @semana=semana from Tg_AnoSem
      where @FECHA between Fec_Inicio and Fec_Fin

Open in new window

Which does not care about the ORDER BY either and is merely picking up the "last" one.
0
PortletPaulfreelancerCommented:
yes, its all one can do without feedback, my edit was intended to indicate that. cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.