CODEWITHSUNDEEP
sqlsql-servert-sqloptimization
extreme4all
extreme4all

Reputation: 326

SQL Query optimalisation Merge Table

Problem: one of my queries is really slow, I can't get my head around it.

Context: I have made the following database. We want to combine all these tables, they do not have the same columns at all. so no Union, we don't want to lose any data cause the intend is to get an overview of what we need to 'clean up' so we used full join to create the merge table.

Database overview

  1. Dim data

    • merge ( inner join of the columns below)
    • users
    • roles
    • entitlements

  2. Peach database

    • merge
    • user
    • role
    • contract (this table has an objID we need later)

  3. Cics

    • Consolidated users

  4. HR

    • Zuordnung
    • users

  5. Merge

    • Merge

The 'slow' query:

(in the actual report we build we have a filter on role, fachteam, team, abteilung, bereich, geschaeftsbereich, konzernbereich) but let's keep it simple with this where statement

select 
    count(distinct CentralAccount) as 'aantal users',  
    count(distinct [Rol naam]) as 'aantal rollen',
    [FTM], [Fachteam], [TEM], [Team], 
    [ABT], [Abteilung], [BER], [Bereich], [GB], [Geschaeftsbereich],
    [KB], [Konzernbereich]
from 
    [ODS_ISRC].[dbo].[Merge]
where 
    CentralAccount is not null 
    and ftm is not null 
group by 
    [FTM], [Fachteam], [TEM], [Team],
    [ABT], [Abteilung], [BER], [Bereich],
    [GB], [Geschaeftsbereich], [KB], [Konzernbereich]
order by 
    count(distinct CentralAccount) desc

What is slow?

The current execution time is 49-50 seconds (client statistics).

What I have tried:

We already have a clustered key: ID, primary key, identity (1,1)

The query statistic showed me that it is creating an index, so I have tried pre creating this index so it would increase the performance, well I was wrong there, it somehow took sql 5 seconds (10%) longer to execute.

create nonclustered index test
on dbo.[Merge] (FTM, centralaccount)
include ([Fachteam],[TEM],[Team],[ABT],[Abteilung],[BER],[Bereich],[GB],[Geschaeftsbereich],[KB],[Konzernbereich], [rol naam])

Question:

Is there anyway I can increase the performance of this query without pre creating the table?

Extra information:

The query runs (acceptable) fast when there is data filled into the report filters, but the user usually wants an overview before filtering.

Execution plan:

Actual report query:

select 
    count(distinct CentralAccount) as 'aantal users',  
    count(distinct [Rol naam]) as 'aantal rollen',
    [FTM], [Fachteam], [TEM], [Team],
    [ABT], [Abteilung], [BER], [Bereich],
    [GB], [Geschaeftsbereich], [KB], [Konzernbereich]
from 
    [ODS_ISRC].[dbo].[Merge]
where 
    CentralAccount is not null and ftm is not null 
    and (@Role = '' and ([Rol naam] like '%'+@Role+'%' or [Rol naam] is null)  
         or @Role = '' and (Role_Beschrijving like '%'+@Role+'%' or Role_Beschrijving is null) 
         or [Rol naam] like '%'+@Role+'%' or Role_Beschrijving like '%'+@Role+'%')
    and (@Team='' and (Team like '%'+@Team+'%' or Team is null) or
         @Team is not null and (Team like '%'+@Team+'%')or
         @Team is null and ( Team is null))
    and (@Fachteam='' and (Fachteam like '%'+@Fachteam+'%' or Fachteam is null) or
         @Fachteam is not null and (Fachteam like '%'+@Fachteam+'%')or
         @Fachteam is null and ( Fachteam is null))
    and (@Abteilung='' and (Abteilung like '%'+@Abteilung+'%' or Abteilung is null) or
         @Abteilung is not null and (Abteilung like '%'+@Abteilung+'%')or
         @Abteilung is null and ( Abteilung is null))
    and (@Bereich='' and (Bereich like '%'+@Bereich+'%' or Bereich is null) or
         @Bereich is not null and (Bereich like '%'+@Bereich+'%')or
         @Bereich is null and ( Bereich is null))
    and (@Geschaeftsbereich='' and (Geschaeftsbereich like  '%'+@Geschaeftsbereich+'%' or Geschaeftsbereich is null) or
         @Geschaeftsbereich is not null and (Geschaeftsbereich like   '%'+@Geschaeftsbereich+'%') or
         @Geschaeftsbereich is null and ( Geschaeftsbereich is null))
group by 
    [FTM], [Fachteam], [TEM], [Team], [ABT], [Abteilung],
    [BER], [Bereich], [GB], [Geschaeftsbereich], [KB], [Konzernbereich]
order by 
    count(distinct CentralAccount) desc

Upvotes: 3

Views: 96

Answers (1)

extreme4all
extreme4all

Reputation: 326

By forcing parralelism i've mananaged to cut down the execution time to 9 seconds, here is an example of a similar query with 3 counts;

select distinct
count(distinct a.CentralAccount) as 'Aantal users',
    'Aantal rollen'=(
        select count(distinct [Rol naam])  from DIM.Clean_KeyMerge b
        left join DIM.Clean_User dcub
            on(b.CentralAccount=dcub.CentralAccount) 
        where dcua.[Fachteam]=dcub.[Fachteam] and dcua.[Team]=dcub.[Team] and dcua.[Abteilung]=dcub.[Abteilung] and dcua.[Bereich]=dcub.[Bereich] and dcua.[Geschaeftsbereich]=dcub.[Geschaeftsbereich] 
    ),
    'Aantal entitlements'=(
        select count(distinct EntName)  from DIM.Clean_KeyMerge c
        left join DIM.Clean_User dcuc
            on(c.CentralAccount=dcuc.CentralAccount) 
        where dcua.[Fachteam]=dcuc.[Fachteam] and dcua.[Team]=dcuc.[Team] and dcua.[Abteilung]=dcuc.[Abteilung] and dcua.[Bereich]=dcuc.[Bereich] and dcua.[Geschaeftsbereich]=dcuc.[Geschaeftsbereich] 
    )
,dcua.[Fachteam],dcua.[Team],dcua.[Abteilung],dcua.[Bereich],dcua.[Geschaeftsbereich]from DIM.Clean_KeyMerge a
left join DIM.Clean_User dcua
    on(a.CentralAccount=dcua.CentralAccount)

Upvotes: 1

Related Questions