[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

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

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
Starr Duskk
Asked:
Starr Duskk
1 Solution
 
knightEknightCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
thanks for the info. but you're right. I simplified my query. my summing is a bit more detailed than that.
:)
0
 
SharathData EngineerCommented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
SharathData EngineerCommented:
>> 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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I found a project on the merge command:
 table1.Merge(table2, False, MissingSchemaAction.Add)
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now