combine 2 resultset

Question

is there a way to add the results of 2 different queries to a resultset?

something like that:

ResultSet rs ;

i=0;

while(i<=l)


  ResultSet rs1 = select * from tablei;

  rs = rs + rs1; 

 i++;

}

I know that I can do it with union, but I have a lot queries and if I use UNION the query is too slow.

Any idea?

Answer 1

I don't believe there is any way to add a ResultSet to another. They have no method in the class that does such a thing or updates the ResultSet from the code. ResultSets are designed to receive data from the database and not from developer manipulation, user input or the like.

My suggestion would be to extract the data to an array or something similar and manipulate the data in the code or do the UNION in the query.

Answer 2

Depending on which data access library you use, ResultSet has a method called MoveNextRecordSet(), and SqlDataReader provides NextResult().

Create a stored procedure to return several result sets (i.e. several SELECT statements in one sp), and navigate through the result sets using these methods.

Answer 3

Are you doing a UNION or a UNION ALL? The latter shouldn't be much different from doing it yourself (although I'd expect doing it yourself to be slower).

Answer 4

I'd be surprised if you found a method that has better performance than UNION in the database. Union is doing what you want, and the database server will have optimised this as best as they can. You'd essentially be re-inventing the wheel.

If your UNION is too slow, then try looking into whether your database could do with better indexing. You should also do some timing analysis on the individual queries, compared with the UNION option. I'd expect one or the other queries is the slow bit, rather than the UNION.

Answer 5

IF that Union takes too much time to complete maybe you should consider changing your indexes on the tables that you use.

Did you check your index fragmentation?

See if you add the right indexes if you can speed up the query that way. I do not think that a while being used like that will be quicker than a union all.