Solved

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

Posted on 2014-03-12
10
383 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
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 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

Author Closing Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 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