Slow query help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Slow query help

Author

Topic

thenearfuture
Starting Member

35 Posts

Posted - 2007-10-24 : 14:39:11

This will be a long post – anyone with the patience and kindness to read it will be greatly rewarded with… uhh… virtual beer (and a very real smile).

At my job, we have three tables that track account entries. Account entries can be debits or credits.

tbl_CSEAccountEntry (Account - 1.5 million records)

tbl_CSEActorAccountEntry (Actor - 2 million records)

tbl_CSEDebtorAccountEntry (Debtor - 2 million records)

The Account table keeps track of the total amount that was entered during one particular transaction for a particular case. The Actor and Debtor tables are almost (they have a couple of different functions, but those aren't relevant here) exact copies of each other - for each transaction, they divide the total amount in the Account table into various parts (called Claim Parts) - interest charges, additional principal, various fees, etc.

The two tables have a foreign key to the Account table, and the sum of all of the entries in the Actor table should be the same as the sum of the entries in the Debtor table for each transaction. And, of course, each of those sums should be equal to the total amount recorded in the Account table.

Sometimes, however, the entries in the Actor and Debtor tables aren't identical.

This query is supposed to find all the cases for which the sums of the entries in the Actor and Debtor tables are not the same. The query takes 28 seconds, which seems to me to be a very long time, even with the fact that the tables hold up to 2 million records.

Does the number of records really slow down the performance that much?

Does anybody have any ideas as to what can be done to improve the performance of the query? Indexes? Another query altogether? These tables are only going to get larger and the performance is only going to get slower, so I would appreciate any suggestions that would help kickstart this thing.

Query:


SELECT    *

FROM   

	(SELECT     	tbl_CSEAccountEntry.big_CSECase_FK, 
			SUM(tbl_CSEActorAccountEntry.mny_Amount) AS SumAct

	FROM         	tbl_CSEActorAccountEntry INNER JOIN
			tbl_CSEAccountEntry 
			ON tbl_CSEActorAccountEntry.int_CSEAccountEntry_FK = 
tbl_CSEAccountEntry.int_CSEAccountEntryId

	GROUP BY 	tbl_CSEAccountEntry.big_CSECase_FK) as Act 

INNER JOIN

	(SELECT     	tbl_CSEAccountEntry.big_CSECase_FK,
			SUM(tbl_CSEDebtorAccountEntry.mny_Amount) AS SumDebt

	FROM          	tbl_CSEAccountEntry INNER JOIN
			tbl_CSEDebtorAccountEntry 
			ON tbl_CSEAccountEntry.int_CSEAccountEntryId = 
tbl_CSEDebtorAccountEntry.int_CSEAccountEntry_FK

	GROUP BY 	tbl_CSEAccountEntry.big_CSECase_FK) As Debt

 
ON Act.big_csecase_fk = debt.big_csecase_fk 
AND SumAct <> SumDebt

DDL:


/***tbl_CSEAccountEntry***/

CREATE TABLE [dbo].[tbl_CSEAccountEntry] (
	[int_CSEAccountEntryId] [int] IDENTITY (1, 1) NOT NULL ,
	[dtm_Created] [datetime] NOT NULL ,
	[int_Creator_FK] [int] NOT NULL ,
	[big_CSECase_FK] [bigint] NOT NULL ,
	[chr_CSEAccountEntryType_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
	[vch_Text] [varchar] (100) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
	[sdt_EntryDate] [smalldatetime] NOT NULL ,
	[sdt_ValueDate] [smalldatetime] NOT NULL ,
	[sdt_InterestDate] [smalldatetime] NULL ,
	[mny_Amount] [money] NOT NULL ,
	[int_CSEInvoice_FK] [int] NULL ,
	[int_LEDTrack_FK] [int] NULL ,
	[int_SourceReference] [int] NULL 
) 
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEAccountEntry] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_CSEAccountEntry] PRIMARY KEY  CLUSTERED 
	(
		[int_CSEAccountEntryId]
	)  
ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_CSEAccountEntry] ADD 
	CONSTRAINT [DF_tbl_CSEAccountEntry_dtm_Created] DEFAULT (getdate()) FOR [dtm_Created]
GO

 CREATE  INDEX [IX_tbl_CSEAccountEntry] ON [dbo].[tbl_CSEAccountEntry]([big_CSECase_FK]) 
ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbl_CSEAccountEntry_1] ON [dbo].[tbl_CSEAccountEntry]([chr_CSEAccountEntryType_FK]) 
ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbl_CSEAccountEntry_2] ON [dbo].[tbl_CSEAccountEntry]([int_LEDTrack_FK]) 
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEAccountEntry] ADD 
	CONSTRAINT [FK_tbl_CSEAccountEntry_tbl_CSEAccountEntryTypeNew] FOREIGN KEY 
	(
		[chr_CSEAccountEntryType_FK]
	) REFERENCES [dbo].[tbl_CSEAccountEntryType] (
		[chr_CSEAccountEntryTypeId]
	),
	CONSTRAINT [FK_tbl_CSEAccountEntry_tbl_CSECase] FOREIGN KEY 
	(
		[big_CSECase_FK]
	) REFERENCES [dbo].[tbl_CSECase] (
		[big_CSECaseId]
	),
	CONSTRAINT [FK_tbl_CSEAccountEntry_tbl_CSEInvoice] FOREIGN KEY 
	(
		[int_CSEInvoice_FK]
	) REFERENCES [dbo].[tbl_CSEInvoice] (
		[int_CSEInvoiceId]
	),
	CONSTRAINT [FK_tbl_CSEAccountEntry_tbl_LEDTrack] FOREIGN KEY 
	(
		[int_LEDTrack_FK]
	) REFERENCES [dbo].[tbl_LEDTrack] (
		[int_LEDTrackId]
	),
	CONSTRAINT [FK_tbl_CSEAccountEntry_tbl_USRUser] FOREIGN KEY 
	(
		[int_Creator_FK]
	) REFERENCES [dbo].[tbl_USRUser] (
		[int_USRUserId]
	)
