CREATE PROCEDURE dbo.usp_Market_View_Get_Headline_Data_By_User
(
	@ImportBatchID	int,
	@CurrentUsername	[udtCode],
	@BrokerCode	udtCode = NULL,
	@ProviderCode	udtCode = NULL
)
AS
	-- Get the limit as a variable for improved performance
	DECLARE @Limit udtMoney
	SET @Limit = dbo.udf_Get_Market_View_Limit()

	--Return holdings for Brokers only associated with @CurrentUsername
	SELECT *
	FROM [CFD_Risk].[dbo].vw_Account_Totals A_T INNER JOIN [CFD_Risk].[dbo].udf_Get_Accounts_By_User(@CurrentUserName) A
		ON A_T.AccountNumber = A.AccountNumber
	WHERE ImportBatchID = @ImportBatchID 
		AND (BrokerCode = @BrokerCode OR @BrokerCode IS NULL)
		AND (ProviderCode = @ProviderCode OR @ProviderCode IS NULL)
		AND (ProviderCode <> 'PSil'  OR TradeValue < @Limit)
	RETURN

GO

SET @ImportBatchID = 396
SET @CurrentUsername = 'sarah'
EXEC @RC = [CFD_Risk].[dbo].[usp_Market_View_Get_Headline_Data_By_User] @ImportBatchID, @CurrentUsername, @BrokerCode, @ProviderCode

CREATE  FUNCTION dbo.udf_Get_Accounts_By_User
(
	@Username	udtCode
)
RETURNS TABLE
AS
	RETURN (SELECT DISTINCT A_S.AccountNumber
		FROM vw_User_Brokers_Active U_B_A INNER JOIN vw_Broker_Accounts_Current B_A_C 
			ON U_B_A.BrokerCode = B_A_C.BrokerCode INNER JOIN vw_Account_Siblings A_S 
			ON B_A_C.AccountNumber = A_S.AccountNumberKey
		WHERE U_B_A.Username = @Username)

StmtText                       
------------------------------ 
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
DECLARE @RC int
DECLARE @ImportBatchID int
DECLARE @CurrentUsername varchar(20)
DECLARE @BrokerCode varchar(20)
DECLARE @ProviderCode varchar(20)
-- Set parameter values
SET @ImportBatchID = 396

SET @CurrentUsername = 'sarah'


--EXEC sp_Recompile 'usp_Market_View_Get_Headline_Data_By_User'

EXEC @RC = [CFD_Risk].[dbo].[usp_Market_View_Get_Headline_Data_By_User] @ImportBatchID, @CurrentUsername, @BrokerCode, @ProviderCode
  



