Top 5 Union Query gives poor performance

[Stejawh]

Hi,                                                                     
                                                                       
I have a requirement in a report where the users have an option to run the report for top 5 Fleets (by one of four metrics) or to switch the filter off.  Each metric has it's own query which is then unioned up into a Feeder top 5 Query.  If one of these filters is selected then this query will filter the rest of the report.  My problem is that this slows down the report to unmanageable levels.   

We are using 8.4.28.15 (FP1) with a SQL Server DB.

Regards,

Steve

[blom0344]

The union itself will allow you to either remove duplicates (UNION)
or keep them (UNION ALL). The latter option will have better performance since the resultset will not have to be sorted to remove duplicates. So, change this accordingly and perhaps performance will be a bit better..

[Stejawh]

Hi blom0344,

Thanks for the suggestion but i do not think this is the issue.  There shouldn't be any duplicates due to the nature of the prompt which drives this query. 

I should've given a bit more detail.  This Section of the report is driven by a prompt where a user has the option to see the report by the Top 5 Fleets by one of four measures or none at all which in turn will return all fleets within their prompt range (Now if no Top 5 measure is selected the report returns in acceptable time). 

I have a query each for each top 5 by (Measure) which is then Unioned up into a Master Query.  Now this query if one of the Top 5 options has been selected will only have 5 Fleet ID's in it due to the other 3 queries not being populated.  The performance issues seem to start when i join this master to the reporting queries On Fleet ID.

Thanks,

Steve