I have a query that pulls from a group of tables.
And another table that pulls from another group of tables.
Then I take that data and calculate totals based on the statuses of the columns returned.
I have created this quite simple little query to demonstrate. I'm just using this to demonstrate the from/groupby/union situation.
Select UserName, sum(total1) + sum(total2) as Total
SELECT UserName,COUNT(UserName) as total1, 0 as total2
Group by UserName
SELECT UserName,0 as total1, COUNT(UserName) as total2
Group by UserName ) as table1
group by UserName
Order By UserName
The actual queries are quite complex and the entire query with the 3 sections is very memory intensive when it's pulling a lot of data from a table with a million records.
Is there a way to break these out so my first select returns a datatable. My second select returns a datatable. Then somehow combine the two datatables and get the sum?
Sometimes I don't do the sum, I just do the two inside selects to combine all the data into one datatable.
Is that something people do? Do they create separate datatables and then combine them to save on memory intensive situations?
How does one do that if so? If not, is there something else I can do?