Are Union queries inherently slower to run?

[SparklyPrincess6969]

Hi everyone,

My client's database has a form whose datasource is a maze of dozens of interlinked queries, and naturally the thing is crushingly slow to run. (I didn't design this thing!)

Now one particular bottleneck I think I've discovered, is a Union query which stacks ten separate queries on top of each other. My question is, if I (somehow) replace it with a query which works on joins but returns an identical recordset, is this on its own likely to result in a speed improvement?

P.S. Apologies if I've posted this in the wrong subforum.

P.P.S. I also realize this might be a bit of a "how long is a piece of string" type question; I can give more specific details if anyone wants.

 

[plog]

Yes it is likely to improve performance. Doesn't mean it will, but I'd bet it does. However, the main reason to do this is to increase your performance. When things go wrong, it is so much simpler to look into one query to find the issue than it is to go on a scavenger hunt through 10 queries hoping you come across the thing making it incorrect.

So, even if performance isn't helped, the correct thing to do is eliminate that UNION query.

 

[Galaxiom]

By default a Union query will only return distinct records so it can take a long time to check for duplicates.

Use UNION ALL to prevent this check.

 

[Galaxiom]

Now one particular bottleneck I think I've discovered, is a Union query which stacks ten separate queries on top of each other.

This strongly suggests that the database has a structural problem which needs to be addressed.

 

[SparklyPrincess6969]

By default a Union query will only return distinct records so it can take a long time to check for duplicates.

Use UNION ALL to prevent this check.

The query did in fact use UNION ALL to begin with, but it's good to know what that actually does so thanks anyway.

This strongly suggests that the database has a structural problem which needs to be addressed.

Oh boy howdy, does it ever. There are some really fundamental design flaws which would take ages to adequately fix while either preserving or recreating their current functionality, and there's no scope for doing that in the current time frame. I'd like to do it, but I wd need to convince the decidedly non-techy management that it's worth paying for...Anyway thanks a whole lot!