Query optimization beyond indexes

75 Views Asked by At

I wrote this query that 'cubes' some data writing partial totals:

select  upper(coalesce(left(k.SubStabilimento,12),'ALL')) as Stabilimento, 
        sum(k.PotenzialmenteInappropriato) as Numeratore, 
        count(k.ProgrSdo)-sum(k.PotenzialmenteInappropriato) as Denominatore, 

 case when (count(k.ProgrSdo)-sum(k.PotenzialmenteInappropriato)) > 0 then 1.0*sum(k.PotenzialmenteInappropriato) / (count(k.ProgrSdo)-sum(k.PotenzialmenteInappropriato)) else   0  end as Rapporto,
        upper(coalesce(DescrDisciplina,'ALL')) AS Disciplina,
                    case when K.TipologiaDRG = 'C' then 'CHIR.'
                         when K.TipologiaDRG = 'M' then 'MED.'
                         when K.TipologiaDRG is null then 'ALL'
                         when K.TipologiaDRG = ''  then 'SENZA TIPO'
                    end  as TipoDRG,
        case when [Anno]=@anno then 'ATTUALE'
             when [Anno]=@anno-1 then 'PRECEDENTE' 
             else cast([Anno] as varchar(4))
             end as Periodo,
        upper(coalesce(left(k.mese,2),  'ALL')) as Mese,
        upper(coalesce(NomeMese,'ALL')) as MeseDescr

from   
        tabella k
where k.Mese <= @mese

   and k.anno between @anno-1 and @anno
   and k.RegimeRicovero = 1
   and codicepresidio=080808
   and TipologiaFlusso like 'Pro%'
group by SubStabilimento, DescrDisciplina, TipologiaDRG, anno, mese,nomemese with cube
having grouping(anno) = 0
AND GROUPING(nomeMese) = GROUPING(mese)

this groovy code is added runtime according to parameters value that have to be passed to the query:

if ( parameters.get('par_stabilimenti').toUpperCase() != "'TUTTO'" )

{  query = query +  "and upper(coalesce(left(k.SubStabilimento,12),'AUSL_TOTALE')) in ("+  parameters.get('par_stabilimenti').toUpperCase() +" )";}
if ( parameters.get('par_discipline').toUpperCase() != "'TUTTO'" )

{   query = query +  "and upper(coalesce(k.DescrDisciplina,'TOT. STABILIMENTO')) in ("+  parameters.get('par_discipline').toUpperCase() +" )";}

SQL parameters are passed by the application runtime

I did (manually) all indexing on single columns and on table primary key, I also added indexes suggested by sql server query tuner.

Now it still takes too long to execute (about 4"), now I need to have it running 8 time faster.

Is there some optimization I can do on the query? (parameters are passed by the application)

Is there a way I can precalculate execution plan,so sql server don't have to re-do it all the times I launch the query?

I really don't have an idea how to improve performances beyond whayt I already did.

I'm on SQL Server 2018 pro (so no columnstore indexes)

Here you can find the execution plan.

0

There are 0 best solutions below