Solved

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

Posted on 2014-03-12
10
377 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:BobCSD
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 1

Author Comment

by:BobCSD
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 40

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
 
LVL 1

Author Comment

by:BobCSD
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 1

Author Comment

by:BobCSD
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 40

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 1

Author Comment

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

Author Comment

by:BobCSD
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:
ScottPletcher earned 500 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 1

Author Closing Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now