Union ALL Query for 1 Table

[slenish]

Hello all,

I am wondering if its possible to do a Union All Query for 1 table instead of mulitiple tables.

I have 7 columns that im trying to Union. I have it working to an extent right now but its way to slow. I'm wondering what I can do to speed it up. (Im sorting threw about 300,000 records)(Time it takes is a min or more) I also noticed that its doubling the record count when i do it this way which could be why its taking so long.

Also Im using another query to pull the information from this one to fill in a form with the results.

Thanks in advance!

Here is what i have so far,

SELECT  [Date],[Number],[Name1],[Name2],[Name3],[Name4],[Count]
FROM [Table]
ORDER BY [Date]
UNION ALL SELECT  [Date],[Number],[Name1],[Name2],[Name3],[Name4],[Count]
FROM [Table]
ORDER BY [Date];

[gershwyn]

What exactly are you trying to achieve by this?

Yes, it's possible to do, but as you mentioned it is doubling all the records. That's what a Union query is for - it combines the records from various sources into one.

[Stewart Ross]

I echo what gershwyn has said. Unioning the same table? Why would you consider doing that, and for what purpose?

Strictly, SQL should not return duplicate rows. It is a flaw of the implementation of relational databases that a plain SELECT statement can return identical rows (as generated by your UNION ALL on the same table). If you used SELECT DISTINCT in your query you'd find that the duplicates vanish, and the UNION ALL would simply be a very inefficient way to run a SELECT query.

As gershwyn said, UNION queries are intended to create single-table output from multi-source inputs - effectively ORing the results of independent tables or queries together. UNION was never meant for use with the same table.

-Stewart

[slenish]

Thanks for the replys :D

Well the reason im trying to use a Union query is because it should be faster than a normal Select Query for searching through records. I built a Union query for another part but it was putting two tables together and was really fast so i was trying to use it again for one table but found there were issues.

I will try the Select Distinct and see how that goes any other ideas I could try??

thanks for the help :D

[Stewart Ross]

Hi. I'm not clear by what you mean by 'searching'. If you are using a WHERE clause to select matching fields (for example, to find partial matches for a person's name) then neither UNION nor SELECT DISTINCT are pertinent; they are for very different purposes and don't have anything to do with the speed at which a match can be found.

UNION is, as already mentioned, intended for separate (non-joined) tables being ORd together. If applied to the same table it could decrease performance drastically, as instead of the query engine searching through N rows it would potentially have to search through N x N = N^2 rows - although its internal optimisation may help prevent that worst-case from arising.

Again, if you could advise what you are really trying to do we'd be happy to help in coming up with an efficient way to do it.

-Stewart

[NeoPa]

As a general rule using UNION is likely to slow down data retrieval drastically.

It can be used on a single table, when you want to separate fields from a table into distinct records (EG as below).

SELECT   [Date]
       , [Number]
       , [Name1] AS [Name]
FROM     [Table]
UNION ALL
SELECT   [Date]
       , [Number]
       , [Name2]
FROM     [Table]
UNION ALL
SELECT   [Date]
       , [Number]
       , [Name3]
FROM     [Table]
ORDER BY [Date]
       , [Number]
       , [Name]

Notice - There can only be one ORDER BY clause. This determines the order of the resultant recordset.

Again, without a better understanding of what you're asking for, it's hard to help further.