GO

/***tbl_CSEActorAccountEntry***/

CREATE TABLE [dbo].[tbl_CSEActorAccountEntry] (
	[int_CSEActorAccountEntryId] [int] IDENTITY (1, 1) NOT NULL ,
	[int_CSEAccountEntry_FK] [int] NOT NULL ,
	[chr_CSEClaimPart_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
	[chr_CSEActorType_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NULL ,
	[mny_Amount] [money] NOT NULL ,
	[int_CSESettlement_FK] [int] NULL 
) 
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEActorAccountEntry] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_CSEActorAccountEntry] PRIMARY KEY  CLUSTERED 
	(
		[int_CSEActorAccountEntryId]
	)  
ON [PRIMARY] 
GO

 CREATE  INDEX [IX_tbl_CSEActorAccountEntry] ON [dbo].[tbl_CSEActorAccountEntry]([chr_CSEClaimPart_FK]) 
ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbl_CSEActorAccountEntry_1] ON [dbo].[tbl_CSEActorAccountEntry]([int_CSEAccountEntry_FK]) 
ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbl_CSEActorAccountEntry_2] ON [dbo].[tbl_CSEActorAccountEntry]([int_CSESettlement_FK]) 
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEActorAccountEntry] ADD 
	CONSTRAINT [FK_tbl_CSEActorAccountEntry_tbl_CSEAccountEntry] FOREIGN KEY 
	(
		[int_CSEAccountEntry_FK]
	) REFERENCES [dbo].[tbl_CSEAccountEntry] (
		[int_CSEAccountEntryId]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_tbl_CSEActorAccountEntry_tbl_CSEActorType] FOREIGN KEY 
	(
		[chr_CSEActorType_FK]
	) REFERENCES [dbo].[tbl_CSEActorType] (
		[chr_CSEActorTypeId]
	),
	CONSTRAINT [FK_tbl_CSEActorAccountEntry_tbl_CSEClaimPart] FOREIGN KEY 
	(
		[chr_CSEClaimPart_FK]
	) REFERENCES [dbo].[tbl_CSEClaimPart] (
		[chr_CSEClaimPartId]
	),
	CONSTRAINT [FK_tbl_CSEActorAccountEntry_tbl_CSESettlement] FOREIGN KEY 
	(
		[int_CSESettlement_FK]
	) REFERENCES [dbo].[tbl_CSESettlement] (
		[int_CSESettlementId]
	)
GO


/***tbl_CSEDebtorAccountEntry***/

CREATE TABLE [dbo].[tbl_CSEDebtorAccountEntry] (
	[int_CSEDebtorAccountEntryId] [int] IDENTITY (1, 1) NOT NULL ,
	[int_CSEAccountEntry_FK] [int] NOT NULL ,
	[chr_CSEClaimPart_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
	[mny_Amount] [money] NOT NULL ,
	[int_OpposingEntry_FK] [int] NULL ,
	[int_ACTFeeType_FK] [int] NULL 
) 
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEDebtorAccountEntry] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_CSEDebtorAccountEntryNew] PRIMARY KEY  CLUSTERED 
	(
		[int_CSEDebtorAccountEntryId]
	)  
ON [PRIMARY] 
GO

 CREATE  INDEX [IX_tbl_CSEDebtorAccountEntryNew] ON [dbo].[tbl_CSEDebtorAccountEntry]([chr_CSEClaimPart_FK]) 
ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbl_CSEDebtorAccountEntryNew_1] ON [dbo].[tbl_CSEDebtorAccountEntry]([int_CSEAccountEntry_FK]) 
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEDebtorAccountEntry] ADD 
	CONSTRAINT [FK_tbl_CSEDebtorAccountEntry_tbl_ACTFeeType] FOREIGN KEY 
	(
		[int_ACTFeeType_FK]
	) REFERENCES [dbo].[tbl_ACTFeeType] (
		[int_ACTFeeTypeId]
	),
	CONSTRAINT [FK_tbl_CSEDebtorAccountEntry_tbl_CSEDebtorAccountEntry] FOREIGN KEY 
	(
		[int_OpposingEntry_FK]
	) REFERENCES [dbo].[tbl_CSEDebtorAccountEntry] (
		[int_CSEDebtorAccountEntryId]
	),
	CONSTRAINT [FK_tbl_CSEDebtorAccountEntryNew_tbl_CSEAccountEntry] FOREIGN KEY 
	(
		[int_CSEAccountEntry_FK]
	) REFERENCES [dbo].[tbl_CSEAccountEntry] (
		[int_CSEAccountEntryId]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_tbl_CSEDebtorAccountEntryNew_tbl_CSEClaimPart] FOREIGN KEY 
	(
		[chr_CSEClaimPart_FK]
	) REFERENCES [dbo].[tbl_CSEClaimPart] (
		[chr_CSEClaimPartId]
	)
GO

Sample Data:


INSERT INTO [tbl_CSEAccountEntry] (
	[int_CSEAccountEntryId],
	[dtm_Created],
	[int_Creator_FK],
	[big_CSECase_FK],
	[chr_CSEAccountEntryType_FK],
	[vch_Text],
	[sdt_EntryDate],
	[sdt_ValueDate],
	[sdt_InterestDate],
	[mny_Amount],
	[int_CSEInvoice_FK],
	[int_LEDTrack_FK],
	[int_SourceReference]) 

SELECT 11, '2005-10-24', 1, 12345678, 'CHARGE', 'Bill 1 regarding something', '2004-06-24', 
'2004-06-24', '2004-07-24', 1500, 1134, NULL, NULL UNION ALL
SELECT 12, '2005-10-24', 1, 12345678, 'CHARGE', 'Bill 2 regarding something', '2004-09-12', 
'2004-09-12', '2004-10-12', 1350, 1897, NULL, NULL UNION ALL
SELECT 13, '2005-10-27', 1, 99339933, 'CHARGE', 'Bill 1 regarding anything!', '2004-09-15', 
'2004-09-15', '2004-10-15', 1000, 2162, NULL, NULL 


