Solved

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

Posted on 2013-02-04
7
539 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
ID: 38852385
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
ID: 38852514
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
ID: 38852521
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38853267
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
ID: 38853304
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
ID: 38855540
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
ID: 38855862
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlAdvisor 2016 3 28
Syntax using Declare 4 38
CROSS APPLY 4 44
Get Duration of last Status Update 4 30
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

24 Experts available now in Live!

Get 1:1 Help Now