CREATE  PROCEDURE dbo.usp_Market_View_Get_Headline_Data_By_User
(
	@ImportBatchID	int,
	@CurrentUsername	[udtCode],
	@BrokerCode	udtCode = NULL,
	@ProviderCode	udtCode = NULL
)
AS
	-- Get the limit as a variable for improved performance
	DEC
  	SELECT *
	FROM [CFD_Risk].[dbo].vw_Account_Totals A_T INNER JOIN [CFD_Risk].[dbo].udf_Get_Accounts_By_User(@CurrentUserName) A
		ON A_T.AccountNumber = A.AccountNumber
	WHERE ImportBatchID = @ImportBatchID 
		AND (BrokerCode = @BrokerCode OR @Broker

(5 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Compute Scalar(DEFINE:([Expr1070]=If ([Expr1060]<>NULL) then [Expr1060] else [Trade].[ImportBatchID], [Expr1071]=isnull([Expr1064], 0.00), [Expr1072]=isnull([Expr1065], 0.00), [Expr1073]=isnull([Expr1066], 0.00), [Expr1074]=isnull([Expr1067], 0.0
            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Client].[ClientNumber]))
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Broker].[TeamID]))
                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Broker_Account].[BrokerCode]))
                 |    |    |--Hash Match(Inner Join, HASH:([Account].[AccountNumber])=([Expr1141]), RESIDUAL:([Expr1141]=[Account].[AccountNumber] AND ([Account].[ProviderCode]<>'PSil' OR isnull([Expr1022], 0.00)<[@Limit])))
                 |    |    |    |--Merge Join(Inner Join, MERGE:([Client].[ClientNumber])=([Account].[ClientNumber]), RESIDUAL:([Account].[ClientNumber]=[Client].[ClientNumber]))
                 |    |    |    |    |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Client].[PK_ClientNumber]), ORDERED FORWARD)
                 |    |    |    |    |--Sort(ORDER BY:([Account].[ClientNumber] ASC))
                 |    |    |    |         |--Merge Join(Inner Join, MERGE:([Account].[AccountNumber])=([Account].[AccountNumber]), RESIDUAL:([Account].[AccountNumber]=[Account].[AccountNumber]))
                 |    |    |    |              |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Account].[PK_AccountNumber]),  WHERE:([Account].[ProviderCode]=[@ProviderCode] OR [@ProviderCode]=NULL) ORDERED FORWARD)
                 |    |    |    |              |--Sort(ORDER BY:([Broker_Account].[AccountNumber] ASC))
                 |    |    |    |                   |--Sort(DISTINCT ORDER BY:([Rank1140] ASC))
                 |    |    |    |                        |--Hash Match(Inner Join, HASH:([Broker_Account].[AccountNumber])=([Account].[AccountNumber]), RESIDUAL:([Broker_Account].[AccountNumber]=[Account].[AccountNumber]))
                 |    |    |    |                             |--Rank
                 |    |    |    |                             |    |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Broker_Account].[PK_Broker_Account]), WHERE:((([Broker_Account].[BrokerCode]=[@BrokerCode] OR [@BrokerCode]=NULL) AND getdate()>=Convert([B
                 |    |    |    |                             |--Hash Match(Inner Join, HASH:([Account].[ClientNumber])=([Account].[ClientNumber]), RESIDUAL:([Account].[ClientNumber]=[Account].[ClientNumber]))
                 |    |    |    |                                  |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Account].[PK_AccountNumber]))
                 |    |    |    |                                  |--Hash Match(Inner Join, HASH:([Account].[AccountNumber])=([Broker_Account].[AccountNumber]), RESIDUAL:([Broker_Account].[AccountNumber]=[Account].[AccountNumber]))
                 |    |    |    |                                       |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Account].[PK_AccountNumber]))
                 |    |    |    |                                       |--Hash Match(Inner Join, HASH:([Union1050])=([Broker_Account].[BrokerCode]), RESIDUAL:([Broker_Account].[BrokerCode]=[Union1050]))
                 |    |    |    |                                            |--Merge Join(Union)
                 |    |    |    |                                            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([User_Brokers].[BrokerCode]))
                 |    |    |    |                                            |    |    |--Clustered Index Seek(OBJECT:([CFD_Risk].[dbo].[User_Brokers].[PK_User_Brokers]), SEEK:([User_Brokers].[Username]=[@CurrentUsername]) ORDERED FORWARD)
                 |    |    |    |                                            |    |    |--Clustered Index Seek(OBJECT:([CFD_Risk].[dbo].[Broker].[PK_Broker]), SEEK:([Broker].[BrokerCode]=[User_Brokers].[BrokerCode]) ORDERED FORWARD)
                 |    |    |    |                                            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Broker].[TeamID]))
                 |    |    |    |                                            |         |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Broker].[PK_Broker]), ORDERED FORWARD)
                 |    |    |    |                                            |         |--Clustered Index Seek(OBJECT:([CFD_Risk].[dbo].[User_Teams].[PK_User_Teams]), SEEK:([User_Teams].[Username]=[@CurrentUsername] AND [User_Teams].[TeamID]=[Broker].[Team
                 |    |    |    |                                            |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Broker_Account].[PK_Broker_Account]), WHERE:(getdate()>=Convert([Broker_Account].[EffectiveFromDate]) AND getdate()<=Convert([Bro
                 |    |    |    |--Compute Scalar(DEFINE:([Expr1141]=If ([Expr1061]<>NULL) then [Expr1061] else [Trade].[AccountNumber]))
                 |    |    |         |--Filter(WHERE:(If ([Expr1060]<>NULL) then [Expr1060] else [Trade].[ImportBatchID]=[@ImportBatchID]))
                 |    |    |              |--Hash Match(Full Outer Join, HASH:([Trade].[ImportBatchID], [Trade].[AccountNumber])=([Expr1060], [Expr1061]), RESIDUAL:([Trade].[ImportBatchID]=[Expr1060] AND [Trade].[AccountNumber]=[Expr1061]))
                 |    |    |                   |--Filter(WHERE:([Expr1022]<>0.00))
                 |    |    |                   |    |--Hash Match(Aggregate, HASH:([Trade].[ImportBatchID], [Trade].[AccountNumber]), RESIDUAL:([Trade].[ImportBatchID]=[Trade].[ImportBatchID] AND [Trade].[AccountNumber]=[Trade].[AccountNumber]) DEFINE:([Ex
                 |    |    |                   |         |--Clustered Index Scan(OBJECT:([CFD_Risk].[dbo].[Trade].[PK_Trade]))
                 |    |    |                   |--Compute Scalar(DEFINE:([Expr1060]=If ([Expr1053]<>NULL) then [Expr1053] else [ProviderHoldings].[ImportBatchID], [Expr1061]=If ([Expr1054]<>NULL) then [Expr1054] else [ProviderHoldings].[AccountNumber], [Ex
                 |    |    |                        |--Hash Match(Full Outer Join, HASH:([Expr1053], [Expr1054])=([ProviderHoldings].[ImportBatchID], [ProviderHoldings].[AccountNumber]), RESIDUAL:([Expr1053]=[ProviderHoldings].[ImportBatchID] AND [Expr1054
                 |    |    |                             |--Compute Scalar(DEFINE:([Expr1053]=If ([PSil_StockHoldings].[ImportBatchID]<>NULL) then [PSil_StockHoldings].[ImportBatchID] else [PSil_CashHoldings].[ImportBatchID], [Expr1054]=If ([PSil_StockHold
                 |    |    |                             |    |--Hash Match(Full Outer Join, HASH:([PSil_CashHoldings].[ImportBatchID], [PSil_CashHoldings].[AccountNumber])=([PSil_StockHoldings].[ImportBatchID], [PSil_StockHoldings].[AccountNumber]), RESID
                 |    |    |                             |         |--Filter(WHERE:([Expr1013]<>0.00))
                 |    |    |                             |         |    |--Hash Match(Aggregate, HASH:([PSil_CashHoldings].[ImportBatchID], [PSil_CashHoldings].[AccountNumber]), RESIDUAL:([PSil_CashHoldings].[ImportBatchID]=[PSil_CashHoldings].[ImportBatch
                 |    |    |                             |         |         |--Table Scan(OBJECT:([CFD_Risk].[dbo].[PSil_CashHoldings]))
                 |    |    |                             |         |--Filter(WHERE:([Expr1016]<>0.00 OR [Expr1017]<>0.00))
                 |    |    |                             |              |--Hash Match(Aggregate, HASH:([PSil_StockHoldings].[AccountNumber], [PSil_StockHoldings].[ImportBatchID]), RESIDUAL:([PSil_StockHoldings].[AccountNumber]=[PSil_StockHoldings].[Account
                 |    |    |                             |                   |--Table Scan(OBJECT:([CFD_Risk].[dbo].[PSil_StockHoldings]))
                 |    |    |                             |--Compute Scalar(DEFINE:([Expr1059]=isnull([Expr1004], 0.000000000000000)))
                 |    |    |                                  |--Hash Match(Right Outer Join, HASH:([Provider_Opening_Positions].[ImportBatchID], [Provider_Opening_Positions].[AccountNumber])=([ProviderHoldings].[ImportBatchID], [ProviderHoldings].[Account
                 |    |    |                                       |--Filter(WHERE:([Expr1004]<>0.000000000000000))
                 |    |    |                                       |    |--Hash Match(Aggregate, HASH:([Provider_Opening_Positions].[ImportBatchID], [Provider_Opening_Positions].[AccountNumber]), RESIDUAL:([Provider_Opening_Positions].[ImportBatchID]=[Prov
                 |    |    |                                       |         |--Hash Match(Inner Join, HASH:([Equity].[EquityID])=([Provider_Opening_Positions].[EquityID]))
                 |    |    |                                       |              |--Index Scan(OBJECT:([CFD_Risk].[dbo].[Equity].[IX_Equity_UNIQUE_NAME]),  WHERE:(NOT(like([Equity].[EquityName], '%spot%', NULL))))
                 |    |    |                                       |              |--Table Scan(OBJECT:([CFD_Risk].[dbo].[Provider_Opening_Positions]))
                 |    |    |                                       |--Filter(WHERE:([Expr1008]<>0.00 OR [Expr1009]<>0.00))
                 |    |    |                                            |--Hash Match(Aggregate, HASH:([ProviderHoldings].[ImportBatchID], [ProviderHoldings].[AccountNumber]), RESIDUAL:([ProviderHoldings].[ImportBatchID]=[ProviderHoldings].[ImportBatchID] 
                 |    |    |                                                 |--Table Scan(OBJECT:([CFD_Risk].[dbo].[ProviderHoldings]))
                 |    |    |--Clustered Index Seek(OBJECT:([CFD_Risk].[dbo].[Broker].[PK_Broker]), SEEK:([Broker].[BrokerCode]=[Broker_Account].[BrokerCode]) ORDERED FORWARD)
                 |    |--Clustered Index Seek(OBJECT:([CFD_Risk].[dbo].[Team].[PK_Team]), SEEK:([Team].[TeamID]=[Broker].[TeamID]) ORDERED FORWARD)
                 |--Table Spool
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([ClientGroup].[ClientGroupID]))
                           |--Index Scan(OBJECT:([CFD_Risk].[dbo].[ClientGroup].[IX_Family_Unique_Name]))
                           |--Clustered Index Seek(OBJECT:([CFD_Risk].[dbo].[ClientGroup_Clients].[PK_ClientGroup_Clients]), SEEK:([ClientGroup_Clients].[ClientGroupID]=[ClientGroup].[ClientGroupID] AND [ClientGroup_Clients].[ClientNumber]=[Client].[Client

(60 row(s) affected)

StmtText       
-------------- 
  	RETURN

(1 row(s) affected)

StmtText                      
----------------------------- 
SET STATISTICS PROFILE OFF

(1 row(s) affected)

quote:

---

That's the "total time" for the job then, including creating the Query Plan. previously your "10 second run" has been relying on the fact that a Query Plan was already cached.

To check this you could run it twice, and the second time it should be 10 seconds instead of 40 seconds.

---

SELECT *
FROM 
	[CFD_Risk].[dbo].vw_Account_Totals A_T INNER JOIN 
	(
	SELECT DISTINCT A_S.AccountNumber
	FROM vw_User_Brokers_Active U_B_A INNER JOIN vw_Broker_Accounts_Current B_A_C 
		ON U_B_A.BrokerCode = B_A_C.BrokerCode INNER JOIN vw_Account_Siblings A_S 
		ON B_A_C.AccountNumber = A_S.AccountNumberKey
	Where U_B_A.Username = @Username
	) A
	ON A_T.AccountNumber = A.AccountNumber
WHERE ImportBatchID = @ImportBatchID 
	AND (BrokerCode = @BrokerCode OR @BrokerCode IS NULL)
	AND (ProviderCode = @ProviderCode OR @ProviderCode IS NULL)
	AND (ProviderCode <> 'PSil'  OR TradeValue < @Limit)

SELECT	A_S.AccountNumber
FROM	vw_Account_Siblings AS A_S
WHERE	EXISTS
	(
	    SELECT  *
	    FROM    vw_Broker_Accounts_Current AS B_A_C
	    WHERE       B_A_C.AccountNumber = A_S.AccountNumberKey
		    AND EXISTS
		    (
			SELECT	*
			FROM	vw_User_Brokers_Active AS U_B_A
			WHERE	    U_B_A.BrokerCode = B_A_C.BrokerCode
				AND U_B_A.Username = @Username)
                   )
       )

quote:

---

Originally posted by Kristen

"it drastically disimproved the performance of the SQL-only version "

That's the "total time" for the job then, including creating the Query Plan. previously your "10 second run" has been relying on the fact that a Query Plan was already cached.

To check this you could run it twice, and the second time it should be 10 seconds instead of 40 seconds.

---

quote:

---

Originally posted by dairec

Tried the sub-query solution but same performance as the UDF.
I'd prefer to use the UDF since the functionality is used elsewhere in other SPROCs.

---

SELECT	A_S.AccountNumber
FROM	vw_Account_Siblings AS A_S
	inner join vw_Broker_Accounts_Current AS B_A_C on B_A_C.AccountNumber = A_S.AccountNumberKey
	inner join vw_User_Brokers_Active AS U_B_A
		on U_B_A.BrokerCode = B_A_C.BrokerCode
		AND U_B_A.Username = @Username

/*
	This view groups together Accounts with their sibling Accounts.
	Two accounts are siblings if they share the same Client.
	Even though Brokers are associated directly with an Account,
	a given Broker is also allowed to view data for the other Accounts
	for that Client, even if not directly associated with those Account.
	This is why, this view is needed.
*/
CREATE VIEW dbo.vw_Account_Siblings
AS
SELECT     A.AccountNumber AS AccountNumberKey, A2.AccountNumber
FROM         dbo.Account A RIGHT OUTER JOIN
                      dbo.Account A2 ON A.ClientNumber = A2.ClientNumber

quote:

---

Aggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhh! Why do people use RIGHT outer joins?

---

quote:

---

If you don't need both values from vw_Account_Siblings, create a different View that only gives the value you DO need, and uses an EXISTS rather than an OUTER JOIN. That will avoid the need for a DISTINCT.

---

...
	--Return holdings for Brokers only associated with @CurrentUsername
	SELECT AccountNumber INTO #UserAccounts FROM [CFD_Risk].[dbo].udf_Get_Accounts_By_User(@CurrentUserName)
 		 
	SELECT *
	FROM [CFD_Risk].[dbo].vw_Account_Totals A_T INNER JOIN #UserAccounts A
		ON A_T.AccountNumber = A.AccountNumber
	WHERE ImportBatchID = @ImportBatchID 
		AND (BrokerCode = @BrokerCode OR @BrokerCode IS NULL)
		AND (ProviderCode = @ProviderCode OR @ProviderCode IS NULL)
		AND (ProviderCode <> 'PSil'  OR TradeValue < @Limit)

	DROP TABLE #UserAccounts
RETURN