INSERT INTO [tbl_CSEActorAccountEntry] (
	[int_CSEActorAccountEntryId],
	[int_CSEAccountEntry_FK],
	[chr_CSEClaimPart_FK],
	[chr_CSEActorType_FK],
	[mny_Amount],
	[int_CSESettlement_FK])

SELECT 345, 11, 'PRINCIPAL', 'CLIENT', 1300, NULL UNION ALL
SELECT 346, 11, 'INTEREST', 'AGENT', 100, NULL UNION ALL
SELECT 347, 11, 'FEE', 'AGENT', 100, NULL UNION ALL
SELECT 348, 12, 'PRINCIPAL', 'CLIENT', 1250, NULL UNION ALL
SELECT 349, 12, 'INTEREST', 'AGENT', 100, NULL UNION ALL
SELECT 350, 13, 'PRINCIPAL', 'CLIENT', 2160, NULL
 

INSERT INTO [tbl_CSEDebtorAccountEntry] (
	[int_CSEDebtorAccountEntryId],
	[int_CSEAccountEntry_FK],
	[chr_CSEClaimPart_FK],
	[mny_Amount],
	[int_OpposingEntry_FK],
	[int_ACTFeeType_FK]) 

SELECT 239, 11, 'PRINCIPAL', 1300, NULL, NULL UNION ALL
SELECT 240, 11, 'INTEREST', 100, NULL, NULL UNION ALL
SELECT 241, 11, 'FEE',  100, NULL, NULL UNION ALL
SELECT 242, 12, 'PRINCIPAL', 1250, NULL, NULL UNION ALL
SELECT 243, 12, 'INTEREST', 100, NULL, NULL UNION ALL
SELECT 244, 13, 'PRINCIPAL', 2160, NULL, NULL UNION ALL
SELECT 245, 13, 'FEE', 2, NULL, NULL

Execution Plan:

I can't (quickly) figure out how to keep the execution plan from breaking the tables, so I've uploaded it to supload:

http://download.yousendit.com/7DD3E9A161588649

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-24 : 17:21:38

Using select * is a bad idea, and the use of Sub queries as your source will slow things down a bit
Make your subqueries into two really quick temp tables, then query the results as below. Untested


SELECT     	tbl_CSEAccountEntry.big_CSECase_FK, 
			SUM(tbl_CSEActorAccountEntry.mny_Amount) AS SumAct
        INTO #Act
	FROM         	tbl_CSEActorAccountEntry INNER JOIN
			tbl_CSEAccountEntry 
			ON tbl_CSEActorAccountEntry.int_CSEAccountEntry_FK = tbl_CSEAccountEntry.int_CSEAccountEntryId

	GROUP BY 	tbl_CSEAccountEntry.big_CSECase_FK) as Act 


SELECT     	tbl_CSEAccountEntry.big_CSECase_FK,
			SUM(tbl_CSEDebtorAccountEntry.mny_Amount) AS SumDebt
       INTO #Debt
	FROM          	tbl_CSEAccountEntry INNER JOIN
			tbl_CSEDebtorAccountEntry 
			ON tbl_CSEAccountEntry.int_CSEAccountEntryId = 
tbl_CSEDebtorAccountEntry.int_CSEAccountEntry_FK

	GROUP BY 	tbl_CSEAccountEntry.big_CSECase_FK) As Debt


Select  big_cseCase_FK,SumAct,sumDebt
From #Act,#debt
Where #Act.big_csecase_fk = #debt.big_csecase_fk 
AND SumAct <> SumDebt

Reporter
Starting Member

48 Posts

Posted - 2007-10-25 : 06:45:35

Unfortunately the script is not suitable for test performing at once.

Here is its successful transformation.

CREATE TABLE [dbo].[tbl_CSEAccountEntry] (
[int_CSEAccountEntryId] [int] NOT NULL ,
[dtm_Created] [datetime] NOT NULL ,
[int_Creator_FK] [int] NOT NULL ,
[big_CSECase_FK] [bigint] NOT NULL ,
[chr_CSEAccountEntryType_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[vch_Text] [varchar] (100) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[sdt_EntryDate] [smalldatetime] NOT NULL ,
[sdt_ValueDate] [smalldatetime] NOT NULL ,
[sdt_InterestDate] [smalldatetime] NULL ,
[mny_Amount] [money] NOT NULL ,
[int_CSEInvoice_FK] [int] NULL ,
[int_LEDTrack_FK] [int] NULL ,
[int_SourceReference] [int] NULL
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEAccountEntry] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_CSEAccountEntry] PRIMARY KEY CLUSTERED
(
[int_CSEAccountEntryId]
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEAccountEntry] ADD
CONSTRAINT [DF_tbl_CSEAccountEntry_dtm_Created] DEFAULT (getdate()) FOR [dtm_Created]
GO

CREATE INDEX [IX_tbl_CSEAccountEntry] ON [dbo].[tbl_CSEAccountEntry]([big_CSECase_FK])
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEAccountEntry_1] ON [dbo].[tbl_CSEAccountEntry]([chr_CSEAccountEntryType_FK])
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEAccountEntry_2] ON [dbo].[tbl_CSEAccountEntry]([int_LEDTrack_FK])
ON [PRIMARY]
GO

/***tbl_CSEActorAccountEntry***/

