Subquery in Join with an Aggregate function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subquery in Join with an Aggregate function

Author

Topic

GaMichael
Starting Member

3 Posts

Posted - 2011-12-08 : 11:53:23

I am new to SQL so this is probably dirt simple.

I am trying to query a table that holds all accounting entries. I am trying to do a two part process. First, I want to identify all Journals in the table where the Journal is unbalanced, i.e., the debits lines added with credit lines do not add to zero.

I want to take that result set and join it back to the original table and select * to get all of the detail lines and all of the fields for each unbalanced journal.

A few metrics:
Total records in the table = 142,560
Number of Unbalanced Journals = 13

I first tried this with a recursive CTE but My anchor set has less fields than the result set that I want, so that did not work. I can not isolate the anchor set using all of the fields I want in the result set.

So I have resorted to joining the Main Query (Select *) to itself by using a subquery in the join. This is resulting in a very slow query.

What is the best approach to tackle this? We all fall back on what we know, and I know very little. I am tempted to create a View to capture the 13 Journals that are out of balance, query the main table and join it to the view to filter out the records I do not want. But Im not sure if that will be any faster than the query I already wrote.

Here is the query, and thanks a lot.

Select *
From GenTransaction a
Inner Join (Select GlYear, GlPeriod, EntryGroup, Journal
From GenTransaction
Group By GlYear, GlPeriod,EntryGroup, Journal
Having Sum(EntryValue) <> 0
)b On
a.GlYear=b.GlYear and a.GlPeriod=b.GlPeriod and a.EntryGroup=b.EntryGroup and a.Journal=b.Journal

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-08 : 12:02:21

Create an index
create index ix_GenTransaction_01 on GenTransaction (GlYear, GlPeriod, EntryGroup, Journal) include (EntryValue)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GaMichael
Starting Member

3 Posts

Posted - 2011-12-08 : 12:10:13

Nigel,
I've never played around with Indexes. How do I join the main query to the index?

The query I wrote finally finished. It took 25 minutes. I am very curious to see how much faster your suggested approach will be

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 12:46:24

quote:
Originally posted by GaMichael

Nigel,
I've never played around with Indexes. How do I join the main query to the index?

The query I wrote finally finished. It took 25 minutes. I am very curious to see how much faster your suggested approach will be

you dont need join query to index
just create index as Nigel suggests first and separately then run query as before
it will perform much faster as it retrieves the details by searching through index

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GaMichael
Starting Member

3 Posts

Posted - 2011-12-08 : 14:36:48

Gotcha. Just tried it. WOW! from 20 minutes to 8 seconds. I am amaazed.

Just out of curiousity, how does SQL "know" to check the index during query execution? There was already an Index on the table, now there are two, so how does SQL know to check the index and which index to check?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 00:26:20

quote:
Originally posted by GaMichael

Gotcha. Just tried it. WOW! from 20 minutes to 8 seconds. I am amaazed.

Just out of curiousity, how does SQL "know" to check the index during query execution? There was already an Index on the table, now there are two, so how does SQL know to check the index and which index to check?

it depends on lots of factors like columns used in where,selectivity of data etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources