?
Solved

two selects, a group by, and a union, walked into a datatable

Posted on 2014-03-12
10
Medium Priority
?
400 Views
Last Modified: 2014-03-13
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
from (
SELECT UserName,COUNT(UserName) as total1, 0 as total2
from TableJane
Group by UserName
  UNION ALL 
SELECT UserName,0 as total1, COUNT(UserName) as total2
from TableJohn
Group by UserName ) as table1
 group by UserName 
 Order By UserName 

Open in new window


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?
0
Comment
Question by:Starr Duskk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 39924983
This may not help in the unaltered query you are using, but you can get the row count of any table like this:


SELECT I.id, SCHEMA_NAME(O.uid) as [schema], O.name, I.rowcnt
  FROM sysindexes I(nolock)
  JOIN sysobjects O(nolock)
    ON O.id = I.id
   AND O.type = 'U'
   AND I.indid < 2
 ORDER BY O.name

and so to sum the row counts from your two tables, do this:

 select sum(I.rowcnt) as totalBoth
  FROM sysindexes I(nolock)
  JOIN sysobjects O(nolock)
    ON O.id = I.id
   AND O.type = 'U'
   AND I.indid < 2
   and O.name in ('TableJohn','TableJane')
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39925070
thanks for the info. but you're right. I simplified my query. my summing is a bit more detailed than that.
:)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39925109
You can re-write your query like this, but this may not improve the performance if you have millions of records.
SELECT UserName,SUM(total) AS Total 
  FROM (SELECT UserName,COUNT(UserName) AS total 
          FROM TableJane 
         GROUP BY UserName 
        UNION ALL 
        SELECT UserName,COUNT(UserName) AS total 
          FROM TableJohn 
         GROUP BY UserName) AS table1 
 GROUP BY UserName 
 ORDER BY UserName 

Open in new window

The other way is store the intermediate results in temp tables like this and consolidate data on all temp tables.
SELECT UserName,COUNT(UserName) AS total 
  INTO #TableJane 
  FROM TableJane 
 GROUP BY UserName; 

SELECT UserName,COUNT(UserName) AS total 
  INTO #TableJohn 
  FROM TableJohn 
 GROUP BY UserName; 

SELECT UserName,SUM(total) AS Total 
  FROM (SELECT * 
          FROM #TableJane 
        UNION ALL 
        SELECT * 
          FROM #TableJohn) AS table1 
 GROUP BY UserName 
 ORDER BY UserName 

Open in new window

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 2

Author Comment

by:Starr Duskk
ID: 39925221
Thanks.

But I think maybe we're getting hung up on the summing and totaling.

Please forget about the summing and totaling and focus on the UNION and data tables

In my example, I am summing the totals that are in the select  clauses inside the FROM ()

I'm not sure what you're doing, but it looks like you're summing the data without having built a query that indicates what data to sum.

Either way, my original question was:
>>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?
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39925236
I did find this here:
http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx

But I don't know how (or if it's possible) to then do a query against a datatable.

http://forums.asp.net/t/1919386.aspx?Passing+datatable+to+sql+procedure

The above link is how to pass a datatable to sql stored procedure. But I want to do it as a sql command in the code.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39925646
>> I'm not sure what you're doing, but it looks like you're summing the data without having built a query that indicates what data to sum.

Based on your original query, I re-wrote the query.

>>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?

You can esaily do that. Can you explain your requirement with some sample data and expected result.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39927906
I found a project on the merge command:
 table1.Merge(table2, False, MissingSchemaAction.Add)
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39927993
I've requested that this question be closed as follows:

Accepted answer: 0 points for BobCSD's comment #a39927906
Assisted answer: 250 points for knightEknight's comment #a39924983
Assisted answer: 250 points for Sharath_123's comment #a39925646

for the following reason:

table1.Merge(table2, False, MissingSchemaAction.Add)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39927956
You should not use UNION ALL at all.  Instead, summarize each table separately and join the results.  [That's because the grouping will cause a sort, and sorting m and n records separately is more efficient than sorting (m + n) records.]

The FULL OUTER JOIN may also require sorting, but that will be far fewer rows since each table has already been pre-summarized.



SELECT
    COALESCE(table1.UserName, table2.UserName) AS UserName, --, table3UserName, ...
    MAX(table1.total1) AS total1,
    MAX(table2.total2) AS total2
    --MAX(table3.total3) AS total3, ...
FROM (
    SELECT UserName, COUNT(UserName) as total1
    from TableJane
    Group by UserName
) AS table1
FULL OUTER JOIN (
    SELECT UserName, COUNT(UserName) as total2
    from TableJohn
    Group by UserName
) as table2 ON
    table2.UserName = table1.UserName
ORDER BY
    UserName
0
 
LVL 2

Author Closing Comment

by:Starr Duskk
ID: 39927995
Thanks! I think that's what I need!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question