CREATE TABLE [dbo].[tbl_CSEActorAccountEntry] (
[int_CSEActorAccountEntryId] [int] NOT NULL ,
[int_CSEAccountEntry_FK] [int] NOT NULL ,
[chr_CSEClaimPart_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[chr_CSEActorType_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NULL ,
[mny_Amount] [money] NOT NULL ,
[int_CSESettlement_FK] [int] NULL
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEActorAccountEntry] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_CSEActorAccountEntry] PRIMARY KEY CLUSTERED
(
[int_CSEActorAccountEntryId]
)
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEActorAccountEntry] ON [dbo].[tbl_CSEActorAccountEntry]([chr_CSEClaimPart_FK])
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEActorAccountEntry_1] ON [dbo].[tbl_CSEActorAccountEntry]([int_CSEAccountEntry_FK])
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEActorAccountEntry_2] ON [dbo].[tbl_CSEActorAccountEntry]([int_CSESettlement_FK])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEActorAccountEntry] ADD
CONSTRAINT [FK_tbl_CSEActorAccountEntry_tbl_CSEAccountEntry] FOREIGN KEY
(
[int_CSEAccountEntry_FK]
) REFERENCES [dbo].[tbl_CSEAccountEntry] (
[int_CSEAccountEntryId]
) ON DELETE CASCADE
GO

/***tbl_CSEDebtorAccountEntry***/

CREATE TABLE [dbo].[tbl_CSEDebtorAccountEntry] (
[int_CSEDebtorAccountEntryId] [int] NOT NULL ,
[int_CSEAccountEntry_FK] [int] NOT NULL ,
[chr_CSEClaimPart_FK] [char] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[mny_Amount] [money] NOT NULL ,
[int_OpposingEntry_FK] [int] NULL ,
[int_ACTFeeType_FK] [int] NULL
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEDebtorAccountEntry] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_CSEDebtorAccountEntryNew] PRIMARY KEY CLUSTERED
(
[int_CSEDebtorAccountEntryId]
)
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEDebtorAccountEntryNew] ON [dbo].[tbl_CSEDebtorAccountEntry]([chr_CSEClaimPart_FK])
ON [PRIMARY]
GO

CREATE INDEX [IX_tbl_CSEDebtorAccountEntryNew_1] ON [dbo].[tbl_CSEDebtorAccountEntry]([int_CSEAccountEntry_FK])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_CSEDebtorAccountEntry] ADD
CONSTRAINT [FK_tbl_CSEDebtorAccountEntry_tbl_CSEDebtorAccountEntry] FOREIGN KEY
(
[int_OpposingEntry_FK]
) REFERENCES [dbo].[tbl_CSEDebtorAccountEntry] (
[int_CSEDebtorAccountEntryId]
),
CONSTRAINT [FK_tbl_CSEDebtorAccountEntryNew_tbl_CSEAccountEntry] FOREIGN KEY
(
[int_CSEAccountEntry_FK]
) REFERENCES [dbo].[tbl_CSEAccountEntry] (
[int_CSEAccountEntryId]
) ON DELETE CASCADE
GO

INSERT INTO [tbl_CSEAccountEntry] (
[int_CSEAccountEntryId],
[dtm_Created],
[int_Creator_FK],
[big_CSECase_FK],
[chr_CSEAccountEntryType_FK],
[vch_Text],
[sdt_EntryDate],
[sdt_ValueDate],
[sdt_InterestDate],
[mny_Amount],
[int_CSEInvoice_FK],
[int_LEDTrack_FK],
[int_SourceReference])

SELECT 11, '2005-10-24', 1, 12345678, 'CHARGE', 'Bill 1 regarding something', '2004-06-24',
'2004-06-24', '2004-07-24', 1500, 1134, NULL, NULL UNION ALL
SELECT 12, '2005-10-24', 1, 12345678, 'CHARGE', 'Bill 2 regarding something', '2004-09-12',
'2004-09-12', '2004-10-12', 1350, 1897, NULL, NULL UNION ALL
SELECT 13, '2005-10-27', 1, 99339933, 'CHARGE', 'Bill 1 regarding anything!', '2004-09-15',
'2004-09-15', '2004-10-15', 1000, 2162, NULL, NULL

INSERT INTO [tbl_CSEActorAccountEntry] (
[int_CSEActorAccountEntryId],
[int_CSEAccountEntry_FK],
[chr_CSEClaimPart_FK],
[chr_CSEActorType_FK],
[mny_Amount],
[int_CSESettlement_FK])

SELECT 345, 11, 'PRINCIPAL', 'CLIENT', 1300, NULL UNION ALL
SELECT 346, 11, 'INTEREST', 'AGENT', 100, NULL UNION ALL
SELECT 347, 11, 'FEE', 'AGENT', 100, NULL UNION ALL
SELECT 348, 12, 'PRINCIPAL', 'CLIENT', 1250, NULL UNION ALL
SELECT 349, 12, 'INTEREST', 'AGENT', 100, NULL UNION ALL
SELECT 350, 13, 'PRINCIPAL', 'CLIENT', 2160, NULL

INSERT INTO [tbl_CSEDebtorAccountEntry] (
[int_CSEDebtorAccountEntryId],
[int_CSEAccountEntry_FK],
[chr_CSEClaimPart_FK],
[mny_Amount],
[int_OpposingEntry_FK],
[int_ACTFeeType_FK])

SELECT 239, 11, 'PRINCIPAL', 1300, NULL, NULL UNION ALL
SELECT 240, 11, 'INTEREST', 100, NULL, NULL UNION ALL
SELECT 241, 11, 'FEE', 100, NULL, NULL UNION ALL
SELECT 242, 12, 'PRINCIPAL', 1250, NULL, NULL UNION ALL
SELECT 243, 12, 'INTEREST', 100, NULL, NULL UNION ALL
SELECT 244, 13, 'PRINCIPAL', 2160, NULL, NULL UNION ALL
SELECT 245, 13, 'FEE', 2, NULL, NULL

There is not enough data for effective testing.

Nevertheless, try test this:

