SQL Collate causing lag?

[sdukes_88]

Hi,

I am struggling with an issue I have.

We are a College which merged with another college and I am doing a chart with 3 years of student numbers. Easy right?

Trouble is 2014, 2015 & 2016 data (a) comes from one source and 2014 & 2015 (b) another source (the old college student numbers).

My data is a direct query into PowerBi via a SQL view and I have optimised the SQL query as much as I can.

(a) contains 1,160 summarised rows and (b) contains 74 summarised rows.

Because (b) came from a different SQL collation I am summarising (a) and using UNION ALL to join to (b) with COLLATE

My problem is when you add a slicer and try and select by site any data in (a) loads relatively OK in terms of speed but (b) data just lags and takes far too long from a SQL query/view which takes 6 seconds to run in SSMS.

I don't know what I can do to get the data working - any help appreciated!

Thanks

Simon

[v-shex-msft · Accepted solution]

Hi @sdukes_88,

You can try to use query editor to analysis with these records and create a new table, then use analysed table as the source of slicer, it may improve the performance.

In addition, you can also try to modify the table structure to improve the performance.

Reference links:
How to make a union view execute more efficiently?

Cross Database Unions

Regards,
Xiaoxin sheng