[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-03-12
10
Medium Priority
?
407 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 70

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 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