select SUM(b.mny_Amount) AS SumAct,SUM(c.mny_Amount) AS SumDebt from
tbl_CSEAccountEntry a
inner join tbl_CSEActorAccountEntry b on b.int_CSEAccountEntry_FK = a.int_CSEAccountEntryId
inner join tbl_CSEDebtorAccountEntry c on c.int_CSEAccountEntry_FK = a.int_CSEAccountEntryId
group by a.big_CSECase_FK

Reporter
Starting Member

48 Posts

Posted - 2007-10-25 : 07:05:53

More exacly

select SumAct,SumDebt from
(select SUM(b.mny_Amount) AS SumAct,SUM(c.mny_Amount) AS SumDebt from
tbl_CSEAccountEntry a
inner join tbl_CSEActorAccountEntry b on b.int_CSEAccountEntry_FK = a.int_CSEAccountEntryId
inner join tbl_CSEDebtorAccountEntry c on c.int_CSEAccountEntry_FK = a.int_CSEAccountEntryId
group by a.big_CSECase_FK) as a where SumAct<>SumDebt

This select executes

1 Index Scan
2 Index Seek

instead

2 Index Scan and
2 Index Seek

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 07:07:06

quote:
Originally posted by Reporter

select SUM(b.mny_Amount) AS SumAct,SUM(c.mny_Amount) AS SumDebt from
tbl_CSEAccountEntry a
inner join tbl_CSEActorAccountEntry b on b.int_CSEAccountEntry_FK = a.int_CSEAccountEntryId
inner join tbl_CSEDebtorAccountEntry c on c.int_CSEAccountEntry_FK = a.int_CSEAccountEntryId
group by a.big_CSECase_FK

I think this will produce a massive CROSS JOIN sums, since they share same Fk value.

Try this

SELECT		ae.big_CSECase_FK,
		SUM(y.Act) AS SumAct,
		SUM(y.Debt) AS SumDebt
FROM		tbl_CSEAccountEntry AS ae
INNER JOIN	(
			SELECT		int_CSEAccountEntry_FK AS Fk,
					mny_Amount AS Act,
					0 AS Debt
			FROM		tbl_CSEActorAccountEntry

			UNION ALL

			SELECT		int_CSEAccountEntry_FK AS Fk,
					0
					mny_Amount AS Debt
			FROM		tbl_CSEDebtorAccountEntry
		) AS y ON y.Fk = ae.int_CSEAccountEntryId
GROUP BY	ae.big_CSECase_FK
HAVING		SUM(y.Act) <> SUM(y.Debt)

or this

SELECT		ae.big_CSECase_FK,
		SUM(y.Act) AS SumAct,
		SUM(y.Debt) AS SumDebt
FROM		tbl_CSEAccountEntry AS ae
INNER JOIN	(
			SELECT		int_CSEAccountEntry_FK AS Fk,
					SUM(mny_Amount) AS Act,
					0 AS Debt
			FROM		tbl_CSEActorAccountEntry
			GROUP BY	int_CSEAccountEntry_FK

			UNION ALL

			SELECT		int_CSEAccountEntry_FK AS Fk,
					0
					SUM(mny_Amount) AS Debt
			FROM		tbl_CSEDebtorAccountEntry
			GROUP BY	int_CSEAccountEntry_FK
		) AS y ON y.Fk = ae.int_CSEAccountEntryId
GROUP BY	ae.big_CSECase_FK
HAVING		SUM(y.Act) <> SUM(y.Debt)

Make sure table CSEDebtorAccountEntry has a covering index over columns {int_CSEAccountEntry_FK, mny_Amount}, and table tbl_CSEActorAccountEntry has a covering index over columns {int_CSEAccountEntry_FK, mny_Amount}.

Can you please email me in another matter? I have forgotten your email address...

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-10-25 : 07:15:58

cross join?

only 2 Nested Loops/INNER JOIN

Reporter
Starting Member

48 Posts

Posted - 2007-10-25 : 07:22:34

Also there are next advantages

1 Aggregate
0 Hash Match/Inner Join

instead

2 Aggregate and
1 Hash Match/Inner Join

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 07:39:47

quote:
Originally posted by Reporter

cross join?

only 2 Nested Loops/INNER JOIN

Written INNER JOINs that behave like CROSS JOIN due to multiple Fks. If you do not trust me, look at this example

DECLARE	@a TABLE (pk INT)
DECLARE	@b TABLE (fk INT, i INT)
DECLARE	@c TABLE (fk INT, j INT)

INSERT	@a
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3

INSERT	@b
SELECT	1, 1 UNION ALL
SELECT	1, 3 UNION ALL
SELECT	2, 4 UNION ALL
SELECT	2, 8 UNION ALL
SELECT	2, 10 UNION ALL
SELECT	3, 1

INSERT	@c
SELECT	1, 11 UNION ALL
SELECT	1, 13 UNION ALL
SELECT	2, 14 UNION ALL
SELECT	2, 18 UNION ALL
SELECT	2, 60 UNION ALL
SELECT	3, 11

select		a.pk,
		SUM(b.i) AS SumAct,
		SUM(c.j) AS SumDebt
from		@a as a
inner join	@b as b on b.fk = a.pk
inner join	@c as c on c.fk = a.pk
group by	a.pk

I think sum 276 is to high! Should only be 92.
The reason for this is that there also is 3 records in @b table that share same fk. 92 times 3 is 276!!!

Learn your JOINs.

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-10-25 : 07:40:31

quote:
Originally posted by Peso

[quote]Originally posted by Reporter

Can you please email me in another matter? I have forgotten your email address...

E 12?55'05.25"
N 56?04'39.16"

I am not sure do you ask me or author of this topic :)

But my is www.com@mail.ru

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 07:48:46

