Ideas on how to improve SQL query runtime

Transact-SQL
1

Hi,
i am just looking for a way of improving the performance of the attached query.
currently it takes about 4 minutes to return results.

with AssetCount
as
(select AssetType
,AssetCategory
,ObjectType
,CatalogProfile
,MainStatus
,Ownership
,Count(Distinct AssetID) CountOfAsset
from moskpi.MDAssetHeaderInfo
Group by AssetType
,AssetCategory
,ObjectType
,CatalogProfile
,MainStatus
,Ownership),
HeaderStat
as (select AssetType
,AssetCategory
,ObjectType
,CatalogProfile
,MainStatus
,Owner
,AssetID
,CharDesc
,CharValue
from (
select AssetType
,AssetCategory
,ObjectType
,CatalogProfile
,MainStatus
,Ownership Owner
,AssetID
,convert(nvarchar(100),Ownership) Ownership
,convert(nvarchar(100),Installdate) InstallDate
,convert(nvarchar(100),FunctionalLoc) FunctionalLoc
,convert(nvarchar(100),Room) Room
,convert(nvarchar(100),OperatingCircuit) OperatingCircuit
,convert(nvarchar(100),OperatingVoltage) OperatingVoltage
from moskpi.MDAssetHeaderInfo
--where CatalogProfile = '7501'
) c
unpivot ( CharValue for CharDesc in ("Ownership","Installdate","FunctionalLoc","Room","OperatingCircuit","OperatingVoltage")) unpvt
)
select ac.*, a.CharDesc, case when CharCount is null then 0 else CharCount end CharCount
from AssetCount ac
cross join (select distinct CharDesc from HeaderStat) a
left outer join (select AssetType
,AssetCategory
,ObjectType
,CatalogProfile
,MainStatus
,Owner
,CharDesc
,Count(CharValue) CharCount
from HeaderStat
Group by AssetType
,AssetCategory
,ObjectType
,CatalogProfile
,MainStatus
,Owner
,CharDesc ) hs
on ac.AssetType = hs.AssetType
and ac.AssetCategory = hs.AssetCategory
and ac.ObjectType = hs.ObjectType
and ac.CatalogProfile = hs.CatalogProfile
and ac.MainStatus = hs.MainStatus
and ac.Ownership = hs.Owner
and a.CharDesc = hs.CharDesc

thank you in advance.

2

is this for Microsoft SQL Server ?

3

Hi,
Yes it is.

4

Can you post an execution plan for the current query?

Also, can you post table structures for the tables accessed in this query (column names, types, and any indexes). It would also help if you could post some sample data and the expected output.

My first thought is that your DISTINCT columns are not indexed and therefore need to be sorted. If the underlying tables are around 100K+ rows, then you're probably spooling to tempdb during the sorts. You are also selecting DISTINCT from the HeaderStat CTE, which is a CTE against the AssetCount CTE, so possibly more than one spool. That's why having sample data and output is important, it's possible this could be simplified.

5

Hi Robert,
i dont have access to the execution plan so thats out.
i am sending you a picture of the table structure.
How do i send sample data in here?

thank you.Capture3

6

Can you give some sample data as INSERT statements NOT as a picture of data, say 100 rows. And then the results you want from that data.

1 Like
7

hi Scott,
the results i am getting are fine. What i am asking is how can i improve the runtime of the query. Currently it takes about 4 minutes to return the data.
Another bad thing here is that we do not have a space where we can create tables...
one more thing, this query is being used by a power bi dashboard to visualize the data. (not that it has something to do with the runtime of the actual query...)
Could it be by creating a stored procedure the runtime would improve?
thanks.

8

I get that. But for me to run it with a lot of data myself, I need some sample data. Then however long the original code runs, I can tell if my rewrite is significantly faster.

How can I know if I'm improving the speed w/o being able to actually run the query?

9

Can you please give us direct access to your sql server?

10

Are you joking with that question?

11

hehehe i think he might be. :slight_smile:

12

Yes. Cause usually they respond of course I cant. Then I answer:then please provide ddl dml" :grin: