Solved

SQL Transaction was deadlocked on lock resources.. on Stored Procedure, multiple users calling SP at same time

Posted on 2013-02-04
7
527 Views
Last Modified: 2013-02-11
error pictureHello All,

Very new to SQL here and I have following issue.
I am receiving the following error message, picture attached.

This particular error is when I call the Stored Procedure below called createMarginCallGridTotals.

Multiple users are calling this SP at the same time.  Which are returning about 7 different columns of data.  

I can't see to figure out why I am getting these errors... the error is at totally different times so I dont know why its occuring.

One thing to note is USERS are calling this SP every 5 seconds.  Say for example 5 different users at the same time.

Now in addition to this, my main ADMIN program, is updating these tables every 5 seconds with new data.  Its not deleting anything, just UPDATING.

Can someone please help me track this issue down?  What info can I provide as I know this is not enough details to troubleshoot.

Thanks in advance!!

USE [GSS]
GO
/****** Object:  StoredProcedure [dbo].[CreateMarginCallGridTotals]    Script Date: 02/04/2013 13:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CreateMarginCallGridTotals] 
	-- Add the parameters for the stored procedure here
	@cobDate nvarchar(50),
	@clientID nvarchar(255)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT 
--SET COLUMN = CLIENTNAME
	(
	SELECT TOP 1 dbo.ClientsDB.Client_Name FROM dbo.ClientsDB 
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Client_ID = dbo.ClientsDB.Client_ID
	WHERE dbo.ClientsDB.Client_ID = @clientID
	) AS [Client Name],
--SET COLUMN = BEG NET LIQ
(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	) AS BegNetLiq,
--SET COLUMN = NET POS
	SUM(newTable.[TopdayNet]) AS NetPosition, 
--SET COLUMN = TOTAL MAINT MARGIN USAGE	
	SUM(newTable.[TotalMaintMarginUsage]) AS [TotalMaintMarginUsage], 
--SET COLUMN = TOTAL PL AS OF SETTLEMENTS
	SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle,
--SET COLUMN = REALTIME NET LIQ
	(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	)
	+
	SUM(newTable.[P&LasofSettle]) 
	AS [Realtime NetLiq],
--SET COLUMN = MAINT EXCESS
	(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	)
	+
	SUM(newTable.[P&LasofSettle])
	-
	SUM(newTable.[TotalMaintMarginUsage]) AS [Maintenance Excess],
--SET COLUMN = PERCENTAGE
	NULLIF(SUM(newTable.[P&LasofSettle]),0)
	/
	NULLIF(
	(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	),0) as [Perc Gain/Loss]
--*********************************************************
	FROM
		(
		SELECT dbo.TopdayTradesDB.Account_ID, dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.Account_Description, dbo.TopdayTradesDB.BloombergSymbol, dbo.AccountsDB.Client_ID, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[MarginMaint Total USD]) As TotalMaintMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.TopdayTradesDB
		INNER JOIN dbo.AccountsDB
		ON dbo.AccountsDB.Account_ID = dbo.TopdayTradesDB.Account_ID
		WHERE [Upload_Timestamp] =
			(SELECT TOP 1 [Upload_Timestamp]
			FROM dbo.TopdayTradesDB
			ORDER BY [Upload_Timestamp] DESC)
		AND dbo.AccountsDB.Client_ID = @clientID
		AND dbo.AccountsDB.Account_Active = 1
		GROUP BY dbo.TopdayTradesDB.[Account_ID], dbo.TopdayTradesDB.[Account_Number], dbo.TopdayTradesDB.[Account_Description], BloombergSymbol, [Client_ID]
		UNION ALL
		SELECT dbo.DailyPositions_JEFF.Account_ID, dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.Account_Description, dbo.DailyPositions_JEFF.BloombergSymbol, dbo.AccountsDB.Client_ID, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[MarginMaint Total USD]) As TotalMaintMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.DailyPositions_JEFF
		INNER JOIN dbo.AccountsDB
		ON dbo.AccountsDB.Account_ID = dbo.DailyPositions_JEFF.Account_ID
		WHERE [COB_Date] = @cobDate
		AND dbo.AccountsDB.Client_ID = @clientID
		AND dbo.AccountsDB.Account_Active = 1
		GROUP BY dbo.DailyPositions_JEFF.[Account_ID], dbo.DailyPositions_JEFF.[Account_Number], dbo.DailyPositions_JEFF.[Account_Description], BloombergSymbol, [Client_ID]
		) newTable
END

Open in new window

0
Comment
Question by:solarissf
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
sorry just a quick response as i unavailable for a few hours

confirm that you haven't "lost" any indexes recently

and have a look at the query plan being used by this procedure...

my first thought looking at the sql is that it could be simplified which would probably help

--SET COLUMN = CLIENTNAME
	(
	SELECT TOP 1 dbo.ClientsDB.Client_Name FROM dbo.ClientsDB 
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Client_ID = dbo.ClientsDB.Client_ID
	WHERE dbo.ClientsDB.Client_ID = @clientID
	) AS [Client Name],

Open in new window


why the join to the account table above... is that then the cause for the top 1?

surely

--SET COLUMN = CLIENTNAME
	(
	SELECT TOP 1 dbo.ClientsDB.Client_Name FROM dbo.ClientsDB 
	
	WHERE dbo.ClientsDB.Client_ID = @clientID
	) AS [Client Name],

Open in new window


is all thats actually required , and hopefully without the top 1 dependant on your history strategy for the client table.... any way without an order by for the top 1 to work on you would be liable to "pick" an old name still

(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	) 

Open in new window

the aboce sub select appears to be in several places ... may be better to just do it once separately and then reference the value in this select via a variable...
0
 

Author Comment

by:solarissf
Comment Utility
thank you and I appreciate the help.

confirm that you haven't "lost" any indexes recently
How do I know if I "lost" any indexes?

I'm sure my procedure could be written better as I am just learning.

why the join to the account table above... is that then the cause for the top 1?

You are absolutely correct.  Who knows what I was thinking when I wrote that.  Either way I changed to your suggestions and I didnt even need TOP 1

	
	SELECT dbo.ClientsDB.Client_Name FROM dbo.ClientsDB
	WHERE dbo.ClientsDB.Client_ID = @clientID

Open in new window


I'm not opposed to using a variable... just dont know how to use it.


Random question... if I have, just to make things simplistic, a main program that updates tables every 5 seconds.  And a user program that runs these SP on those tables... (user program not updating, only querying), and they run at the exact second, is that allowed?
0
 

Author Comment

by:solarissf
Comment Utility
updated SP below

USE [GSS]
GO
/****** Object:  StoredProcedure [dbo].[CreateMarginCallGridTotals]    Script Date: 02/04/2013 15:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CreateMarginCallGridTotals] 
	-- Add the parameters for the stored procedure here
	@cobDate nvarchar(50),
	@clientID nvarchar(255)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT 
--SET COLUMN = CLIENTNAME
	(
	SELECT dbo.ClientsDB.Client_Name FROM dbo.ClientsDB
	WHERE dbo.ClientsDB.Client_ID = @clientID
	--SELECT TOP 1 dbo.ClientsDB.Client_Name FROM dbo.ClientsDB 
	--INNER JOIN dbo.AccountsDB
	--ON dbo.AccountsDB.Client_ID = dbo.ClientsDB.Client_ID
	--WHERE dbo.ClientsDB.Client_ID = @clientID
	) AS [Client Name],
--SET COLUMN = BEG NET LIQ
(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	) AS BegNetLiq,
--SET COLUMN = NET POS
	SUM(newTable.[TopdayNet]) AS NetPosition, 
--SET COLUMN = TOTAL MAINT MARGIN USAGE	
	SUM(newTable.[TotalMaintMarginUsage]) AS [TotalMaintMarginUsage], 
--SET COLUMN = TOTAL PL AS OF SETTLEMENTS
	SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle,
--SET COLUMN = REALTIME NET LIQ
	(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	)
	+
	SUM(newTable.[P&LasofSettle]) 
	AS [Realtime NetLiq],
--SET COLUMN = MAINT EXCESS
	(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	)
	+
	SUM(newTable.[P&LasofSettle])
	-
	SUM(newTable.[TotalMaintMarginUsage]) AS [Maintenance Excess],
--SET COLUMN = PERCENTAGE
	NULLIF(SUM(newTable.[P&LasofSettle]),0)
	/
	NULLIF(
	(
	SELECT
	SUM(dbo.LedgerDB.Journal_Amount)
	FROM dbo.LedgerDB
	INNER JOIN dbo.AccountsDB
	ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
	WHERE dbo.AccountsDB.Client_ID = @clientID
	AND dbo.LedgerDB.Journal_Type = 'NLV'
	And dbo.LedgerDB.Journal_Date = @cobDate
	AND dbo.AccountsDB.Account_Active = 1
	),0) as [Perc Gain/Loss]
--*********************************************************
	FROM
		(
		SELECT dbo.TopdayTradesDB.Account_ID, dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.Account_Description, dbo.TopdayTradesDB.BloombergSymbol, dbo.AccountsDB.Client_ID, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[MarginMaint Total USD]) As TotalMaintMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.TopdayTradesDB
		INNER JOIN dbo.AccountsDB
		ON dbo.AccountsDB.Account_ID = dbo.TopdayTradesDB.Account_ID
		WHERE [Upload_Timestamp] =
			(SELECT TOP 1 [Upload_Timestamp]
			FROM dbo.TopdayTradesDB
			ORDER BY [Upload_Timestamp] DESC)
		AND dbo.AccountsDB.Client_ID = @clientID
		AND dbo.AccountsDB.Account_Active = 1
		GROUP BY dbo.TopdayTradesDB.[Account_ID], dbo.TopdayTradesDB.[Account_Number], dbo.TopdayTradesDB.[Account_Description], BloombergSymbol, [Client_ID]
		UNION ALL
		SELECT dbo.DailyPositions_JEFF.Account_ID, dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.Account_Description, dbo.DailyPositions_JEFF.BloombergSymbol, dbo.AccountsDB.Client_ID, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[MarginMaint Total USD]) As TotalMaintMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.DailyPositions_JEFF
		INNER JOIN dbo.AccountsDB
		ON dbo.AccountsDB.Account_ID = dbo.DailyPositions_JEFF.Account_ID
		WHERE [COB_Date] = @cobDate
		AND dbo.AccountsDB.Client_ID = @clientID
		AND dbo.AccountsDB.Account_Active = 1
		GROUP BY dbo.DailyPositions_JEFF.[Account_ID], dbo.DailyPositions_JEFF.[Account_Number], dbo.DailyPositions_JEFF.[Account_Description], BloombergSymbol, [Client_ID]
		) newTable
END

Open in new window

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
yes you can have multiple / frequent updates going on in your system , you just have to ensure that the application and system is designed to cater for that scenario from the start...

i think i'd have expected it too be written more like this

USE [GSS]
GO
/****** Object:  StoredProcedure [dbo].[CreateMarginCallGridTotals]    Script Date: 02/04/2013 15:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CreateMarginCallGridTotals] 
	-- Add the parameters for the stored procedure here
	@cobDate nvarchar(50),
	@clientID nvarchar(255)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

declare @begNetLiq numeric(18,6)   --<--   set to whatever mi,rtiv data type is appropiate

SELECT   @begnetliq =
	SUM(dbo.LedgerDB.Journal_Amount)
   FROM dbo.LedgerDB
  INNER JOIN dbo.AccountsDB
         ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
 WHERE dbo.AccountsDB.Client_ID = @clientID
       AND dbo.LedgerDB.Journal_Type = 'NLV'
       And dbo.LedgerDB.Journal_Date = @cobDate
      AND dbo.AccountsDB.Account_Active = 1


    -- Insert statements for procedure here
SELECT 
--SET COLUMN = CLIENTNAME
	(
	SELECT dbo.ClientsDB.Client_Name FROM dbo.ClientsDB
	WHERE dbo.ClientsDB.Client_ID = @clientID
	--SELECT TOP 1 dbo.ClientsDB.Client_Name FROM dbo.ClientsDB 
	--INNER JOIN dbo.AccountsDB
	--ON dbo.AccountsDB.Client_ID = dbo.ClientsDB.Client_ID
	--WHERE dbo.ClientsDB.Client_ID = @clientID
	) AS [Client Name],
--SET COLUMN = BEG NET LIQ
(  @begnetliq 
	
	) AS BegNetLiq,
--SET COLUMN = NET POS
	SUM(newTable.[TopdayNet]) AS NetPosition, 
--SET COLUMN = TOTAL MAINT MARGIN USAGE	
	SUM(newTable.[TotalMaintMarginUsage]) AS [TotalMaintMarginUsage], 
--SET COLUMN = TOTAL PL AS OF SETTLEMENTS
	SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle,
--SET COLUMN = REALTIME NET LIQ
	(
	@begnetliq 
	)
	+
	SUM(newTable.[P&LasofSettle]) 
	AS [Realtime NetLiq],
--SET COLUMN = MAINT EXCESS
	(
	@begnetliq 
	)
	+
	SUM(newTable.[P&LasofSettle])
	-
	SUM(newTable.[TotalMaintMarginUsage]) AS [Maintenance Excess],
--SET COLUMN = PERCENTAGE
	NULLIF(SUM(newTable.[P&LasofSettle]),0)
	/
	NULLIF(
	(
	@begnetliq 
	),0) as [Perc Gain/Loss]
--*********************************************************
	FROM  dbo.AccountsDB
                    inner join
		(
		SELECT TT.Account_ID, TT.Account_Number, TT.Account_Description, TT.BloombergSymbol
                                                       , SUM(TT.Qty_Net) As TopdayNet, SUM(TT.[MarginMaint Total USD]) As TotalMaintMarginUsage
                                                       , SUM(TT.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.TopdayTradesDB as TT
		
		WHERE [Upload_Timestamp] =
			(SELECT TOP 1 [Upload_Timestamp]
			FROM dbo.TopdayTradesDB
			ORDER BY [Upload_Timestamp] DESC)
		
		GROUP BY TT.[Account_ID], TT.[Account_Number], TT.[Account_Description], BloombergSymbol
		UNION ALL
		SELECT DP.Account_ID, DP.Account_Number, DP.Account_Description, DP.BloombergSymbol
                                                      ,  SUM(DP.Qty_Net) As TopdayNet, SUM(DP.[MarginMaint Total USD]) As TotalMaintMarginUsage
                                                      , SUM(DP.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.DailyPositions_JEFF as DP
		
		WHERE [COB_Date] = @cobDate
		
		GROUP BY DP.[Account_ID], DP.[Account_Number], DP.[Account_Description], BloombergSymbol
		) newTable
ON dbo.AccountsDB.Account_ID = newtable.Account_ID
where dbo.AccountsDB.Client_ID = @clientID
AND dbo.AccountsDB.Account_Active = 1
END

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
then i'd have expected you to have considered implementing indexed view to minimise the impact of your requirements for near/realtime updates and access to summarised daily/top positions ... e.g.

create view dailytoptrades as (
SELECT TT.Account_ID, TT.Account_Number, TT.Account_Description, TT.BloombergSymbol
                                                       , SUM(TT.Qty_Net) As TopdayNet, SUM(TT.[MarginMaint Total USD]) As TotalMaintMarginUsage
                                                       , SUM(TT.[P&L Settlement]) AS [P&LasofSettle]
     ,upload_timestamp
            FROM dbo.TopdayTradesDB as TT
            GROUP BY TT.[Account_ID], TT.[Account_Number], TT.[Account_Description], BloombergSymbol,upload_timestamp)

create unique clusterid index dailytoptrade_idx on dailytoptrades as (upload_timestamp desc,account_id)

create view dailypositionsummary as (SELECT DP.Account_ID, DP.Account_Number, DP.Account_Description, DP.BloombergSymbol
                                                      ,  SUM(DP.Qty_Net) As TopdayNet, SUM(DP.[MarginMaint Total USD]) As TotalMaintMarginUsage
                                                      , SUM(DP.[P&L Settlement]) AS [P&LasofSettle],cob_date
            FROM dbo.DailyPositions_JEFF as DP
            
            
            GROUP BY DP.[Account_ID], DP.[Account_Number], DP.[Account_Description], BloombergSymbol,cob_date)

create unique clusetered index dailypossum_idx on dailypositionsummary as (cob_date desc,account_id)

then the procedure becomes

USE [GSS]
GO
/****** Object:  StoredProcedure [dbo].[CreateMarginCallGridTotals]    Script Date: 02/04/2013 15:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CreateMarginCallGridTotals]
      -- Add the parameters for the stored procedure here
      @cobDate nvarchar(50),
      @clientID nvarchar(255)

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

declare @begNetLiq numeric(18,6)   --<--   set to whatever mi,rtiv data type is appropiate

SELECT   @begnetliq =
      SUM(dbo.LedgerDB.Journal_Amount)
   FROM dbo.LedgerDB
  INNER JOIN dbo.AccountsDB
         ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID
 WHERE dbo.AccountsDB.Client_ID = @clientID
       AND dbo.LedgerDB.Journal_Type = 'NLV'
       And dbo.LedgerDB.Journal_Date = @cobDate
      AND dbo.AccountsDB.Account_Active = 1


    -- Insert statements for procedure here
SELECT
--SET COLUMN = CLIENTNAME
      (
      SELECT dbo.ClientsDB.Client_Name FROM dbo.ClientsDB
      WHERE dbo.ClientsDB.Client_ID = @clientID
      --SELECT TOP 1 dbo.ClientsDB.Client_Name FROM dbo.ClientsDB
      --INNER JOIN dbo.AccountsDB
      --ON dbo.AccountsDB.Client_ID = dbo.ClientsDB.Client_ID
      --WHERE dbo.ClientsDB.Client_ID = @clientID
      ) AS [Client Name],
--SET COLUMN = BEG NET LIQ
(  @begnetliq
      
      ) AS BegNetLiq,
--SET COLUMN = NET POS
      SUM(newTable.[TopdayNet]) AS NetPosition,
--SET COLUMN = TOTAL MAINT MARGIN USAGE      
      SUM(newTable.[TotalMaintMarginUsage]) AS [TotalMaintMarginUsage],
--SET COLUMN = TOTAL PL AS OF SETTLEMENTS
      SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle,
--SET COLUMN = REALTIME NET LIQ
      (
      @begnetliq
      )
      +
      SUM(newTable.[P&LasofSettle])
      AS [Realtime NetLiq],
--SET COLUMN = MAINT EXCESS
      (
      @begnetliq
      )
      +
      SUM(newTable.[P&LasofSettle])
      -
      SUM(newTable.[TotalMaintMarginUsage]) AS [Maintenance Excess],
--SET COLUMN = PERCENTAGE
      NULLIF(SUM(newTable.[P&LasofSettle]),0)
      /
      NULLIF(
      (
      @begnetliq
      ),0) as [Perc Gain/Loss]
--*********************************************************
      FROM  dbo.AccountsDB
                    inner join
            (
            SELECT Account_ID, Account_Number, Account_Description, BloombergSymbol
                                                       , TopdayNet, TotalMaintMarginUsage
                                                       ,  [P&LasofSettle]
            FROM dbo.dailytoptrades as TT            
            WHERE [Upload_Timestamp] =
                  (SELECT TOP 1 [Upload_Timestamp]
                  FROM dbo.TopdayTradesDB
                  ORDER BY [Upload_Timestamp] DESC)                  
            UNION ALL
            SELECT Account_ID, Account_Number, Account_Description, BloombergSymbol
                                                       , TopdayNet, TotalMaintMarginUsage
                                                       ,  [P&LasofSettle]
            FROM dbo.DailyPositionssummary            
            WHERE [COB_Date] = @cobDate            
            ) newTable
ON dbo.AccountsDB.Account_ID = newtable.Account_ID
where dbo.AccountsDB.Client_ID = @clientID
AND dbo.AccountsDB.Account_Active = 1
END

and you could also consider a trigger on the dailytoptrades table to provide the lastestupload timestamp as a single row table....

i notice that the account_description, bloombergsymbol etc don;appear to be used /required removing them from the code would
give performance benefits if they aren't required...
0
 

Author Comment

by:solarissf
Comment Utility
so I'm trying to piece this all together... its a bit above my paygrade ;)

where you said

i think i'd have expected it too be written more like this

I tried that code instead of my previous code.  I have no doubt that your code is more efficient than mine, however it take almost 2 seconds to return data.  Where my previous code instantly returned the data.  Then again... this whole post started because I am getting deadlocked resources.
Any thoughts on this?

Then your suggestions for indexed views and unique clusterid index.  I do not totally understand these yet but I am definately willing to do the legwork to learn the benefits on.

Question though... if I am able to successfully understand and implement your suggestions, will this fix the deadlocked resources issue?
0
 

Author Comment

by:solarissf
Comment Utility
just to give you more background.  This whole system I'm building is 3 different layers.

Layer 1 = small c# program that returns live pricing via an API connection, and saves prices to the TODAYS_MARKETS table every 5 seconds.

Layer 2 = another method that gets launched in the layer1 program in the same 5 seconds interval that does the following....
UPDATES about 10 columns of data in the DAILYPOSITIONS_JEFF table.  
Also updates about 10 columns in the TOPDAYTRADES tables.
This layer is essentially recalculating all of the p&l on every trade in the whole table.
Grabbing the prices FROM TODAY-MARKETS table.

Layer 3 = USER program.  Completely separate user interface login.  This program just uses stored procedure and select statements to grab the latest results from DAILYPOSITIONS_JEFF table AND TOPDAY TRADES table.  This is about every 5-10 seconds so it appears live for a user.

I'm open to critisim as this is my first full system design.  The method to my madness was do ALL the heavy lifting/calculations in SQL.  Then have a USER program just QUERY results.  So today the user is a winform, and tomorrow is a phone app, hopefully either way will be just return latest results via QUERYS and SP.

the locked resources if when the USER calls a SP from Layer 3
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now