November 20, 2017 at 2:11 pm
Just looking at the query itself shows that it could deal with a rewrite. The use of the YEAR function in the WHERE clause is going to cause scans on the gift table (or an index that has the [dte] column) How @Date and the [dte] variable and column are used looks like it is trying to identify year to date data. And then there is this, AND [c].[campaign_id] <> '*AA', that could be an issue.
November 20, 2017 at 2:15 pm
Just took a look at some of the joins, and yes, they could be problematic as well. Conditional joins can be painful.
Jeff puts it well, many problems can be fixed by simply looking at the code. I think this is one of them.
November 21, 2017 at 8:53 am
So we know we can make the query faster by a rewrite, we are kind of interested in the fact that the optimizer is picking a plan that is vastly superior in speed despite the fact that the statistics are very outdated.
So we did another test with just 2008R2, the same server where the query runs in about a minute. We rebuilt the stats on the gift table only using full scan. It takes 12 minutes now.
Original 2008R2 plan
https://www.brentozar.com/pastetheplan/?id=BJ891oexM runs in 1 minute
Rebuilt stats only on gift table
https://www.brentozar.com/pastetheplan/?id=Bkf9oa-eG now runs 12 minutes
So we are apples to apples now...
November 21, 2017 at 9:08 am
Do a wait stats analysis. There are 52 parallel operations in the slow plan and only 45 parallel operations in the fast plan. To break it out a bit more:
Slow plan: 52 parallel operations, 6 key lookups, 1 sort operation, 5 hash match operations, 3 clustered index scans, and 1 index scan.
Fast plan: 45 parallel operations, 7 key lookups, 1 sort operation, 1 clustered index scan
November 21, 2017 at 9:21 am
Also, I would look at a rewrite of the existing query. It looks like a year to date reporting query and there is a better way to do that little part than they way it is coded in your query. Also, with conditional join criteria, you may want to split out the query into two and union (or union all) the results together.
November 21, 2017 at 11:03 am
jcourtjr 29296 - Tuesday, November 21, 2017 8:53 AMSo we know we can make the query faster by a rewrite, we are kind of interested in the fact that the optimizer is picking a plan that is vastly superior in speed despite the fact that the statistics are very outdated.So we did another test with just 2008R2, the same server where the query runs in about a minute. We rebuilt the stats on the gift table only using full scan. It takes 12 minutes now.
Original 2008R2 plan
https://www.brentozar.com/pastetheplan/?id=BJ891oexM runs in 1 minuteRebuilt stats only on gift table
https://www.brentozar.com/pastetheplan/?id=Bkf9oa-eG now runs 12 minutesSo we are apples to apples now...
Makes perfect sense... sort of. As Lynn has pointed out, the problems are in the query. However, you're in a fairly unique position where the two bad/questionable plans (both are timeouts) don't perform equally badly. In fact, one of them performs radically better. It's just luck. You're going to have a VERY tough time controlling it. Also, it suggests you've stopped doing statistics maintenance. While clearly that's hurting this query, the lack of up to date stats are probably seriously impacting your other queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 21, 2017 at 12:02 pm
Not sure if this will work (return the same result set) as your current query, but it is a shot at a rewrite with nothing to test against. Be sure to check the comments I added in the code as well. I am curious how this works.
SELECT
[g].[id]
, [g].[dte]
, [g].[type]
, [g].[amount]
, [f].[fund_id]
, [f].[fund_category]
, [c].[campaign_id]
, [fat].[tableentriesid] AS [Pavilion_Building]
, [fat2].[tableentriesid] AS [Excluded_Fund]
, [fat3].[tableentriesid] AS [Unrestricted]
, [fat4].[tableentriesid] AS [College_Unrestricted]
, [f].[description]
, [r].[constituent_ID]
, [r].[anonymous]
, [r].[first_name]
, [r].[last_name]
, [r].[org_name]
, [r].[key_indicator]
, [gs].[giftsplitid]
, [gs].[amount] AS [SplitAmount]
, [te].[longdescription] AS [Fund_Cat_Desc]
FROM
[gift] AS [g]
INNER JOIN [giftsplit] AS [gs]
ON ([gs].[giftid] = [g].[id])
INNER JOIN [fund] AS [f]
ON [f].[id] = [gs].[fundid]
LEFT OUTER JOIN [campaign] AS [c]
ON [c].[id] = [gs].[campaignid]
--LEFT OUTER JOIN [giftattributes] AS [gat]
-- ON [gat].[parentid] = [g].[id]
-- AND [gat].[attributetypesid] = 167
-- AND [gat].[boolean] = -1
LEFT OUTER JOIN [fundattributes] AS [fat]
ON [fat].[parentid] = [f].[id]
AND [fat].[attributetypesid] = 149
AND
(
[fat].[tableentriesid] = 7025
OR [fat].[tableentriesid] = 9659
)
LEFT OUTER JOIN [fundattributes] AS [fat2]
ON [fat2].[parentid] = [f].[id]
AND [fat2].[attributetypesid] = 149
AND [fat2].[tableentriesid] = 7026
LEFT OUTER JOIN [fundattributes] AS [fat3]
ON [fat3].[parentid] = [f].[id]
AND [fat3].[attributetypesid] = 149
AND [fat3].[tableentriesid] = 7825
LEFT OUTER JOIN [fundattributes] AS [fat4]
ON [fat4].[parentid] = [f].[id]
AND [fat4].[attributetypesid] = 149
AND [fat4].[tableentriesid] = 7849
LEFT OUTER JOIN [records] AS [r]
ON [r].[id] = [g].[constit_id]
LEFT OUTER JOIN [tableentries] AS [te]
ON [te].[tableentriesid] = [f].[fund_category]
WHERE
NOT EXISTS(SELECT 1
FROM [giftattributes] AS [gat]
WHERE [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1)
/* A filtered index on giftattributes on parentid, attributetypesid, and boolean
where [attributetypesid] = 167 and [boolean] = -1 would probably benefit this
subquery
*/
-- [gat].[attributetypesid] IS NULL
-- AND YEAR(@Date) = YEAR([g].[dte])
-- AND @Date >= [g].[dte]
AND [g].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
AND
(
([g].[type] IN (1,8,9,10,15,18,27,31,34))
--OR
-- (
-- (
-- [g].[type] = 21
-- OR [g].[type] = 22
-- )
-- AND YEAR(@Date) = YEAR([g2].[dte])
-- AND @Date >= [g2].[dte]
-- )
)
AND [c].[campaign_id] <> '*AA'
UNION ALL
SELECT
[g].[id]
, [g].[dte]
, [g].[type]
, [g].[amount]
, [f].[fund_id]
, [f].[fund_category]
, [c].[campaign_id]
, [fat].[tableentriesid] AS [Pavilion_Building]
, [fat2].[tableentriesid] AS [Excluded_Fund]
, [fat3].[tableentriesid] AS [Unrestricted]
, [fat4].[tableentriesid] AS [College_Unrestricted]
, [f].[description]
, [r].[constituent_ID]
, [r].[anonymous]
, [r].[first_name]
, [r].[last_name]
, [r].[org_name]
, [r].[key_indicator]
, [gs].[giftsplitid]
, [gs].[amount] AS [SplitAmount]
, [te].[longdescription] AS [Fund_Cat_Desc]
FROM
[gift] AS [g]
LEFT OUTER JOIN [installmentpayment] AS 127.0.0.1
ON 127.0.0.1.[paymentid] = [g].[id]
LEFT OUTER JOIN [gift] AS [g2]
ON 127.0.0.1.[pledgeid] = [g2].[id]
INNER JOIN [giftsplit] AS [gs]
ON ([gs].[giftid] = [g2].[id])
INNER JOIN [fund] AS [f]
ON [f].[id] = [gs].[fundid]
LEFT OUTER JOIN [campaign] AS [c]
ON [c].[id] = [gs].[campaignid]
LEFT OUTER JOIN [giftattributes] AS [gat]
ON [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1
LEFT OUTER JOIN [fundattributes] AS [fat]
ON [fat].[parentid] = [f].[id]
AND [fat].[attributetypesid] = 149
AND
(
[fat].[tableentriesid] = 7025
OR [fat].[tableentriesid] = 9659
)
LEFT OUTER JOIN [fundattributes] AS [fat2]
ON [fat2].[parentid] = [f].[id]
AND [fat2].[attributetypesid] = 149
AND [fat2].[tableentriesid] = 7026
LEFT OUTER JOIN [fundattributes] AS [fat3]
ON [fat3].[parentid] = [f].[id]
AND [fat3].[attributetypesid] = 149
AND [fat3].[tableentriesid] = 7825
LEFT OUTER JOIN [fundattributes] AS [fat4]
ON [fat4].[parentid] = [f].[id]
AND [fat4].[attributetypesid] = 149
AND [fat4].[tableentriesid] = 7849
LEFT OUTER JOIN [records] AS [r]
ON [r].[id] = [g].[constit_id]
LEFT OUTER JOIN [tableentries] AS [te]
ON [te].[tableentriesid] = [f].[fund_category]
WHERE
NOT EXISTS(SELECT 1
FROM [giftattributes] AS [gat]
WHERE [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1)
/* A filtered index on giftattributes on parentid, attributetypesid, and boolean
where [attributetypesid] = 167 and [boolean] = -1 would probably benefit this
subquery
*/
-- [gat].[attributetypesid] IS NULL
-- AND YEAR(@Date) = YEAR([g].[dte])
-- AND @Date >= [g].[dte]
AND [g].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
AND
(
--([g].[type] IN (1,8,9,10,15,18,27,31,34))
--OR
(
[g].[type] IN (21,22)
AND [g2].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g2].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
--AND YEAR(@Date) = YEAR([g2].[dte])
--AND @Date >= [g2].[dte]
)
)
AND [c].[campaign_id] <> '*AA'
ORDER BY
[g].[id]
, [gs].[giftsplitid];
November 21, 2017 at 12:19 pm
Lynn Pettis - Tuesday, November 21, 2017 12:02 PMNot sure if this will work (return the same result set) as your current query, but it is a shot at a rewrite with nothing to test against. Be sure to check the comments I added in the code as well. I am curious how this works.
SELECT
[g].[id]
, [g].[dte]
, [g].[type]
, [g].[amount]
, [f].[fund_id]
, [f].[fund_category]
, [c].[campaign_id]
, [fat].[tableentriesid] AS [Pavilion_Building]
, [fat2].[tableentriesid] AS [Excluded_Fund]
, [fat3].[tableentriesid] AS [Unrestricted]
, [fat4].[tableentriesid] AS [College_Unrestricted]
, [f].[description]
, [r].[constituent_ID]
, [r].[anonymous]
, [r].[first_name]
, [r].[last_name]
, [r].[org_name]
, [r].[key_indicator]
, [gs].[giftsplitid]
, [gs].[amount] AS [SplitAmount]
, [te].[longdescription] AS [Fund_Cat_Desc]
FROM
[gift] AS [g]
INNER JOIN [giftsplit] AS [gs]
ON ([gs].[giftid] = [g].[id])
INNER JOIN [fund] AS [f]
ON [f].[id] = [gs].[fundid]
LEFT OUTER JOIN [campaign] AS [c]
ON [c].[id] = [gs].[campaignid]
--LEFT OUTER JOIN [giftattributes] AS [gat]
-- ON [gat].[parentid] = [g].[id]
-- AND [gat].[attributetypesid] = 167
-- AND [gat].[boolean] = -1
LEFT OUTER JOIN [fundattributes] AS [fat]
ON [fat].[parentid] = [f].[id]
AND [fat].[attributetypesid] = 149
AND
(
[fat].[tableentriesid] = 7025
OR [fat].[tableentriesid] = 9659
)
LEFT OUTER JOIN [fundattributes] AS [fat2]
ON [fat2].[parentid] = [f].[id]
AND [fat2].[attributetypesid] = 149
AND [fat2].[tableentriesid] = 7026
LEFT OUTER JOIN [fundattributes] AS [fat3]
ON [fat3].[parentid] = [f].[id]
AND [fat3].[attributetypesid] = 149
AND [fat3].[tableentriesid] = 7825
LEFT OUTER JOIN [fundattributes] AS [fat4]
ON [fat4].[parentid] = [f].[id]
AND [fat4].[attributetypesid] = 149
AND [fat4].[tableentriesid] = 7849
LEFT OUTER JOIN [records] AS [r]
ON [r].[id] = [g].[constit_id]
LEFT OUTER JOIN [tableentries] AS [te]
ON [te].[tableentriesid] = [f].[fund_category]
WHERE
NOT EXISTS(SELECT 1
FROM [giftattributes] AS [gat]
WHERE [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1)
/* A filtered index on giftattributes on parentid, attributetypesid, and boolean
where [attributetypesid] = 167 and [boolean] = -1 would probably benefit this
subquery
*/
-- [gat].[attributetypesid] IS NULL
-- AND YEAR(@Date) = YEAR([g].[dte])
-- AND @Date >= [g].[dte]
AND [g].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
AND
(
([g].[type] IN (1,8,9,10,15,18,27,31,34))
--OR
-- (
-- (
-- [g].[type] = 21
-- OR [g].[type] = 22
-- )
-- AND YEAR(@Date) = YEAR([g2].[dte])
-- AND @Date >= [g2].[dte]
-- )
)
AND [c].[campaign_id] <> '*AA'
UNION ALL
SELECT
[g].[id]
, [g].[dte]
, [g].[type]
, [g].[amount]
, [f].[fund_id]
, [f].[fund_category]
, [c].[campaign_id]
, [fat].[tableentriesid] AS [Pavilion_Building]
, [fat2].[tableentriesid] AS [Excluded_Fund]
, [fat3].[tableentriesid] AS [Unrestricted]
, [fat4].[tableentriesid] AS [College_Unrestricted]
, [f].[description]
, [r].[constituent_ID]
, [r].[anonymous]
, [r].[first_name]
, [r].[last_name]
, [r].[org_name]
, [r].[key_indicator]
, [gs].[giftsplitid]
, [gs].[amount] AS [SplitAmount]
, [te].[longdescription] AS [Fund_Cat_Desc]
FROM
[gift] AS [g]
LEFT OUTER JOIN [installmentpayment] AS 127.0.0.1
ON 127.0.0.1.[paymentid] = [g].[id]
LEFT OUTER JOIN [gift] AS [g2]
ON 127.0.0.1.[pledgeid] = [g2].[id]
INNER JOIN [giftsplit] AS [gs]
ON ([gs].[giftid] = [g2].[id])
INNER JOIN [fund] AS [f]
ON [f].[id] = [gs].[fundid]
LEFT OUTER JOIN [campaign] AS [c]
ON [c].[id] = [gs].[campaignid]
LEFT OUTER JOIN [giftattributes] AS [gat]
ON [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1
LEFT OUTER JOIN [fundattributes] AS [fat]
ON [fat].[parentid] = [f].[id]
AND [fat].[attributetypesid] = 149
AND
(
[fat].[tableentriesid] = 7025
OR [fat].[tableentriesid] = 9659
)
LEFT OUTER JOIN [fundattributes] AS [fat2]
ON [fat2].[parentid] = [f].[id]
AND [fat2].[attributetypesid] = 149
AND [fat2].[tableentriesid] = 7026
LEFT OUTER JOIN [fundattributes] AS [fat3]
ON [fat3].[parentid] = [f].[id]
AND [fat3].[attributetypesid] = 149
AND [fat3].[tableentriesid] = 7825
LEFT OUTER JOIN [fundattributes] AS [fat4]
ON [fat4].[parentid] = [f].[id]
AND [fat4].[attributetypesid] = 149
AND [fat4].[tableentriesid] = 7849
LEFT OUTER JOIN [records] AS [r]
ON [r].[id] = [g].[constit_id]
LEFT OUTER JOIN [tableentries] AS [te]
ON [te].[tableentriesid] = [f].[fund_category]
WHERE
NOT EXISTS(SELECT 1
FROM [giftattributes] AS [gat]
WHERE [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1)
/* A filtered index on giftattributes on parentid, attributetypesid, and boolean
where [attributetypesid] = 167 and [boolean] = -1 would probably benefit this
subquery
*/
-- [gat].[attributetypesid] IS NULL
-- AND YEAR(@Date) = YEAR([g].[dte])
-- AND @Date >= [g].[dte]
AND [g].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
AND
(
--([g].[type] IN (1,8,9,10,15,18,27,31,34))
--OR
(
[g].[type] IN (21,22)
AND [g2].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g2].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
--AND YEAR(@Date) = YEAR([g2].[dte])
--AND @Date >= [g2].[dte]
)
)
AND [c].[campaign_id] <> '*AA'
ORDER BY
[g].[id]
, [gs].[giftsplitid];
It runs in 3 seconds. And it worked on the first try.
https://www.brentozar.com/pastetheplan/?id=rkHjsgfez
I learned a lot from this. I have to get better at reading these plans...
Thanks to all of you!
November 21, 2017 at 12:26 pm
For S & G's could you post the new execution plan?
Never mind, you did.
November 21, 2017 at 12:31 pm
Oh, it wasn't really from reading the plan that I came up with the rewrite, I simply "looked with eye" at the code and made what I thought were logical changes based on what the existing code was trying to accomplish.
Given more time and the resources I might actually improve it more. Knowing the data would be a big help in that regard.
November 21, 2017 at 1:01 pm
Actually, I think I did. Try this one, of course it may not look the same thinking about it: (may need a group by clause)
SELECT
[g].[id]
, [g].[dte]
, [g].[type]
, [g].[amount]
, [f].[fund_id]
, [f].[fund_category]
, [c].[campaign_id]
--, [fat].[tableentriesid] AS [Pavilion_Building]
, CASE WHEN [fat].[attributetypesid] = 149
AND ([fat].[tableentriesid] = 7025
OR [fat].[tableentriesid] = 9659
) THEN [fat].[tableentriesid] END AS [Pavilion_Building]
--, [fat2].[tableentriesid] AS [Excluded_Fund]
, CASE WHEN [fat].[attributetypesid] = 149
AND [fat].[tableentriesid] = 7026
THEN [fat].[tableentriesid] END AS [Excluded_Fund]
--, [fat3].[tableentriesid] AS [Unrestricted]
, CASE WHEN [fat].[attributetypesid] = 149
AND [fat].[tableentriesid] = 7825
THEN [fat].[tableentriesid] END AS [Unrestricted]
--, [fat4].[tableentriesid] AS [College_Unrestricted]
, CASE WHEN [fat].[attributetypesid] = 149
AND [fat].[tableentriesid] = 7849
THEN [fat].[tableentriesid] END AS [College_Unrestricted]
, [f].[description]
, [r].[constituent_ID]
, [r].[anonymous]
, [r].[first_name]
, [r].[last_name]
, [r].[org_name]
, [r].[key_indicator]
, [gs].[giftsplitid]
, [gs].[amount] AS [SplitAmount]
, [te].[longdescription] AS [Fund_Cat_Desc]
FROM
[gift] AS [g]
INNER JOIN [giftsplit] AS [gs]
ON ([gs].[giftid] = [g].[id])
INNER JOIN [fund] AS [f]
ON [f].[id] = [gs].[fundid]
LEFT OUTER JOIN [campaign] AS [c]
ON [c].[id] = [gs].[campaignid]
--LEFT OUTER JOIN [giftattributes] AS [gat]
-- ON [gat].[parentid] = [g].[id]
-- AND [gat].[attributetypesid] = 167
-- AND [gat].[boolean] = -1
LEFT OUTER JOIN [fundattributes] AS [fat]
ON [fat].[parentid] = [f].[id]
-- AND [fat].[attributetypesid] = 149
-- AND
-- (
-- [fat].[tableentriesid] = 7025
-- OR [fat].[tableentriesid] = 9659
-- )
-- LEFT OUTER JOIN [fundattributes] AS [fat2]
-- ON [fat2].[parentid] = [f].[id]
-- AND [fat2].[attributetypesid] = 149
-- AND [fat2].[tableentriesid] = 7026
-- LEFT OUTER JOIN [fundattributes] AS [fat3]
-- ON [fat3].[parentid] = [f].[id]
-- AND [fat3].[attributetypesid] = 149
-- AND [fat3].[tableentriesid] = 7825
-- LEFT OUTER JOIN [fundattributes] AS [fat4]
-- ON [fat4].[parentid] = [f].[id]
-- AND [fat4].[attributetypesid] = 149
-- AND [fat4].[tableentriesid] = 7849
LEFT OUTER JOIN [records] AS [r]
ON [r].[id] = [g].[constit_id]
LEFT OUTER JOIN [tableentries] AS [te]
ON [te].[tableentriesid] = [f].[fund_category]
WHERE
NOT EXISTS(SELECT 1
FROM [giftattributes] AS [gat]
WHERE [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1)
/* A filtered index on giftattributes on parentid, attributetypesid, and boolean
where [attributetypesid] = 167 and [boolean] = -1 would probably benefit this
subquery
*/
-- [gat].[attributetypesid] IS NULL
-- AND YEAR(@Date) = YEAR([g].[dte])
-- AND @Date >= [g].[dte]
AND [g].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
AND
(
([g].[type] IN (1,8,9,10,15,18,27,31,34))
--OR
-- (
-- (
-- [g].[type] = 21
-- OR [g].[type] = 22
-- )
-- AND YEAR(@Date) = YEAR([g2].[dte])
-- AND @Date >= [g2].[dte]
-- )
)
AND [c].[campaign_id] <> '*AA'
UNION ALL
SELECT
[g].[id]
, [g].[dte]
, [g].[type]
, [g].[amount]
, [f].[fund_id]
, [f].[fund_category]
, [c].[campaign_id]
--, [fat].[tableentriesid] AS [Pavilion_Building]
, CASE WHEN [fat].[attributetypesid] = 149
AND ([fat].[tableentriesid] = 7025
OR [fat].[tableentriesid] = 9659
) THEN [fat].[tableentriesid] END AS [Pavilion_Building]
--, [fat2].[tableentriesid] AS [Excluded_Fund]
, CASE WHEN [fat].[attributetypesid] = 149
AND [fat].[tableentriesid] = 7026
THEN [fat].[tableentriesid] END AS [Excluded_Fund]
--, [fat3].[tableentriesid] AS [Unrestricted]
, CASE WHEN [fat].[attributetypesid] = 149
AND [fat].[tableentriesid] = 7825
THEN [fat].[tableentriesid] END AS [Unrestricted]
--, [fat4].[tableentriesid] AS [College_Unrestricted]
, CASE WHEN [fat].[attributetypesid] = 149
AND [fat].[tableentriesid] = 7849
THEN [fat].[tableentriesid] END AS [College_Unrestricted]
, [f].[description]
, [r].[constituent_ID]
, [r].[anonymous]
, [r].[first_name]
, [r].[last_name]
, [r].[org_name]
, [r].[key_indicator]
, [gs].[giftsplitid]
, [gs].[amount] AS [SplitAmount]
, [te].[longdescription] AS [Fund_Cat_Desc]
FROM
[gift] AS [g]
LEFT OUTER JOIN [installmentpayment] AS 127.0.0.1
ON 127.0.0.1.[paymentid] = [g].[id]
LEFT OUTER JOIN [gift] AS [g2]
ON 127.0.0.1.[pledgeid] = [g2].[id]
INNER JOIN [giftsplit] AS [gs]
ON ([gs].[giftid] = [g2].[id])
INNER JOIN [fund] AS [f]
ON [f].[id] = [gs].[fundid]
LEFT OUTER JOIN [campaign] AS [c]
ON [c].[id] = [gs].[campaignid]
LEFT OUTER JOIN [giftattributes] AS [gat]
ON [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1
LEFT OUTER JOIN [fundattributes] AS [fat]
ON [fat].[parentid] = [f].[id]
-- AND [fat].[attributetypesid] = 149
-- AND
-- (
-- [fat].[tableentriesid] = 7025
-- OR [fat].[tableentriesid] = 9659
-- )
-- LEFT OUTER JOIN [fundattributes] AS [fat2]
-- ON [fat2].[parentid] = [f].[id]
-- AND [fat2].[attributetypesid] = 149
-- AND [fat2].[tableentriesid] = 7026
-- LEFT OUTER JOIN [fundattributes] AS [fat3]
-- ON [fat3].[parentid] = [f].[id]
-- AND [fat3].[attributetypesid] = 149
-- AND [fat3].[tableentriesid] = 7825
-- LEFT OUTER JOIN [fundattributes] AS [fat4]
-- ON [fat4].[parentid] = [f].[id]
-- AND [fat4].[attributetypesid] = 149
-- AND [fat4].[tableentriesid] = 7849
LEFT OUTER JOIN [records] AS [r]
ON [r].[id] = [g].[constit_id]
LEFT OUTER JOIN [tableentries] AS [te]
ON [te].[tableentriesid] = [f].[fund_category]
WHERE
NOT EXISTS(SELECT 1
FROM [giftattributes] AS [gat]
WHERE [gat].[parentid] = [g].[id]
AND [gat].[attributetypesid] = 167
AND [gat].[boolean] = -1)
/* A filtered index on giftattributes on parentid, attributetypesid, and boolean
where [attributetypesid] = 167 and [boolean] = -1 would probably benefit this
subquery
*/
-- [gat].[attributetypesid] IS NULL
-- AND YEAR(@Date) = YEAR([g].[dte])
-- AND @Date >= [g].[dte]
AND [g].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
AND
(
--([g].[type] IN (1,8,9,10,15,18,27,31,34))
--OR
(
[g].[type] IN (21,22)
AND [g2].[dte] >= DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
AND [g2].[dte] < DATEADD(DAY,DATEDIFF(DAY,0,@Date),0) -- or CAST(@Date AS DATE)
--AND YEAR(@Date) = YEAR([g2].[dte])
--AND @Date >= [g2].[dte]
)
)
AND [c].[campaign_id] <> '*AA'
ORDER BY
[g].[id]
, [gs].[giftsplitid];
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply