One INSERT with UNIONS or multiple INSERTS?

I have a sql function that returns a table. The table is populated via 6 or so reasonably complex statements.

Is it better to UNION together these statement so there is only 1 insert or are these better kept separat开发者_如何学编程e?

Or does it make no difference whatsoever?

I will suggest separate inserts. I found that performance of insert with Union All is very poor as compared to separate inserts. I tried inserting around 3500 records using both the approaches, separate insert statements were 4 times faster. (It may vary on number of columns in your insert)

I'd just UNION ALL them - the key there is not a UNION (which could be less efficient by deduping), but a UNION ALL.

Instead to think by yourself what is the best solution i suggest as a tip to use a properly tool.

In SQL server using the Managment Studio you can evaluate the performance by Display Estimated Execution Plan. So in this way you can really see the differences between your 2 cases and select the best one by observing the results.

Actually, it does make a difference.

When you have separate INSERTs, you have to take care to do it inside a transaction, so that you have a consistent state at all times. But, since they're separate, the amount of memory required for the server to complete is less because every INSERT has it's own result set - when the query completes the server is free to perform another query etc...

When you have everything inside one big query joined by UNION ALL the server would need more temp tables and more memory to complete. But, this way you have something of a 'bulk' insert, and could result in faster insert.

Personally, I'd go with separate inserts inside a transaction. Sometimes just by having something done simpler is way more beneficial in terms of maintainability and debugging than some minor performance gain (if any, depends on the number of rows actually getting inserted)

Within a User Defined Function, I don't see a reason to use one over the other. A UNION has the potential to be more constraining than separate statements...

I'd like to see the details of the function, and it's use, because it's possible it might not be necessary at all.

UNION v/s UNION ALL are not functionally same. Exercise caution when using UNION as it will try to de-dup and potentially that may not be the result you're originally looking for.

Regarding the question, multiple INSERT(s) might be faster given the lesser number of records compared to UNION ALL