Here is the execution plan with second suggestion (inner and outer group by's)


  |--Filter(WHERE:([Expr1015]<>[Expr1016]))
       |--Compute Scalar(DEFINE:([Expr1015]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END, [Expr1016]=CASE WHEN [Expr1021]=(0) THEN NULL ELSE [Expr1022] END))
            |--Stream Aggregate(GROUP BY:([ae].[big_CSECase_FK]) DEFINE:([Expr1019]=COUNT_BIG([Union1013]), [Expr1020]=SUM([Union1013]), [Expr1021]=COUNT_BIG([Union1014]), [Expr1022]=SUM([Union1014])))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([ae].[int_CSEAccountEntryId]))
                      |--Sort(ORDER BY:([ae].[big_CSECase_FK] ASC))
                      |    |--Clustered Index Scan(OBJECT:([Test].[dbo].[tbl_CSEAccountEntry].[PK_tbl_CSEAccountEntry] AS [ae]))
                      |--Concatenation
                           |--Compute Scalar(DEFINE:([Expr1006]=($0.0000)))
                           |    |--Stream Aggregate(DEFINE:([Expr1005]=SUM([Test].[dbo].[tbl_CSEActorAccountEntry].[mny_Amount])))
                           |         |--Index Seek(OBJECT:([Test].[dbo].[tbl_CSEActorAccountEntry].[ix_peso]), SEEK:([Test].[dbo].[tbl_CSEActorAccountEntry].[int_CSEAccountEntry_FK]=[Test].[dbo].[tbl_CSEAccountEntry].[int_CSEAccountEntryId] as [ae].[int_CSEAccountEntryId]) ORDERED FORWARD)
                           |--Compute Scalar(DEFINE:([Expr1011]=($0.0000)))
                                |--Stream Aggregate(DEFINE:([Expr1010]=SUM([Test].[dbo].[tbl_CSEDebtorAccountEntry].[mny_Amount])))
                                     |--Index Seek(OBJECT:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[ix_peso]), SEEK:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[int_CSEAccountEntry_FK]=[Test].[dbo].[tbl_CSEAccountEntry].[int_CSEAccountEntryId] as [ae].[int_CSEAccountEntryId]) ORDERED FORWARD)

And your original query plan

  |--Hash Match(Inner Join, HASH:([Test].[dbo].[tbl_CSEAccountEntry].[big_CSECase_FK])=([Test].[dbo].[tbl_CSEAccountEntry].[big_CSECase_FK]), RESIDUAL:([Test].[dbo].[tbl_CSEAccountEntry].[big_CSECase_FK]=[Test].[dbo].[tbl_CSEAccountEntry].[big_CSECase_FK] AND [Expr1006]<>[Expr1013]))
       |--Stream Aggregate(GROUP BY:([Test].[dbo].[tbl_CSEAccountEntry].[big_CSECase_FK]) DEFINE:([Expr1006]=SUM([Test].[dbo].[tbl_CSEActorAccountEntry].[mny_Amount])))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[tbl_CSEActorAccountEntry].[int_CSEActorAccountEntryId]))
       |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[tbl_CSEAccountEntry].[int_CSEAccountEntryId]))
       |         |    |--Index Scan(OBJECT:([Test].[dbo].[tbl_CSEAccountEntry].[IX_tbl_CSEAccountEntry]), ORDERED FORWARD)
       |         |    |--Index Seek(OBJECT:([Test].[dbo].[tbl_CSEActorAccountEntry].[IX_tbl_CSEActorAccountEntry_1]), SEEK:([Test].[dbo].[tbl_CSEActorAccountEntry].[int_CSEAccountEntry_FK]=[Test].[dbo].[tbl_CSEAccountEntry].[int_CSEAccountEntryId]) ORDERED FORWARD)
       |         |--Clustered Index Seek(OBJECT:([Test].[dbo].[tbl_CSEActorAccountEntry].[PK_tbl_CSEActorAccountEntry]), SEEK:([Test].[dbo].[tbl_CSEActorAccountEntry].[int_CSEActorAccountEntryId]=[Test].[dbo].[tbl_CSEActorAccountEntry].[int_CSEActorAccountEntryId]) LOOKUP ORDERED FORWARD)
       |--Stream Aggregate(GROUP BY:([Test].[dbo].[tbl_CSEAccountEntry].[big_CSECase_FK]) DEFINE:([Expr1013]=SUM([Test].[dbo].[tbl_CSEDebtorAccountEntry].[mny_Amount])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[int_CSEDebtorAccountEntryId]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[tbl_CSEAccountEntry].[int_CSEAccountEntryId]))
                 |    |--Index Scan(OBJECT:([Test].[dbo].[tbl_CSEAccountEntry].[IX_tbl_CSEAccountEntry]), ORDERED FORWARD)
                 |    |--Index Seek(OBJECT:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[IX_tbl_CSEDebtorAccountEntryNew_1]), SEEK:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[int_CSEAccountEntry_FK]=[Test].[dbo].[tbl_CSEAccountEntry].[int_CSEAccountEntryId]) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[PK_tbl_CSEDebtorAccountEntryNew]), SEEK:([Test].[dbo].[tbl_CSEDebtorAccountEntry].[int_CSEDebtorAccountEntryId]=[Test].[dbo].[tbl_CSEDebtorAccountEntry].[int_CSEDebtorAccountEntryId]) LOOKUP ORDERED FORWARD)

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-10-25 : 08:23:36

quote:
Originally posted by Peso

I am sorry.
That's right

select a.pk,
SUM(b.i) AS SumAct,
SUM(b.j) AS SumDebt
from @a as a inner join
(select fk=isnull(b.fk,c.fk),i=isnull(i,0),j=isnull(j,0) from @b as b full join @c as c on 1<>1) as b on b.fk=a.pk
group by a.pk

E 12?55'05.25"
N 56?04'39.16"

thenearfuture
Starting Member

35 Posts

Posted - 2007-10-25 : 08:55:35

Thank you all for responding.

