Combine 2 result sets
Is there a way to add the results of two different queries to the result set?
something like that:
ResultSet rs ;
i=0;
while(i<=l)
ResultSet rs1 = select * from tablei;
rs = rs + rs1;
i++;
}
I know I can do it with union, but I have a lot of queries and if I use UNION the query will be too slow.
Any idea?
source to share
I don't believe there is a way to add a ResultSet to another. They don't have a method in the class that does such a thing or update the ResultSet from code. ResultSets are meant to receive data from the database, not from manipulation with developers, user input, etc.
My suggestion was to extract the data into an array or something like that and manipulate the data in code or make a UNION in the query.
source to share
Depending on the data access library being used, ResultSet has a MoveNextRecordSet () method, and SqlDataReader provides NextResult ().
Create a stored procedure to return multiple result sets (i.e. multiple SELECT statements in one sp) and traverse the result sets using these methods.
source to share
I would be surprised if you find a method that has better performance than UNION in the database. Union does what you want and the database server will optimize this as much as possible. You will essentially be reinventing the wheel.
If your UNION is too slow, try investigating if your database can improve indexing. You should also do a time analysis on the individual queries versus the UNION variant. I expect one or the other queries to be slow, not UNION.
source to share
IF this union takes too long, you might want to consider changing the indexes on the tables you are using.
Have you checked for index fragmentation?
See if you are adding the desired indexes if you can speed up the query this way. I don't think it will be used in the same way as all allies for a while.
source to share