0

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.

DDS
  • 2,340
  • 16
  • 34
  • SQL Server will use a cached plan, if it thinks it's a good plan... [Read this on getting help with a slow query](https://www.brentozar.com/archive/2009/03/getting-help-with-a-slow-query/) and get back to us with the execution plan, server settings, etc. [Here is an answer](https://stackoverflow.com/a/52896116/6167855) with some suggestions based on an equally broad question. – S3S Oct 23 '18 at 16:09
  • `I really don't have an idea how` - by starting from the beginning. Identify issues at first, then solve them. Find out which part of this query adds what workload, find ways to improve that code. `I did all indexing on single columns` - this is a no go. "Indexing" is not supposed to result in creating an index to each and every column. – Ivan Starostin Oct 23 '18 at 16:46
  • "...I did all indexing on single columns..." Why did you do that? That's a really bad strategy in the first place. You need to create indexes according to the usage of the table. And you should create the bare minimum of them on OLTP applications. – The Impaler Oct 23 '18 at 21:13
  • I edited: I did 'manually' indexes on single columns (used by filters and group by), then, because query was slow, indexes on multiple columns as suggested by the SSTuning tool – DDS Oct 24 '18 at 07:27

0 Answers0