Reporter: Thanks for cleaning up the DDL and removing the identity info. It completely slipped my mind.

I've run all the scripts suggested in this thread and, surprisingly, every one is slower than the original.

This probably has to do with the fact that sample size is very small compared to the 2 million records in the real tables (?).

I also tried Peso's suggestion and added covered indexes on Actor and Debtor.


CREATE NONCLUSTERED INDEX [ix_peso] ON [dbo].[tbl_CSEActorAccountEntry]
(
[int_CSEAccountEntry_FK] ASC,
[mny_Amount] ASC
)

CREATE NONCLUSTERED INDEX [ix_peso] ON [dbo].[tbl_CSEDebtorAccountEntry]
(
[int_CSEAccountEntry_FK] ASC,
[mny_Amount] ASC
)

Here are the results (00:25 is 25 seconds):


|-------------------------------|---------------|---------------|
|				|   No Indexes	|     Indexes	|
|-------------------------------|---------------|---------------|
|	Original Query		|     00:25	|     00:21	|
|-------------------------------|---------------|---------------|
|	Peso - Query 1		|     00:52	|     00:39	|
|-------------------------------|---------------|---------------|
|	Peso - Query 2		|     00:43	|     00:31	|
|-------------------------------|---------------|---------------|

Reporter
Starting Member

48 Posts

Posted - 2007-10-26 : 01:44:52

But, what about this?

select big_CSECase_FK,SumAct,SumDebt from
(select a.big_CSECase_FK,SUM(b.i) AS SumAct,SUM(b.j) AS SumDebt from
tbl_CSEAccountEntry a inner join
(select fk=isnull(b.int_CSEAccountEntry_FK,c.int_CSEAccountEntry_FK),i=isnull(b.mny_Amount,0),j=isnull(c.mny_Amount,0)
from tbl_CSEActorAccountEntry as b full join tbl_CSEDebtorAccountEntry as c on 1<>1) as b on b.fk=a.int_CSEAccountEntryId
group by a.big_CSECase_FK) as a
where SumAct<>SumDebt

If it is not very hard for you tell me, what is the result of this query?

PS. Thank Peso very much for point to my mistake and for simple method of test.

Good luck.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 06:18:25

thenearfuture, have you tried index hinting in your queries?

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-10-26 : 06:45:15

Some of statistics

thenearfuture ---------------------------------------------------------

Table 'tbl_CSEDebtorAccountEntry'. Scan count 3, logical reads 17
Table 'tbl_CSEAccountEntry'. Scan count 2, logical reads 2
Table 'tbl_CSEActorAccountEntry'. Scan count 3, logical reads 15

scan count 8, logical reads 34

Peso 1 ----------------------------------------------------------------

Table 'tbl_CSEDebtorAccountEntry'. Scan count 3, logical reads 17
Table 'tbl_CSEActorAccountEntry'. Scan count 3, logical reads 15
Table 'tbl_CSEAccountEntry'. Scan count 1, logical reads 1

scan count 7, logical reads 33

Peso 2 ----------------------------------------------------------------

Table 'tbl_CSEDebtorAccountEntry'. Scan count 5, logical reads 27
Table 'tbl_CSEActorAccountEntry'. Scan count 3, logical reads 15
Table 'tbl_CSEAccountEntry'. Scan count 1, logical reads 1

scan count 9, logical reads 43

Reporter 1 ------------------------------------------------------------

Table 'tbl_CSEAccountEntry'. Scan count 13,logical reads 26
Table 'tbl_CSEDebtorAccountEntry'. Scan count 1, logical reads 2
Table 'tbl_CSEActorAccountEntry'. Scan count 1, logical reads 2

scan count 15, logical reads 30

Reporter 2 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

select a.big_CSECase_FK,b.SumAct,b.SumDebt from
(select fk=isnull(b.int_CSEAccountEntry_FK,c.int_CSEAccountEntry_FK),SumAct=sum(isnull(b.mny_Amount,0)),SumDebt=sum(isnull(c.mny_Amount,0))
from tbl_CSEActorAccountEntry as b full join tbl_CSEDebtorAccountEntry as c on 1<>1 group by isnull(b.int_CSEAccountEntry_FK,c.int_CSEAccountEntry_FK)
having sum(isnull(b.mny_Amount,0))<>sum(isnull(c.mny_Amount,0))) as b inner join
tbl_CSEAccountEntry a on b.fk=a.int_CSEAccountEntryId

Table 'tbl_CSEAccountEntry'. Scan count 1, logical reads 2
Table 'tbl_CSEDebtorAccountEntry'. Scan count 1, logical reads 2
Table 'tbl_CSEActorAccountEntry'. Scan count 1, logical reads 2

scan count 3, logical reads 6 In my opinion you must try this

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 07:11:02

Strange, I can't get those results from "SET STATISTICS IO ON".
However in SSMS when running Reporter2 and Peso2 queries, reporter2 query get 79% of the batch and Peso2 only 21%.
Reporter2 query averages on 31 ms and Peso2 averages on 0 ms.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 07:14:40

Reporter2 query also spend only 25% of time on index operations.
46% for a hash join and 29% of query in a sort.

Peso2 query spends 99% of time on index operations.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 07:24:48

Reporter2 query is the same as this (in my opinion more readable)

SELECT		ae.big_CSECase_FK,
		SUM(a.Act) AS Act,
		SUM(d.Debt) AS Debt
FROM		tbl_CSEAccountEntry AS ae
INNER JOIN	(
			SELECT		int_CSEAccountEntry_FK,
					SUM(mny_Amount) AS Act
			FROM		tbl_CSEActorAccountEntry
			GROUP BY	int_CSEAccountEntry_FK
		) AS a ON a.int_CSEAccountEntry_FK = ae.int_CSEAccountEntryId
INNER JOIN	(
			SELECT		int_CSEAccountEntry_FK,
					SUM(mny_Amount) AS Debt
			FROM		tbl_CSEDebtorAccountEntry
			GROUP BY	int_CSEAccountEntry_FK
		) AS d ON d.int_CSEAccountEntry_FK = ae.int_CSEAccountEntryId 
GROUP BY	ae.big_CSECase_FK
HAVING		SUM(a.Act) <> SUM(d.Debt)

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-10-26 : 07:42:15

Peter, this isn't the same absolutely. See execution plans. The cost of all iterators is 0%
And "scan count 7, logical reads 36" of your query is not the same
with "scan count 3, logical reads 6". Even if it's more readable.

In additional, here is query cost (relative to the batch) was obtained by Query Analyzer

thenearfuture 0,00
Peso 1 26,32
Peso 2 26,32
Reporter 1 47,37
Reporter 2 0,00

Nevertheless, will be waiting any result from thenearfuture.

thenearfuture
Starting Member

35 Posts

Posted - 2007-10-26 : 08:14:01

Reporter2 running time: 2:46

Reporter2 Execution plan:

select a.big_CSECase_FK,b.SumAct,b.SumDebt from (select fk=isnull(b.int_CSEAccountEntry_FK,c.int_CSEAccountEntry_FK),SumAct=sum(isnull(b.mny_Amount,0)),SumDebt=sum(isnull(c.mny_Amount,0)) from tbl_CSEActorAccountEntry as b full join tbl_CSEDebtorAccount
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]) WITH PREFETCH)
|--Filter(WHERE:([Expr1003]<>[Expr1004]))
| |--Compute Scalar(DEFINE:([Expr1003]=If ([Expr1014]=0) then NULL else [Expr1015], [Expr1004]=If ([Expr1016]=0) then NULL else [Expr1017]))
| |--Hash Match(Aggregate, HASH:([Expr1002]), RESIDUAL:([Expr1002]=[Expr1002]) DEFINE:([Expr1014]=COUNT_BIG(isnull([b].[mny_Amount], 0.00)), [Expr1015]=SUM(isnull([b].[mny_Amount], 0.00)), [Expr1016]=COUNT_BIG(isnull([c].[mny_Amount], 0.00))
| |--Compute Scalar(DEFINE:([Expr1002]=isnull([b].[int_CSEAccountEntry_FK], [c].[int_CSEAccountEntry_FK])))
| |--Concatenation
| |--Compute Scalar(DEFINE:([c].[mny_Amount]=NULL, [c].[int_CSEAccountEntry_FK]=NULL))
| | |--Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEActorAccountEntry].[ix_peso] AS [b]))
| |--Compute Scalar(DEFINE:([b].[mny_Amount]=NULL, [b].[int_CSEAccountEntry_FK]=NULL))
| |--Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEDebtorAccountEntry].[ix_peso] AS [c]))
|--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_CSEAccountEntry].[PK_tbl_CSEAccountEntry] AS [a]), SEEK:([a].[int_CSEAccountEntryId]=[Expr1002]) ORDERED FORWARD)

-----

Peso: Yep, tried index hints - no change.

-----

Thank you both for your willingness to keep picking at this problem. The sample data set just seems to be too small to accurately reflect what's happening with the much larger actual database. I think my department will probably just have to use the 20 second query for now. (Or at least until we drag a certain real live consultant into the office again, kicking and screaming.

)

Reporter
Starting Member

48 Posts

Posted - 2007-10-26 : 08:40:32

THERE IS NO "HASH MATCH" ITERATOR!!!

select a.big_CSECase_FK,b.SumAct,b.SumDebt from
(select fk=isnull(b.int_CSEAccountEntry_FK,c.int_CSEAccountEntry_FK),SumAct=sum(isnull(b.mny_Amount,0)),SumDebt=sum(isnull(c.mny_Amount,0))
from tbl_CSEActorAccountEntry as b full join tbl_CSEDebtorAccountEntry as c on 1<>1 group by isnull(b.int_CSEAccountEntry_FK,c.int_CSEAccountEntry_FK)
having sum(isnull(b.mny_Amount,0))<>sum(isnull(c.mny_Amount,0))) as b inner join
tbl_CSEAccountEntry a on b.fk=a.int_CSEAccountEntryId

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))
|--Filter(WHERE:([Expr1003]<>[Expr1004]))
| |--Compute Scalar(DEFINE:([Expr1003]=If ([Expr1014]=0) then NULL else [Expr1015], [Expr1004]=If ([Expr1016]=0) then NULL else [Expr1017]))
| |--Stream Aggregate(GROUP BY:([Expr1002]) DEFINE:([Expr1014]=COUNT_BIG(isnull([b].[mny_Amount], 0.00)), [Expr1015]=SUM(isnull([b].[mny_Amount], 0.00)), [Expr1016]=COUNT_BIG(isnull([c].[mny_Amount], 0.00)), [Expr1017]=SUM(isnull([c].[mny_Am
| |--Sort(ORDER BY:([Expr1002] ASC))
| |--Compute Scalar(DEFINE:([Expr1002]=isnull([b].[int_CSEAccountEntry_FK], [c].[int_CSEAccountEntry_FK])))
| |--Concatenation
| |--Compute Scalar(DEFINE:([c].[mny_Amount]=NULL, [c].[int_CSEAccountEntry_FK]=NULL))
| | |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[tbl_CSEActorAccountEntry].[PK_tbl_CSEActorAccountEntry] AS [b]))
| |--Compute Scalar(DEFINE:([b].[mny_Amount]=NULL, [b].[int_CSEAccountEntry_FK]=NULL))
| |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[tbl_CSEDebtorAccountEntry].[PK_tbl_CSEDebtorAccountEntryNew] AS [c]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[tbl_CSEAccountEntry].[PK_tbl_CSEAccountEntry] AS [a]), SEEK:([a].[int_CSEAccountEntryId]=[Expr1002]) ORDERED FORWARD)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources