Solved

sql updating columns in terms of efficiency - 2008 r2 express

Posted on 2013-01-17
5
184 Views
Last Modified: 2013-02-11
Good Morning All,

I have the following stored procedure in SQL 2008 R2 Express and just to warn I am a beginner.

It updates 24 different columns in one table.

Some are easy "vlookup" types... where it just looks up in another table and returns the result.... and some a calling scalar functions with parameters to do some simple calculations.

This main stored procedure is run on a timer every 5 seconds and the table that it is updating on a given time is about 2000 rows.  My first couple of runs... it takes about 2 seconds every time this stored procedure is run.

My question is... how can I speed it up?  Is my code horribly inefficient?
Any guidelines would be appreciated.

thanks

USE [GSS]
GO
/****** Object:  StoredProcedure [dbo].[SetMarketInputsTopday]    Script Date: 01/17/2013 08:26:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SetMarketInputsTopday]
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[COMPANY1_Multiplier] = 
	(SELECT 
		case 
			when [Clearing_Broker] = 'Company1' then 
				dbo.MarketsDB.[COMPANY1_MULTIPLIER]
			when [Clearing_Broker] = 'Company2'then	
				dbo.MarketsDB.[Company2_MULTIPLIER]
		end
	FROM dbo.MarketsDB
	INNER JOIN dbo.AccountsDB
	ON dbo.TopdayTradesDB.Account_ID = dbo.AccountsDB.Account_ID
	WHERE ([COMPANY1_SYMBOL]= dbo.TopdayTradesDB.[Product]
			AND [Clearing_Broker] = 'Company1')
	OR ([Company2_SYMBOL]= dbo.TopdayTradesDB.[Product]
			AND [Clearing_Broker] = 'Company2')
	)
	WHERE [COMPANY1_Multiplier] IS NULL
		
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Sector] = 
	(
	SELECT dbo.MarketsDB.[SECTOR]
	FROM dbo.MarketsDB
	WHERE [COMPANY1_SYMBOL]= dbo.TopdayTradesDB.[Product]
	Or 
	[Company2_SYMBOL] = dbo.TopdayTradesDB.[Product]
	)
	WHERE [Sector] IS NULL
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Currency] = dbo.Todays_MarketsDB.CRNCY
	FROM dbo.Todays_MarketsDB
	WHERE [BloombergSymbol] = [BBM_Symbol]
	AND dbo.TopdayTradesDB.[Currency] IS NULL
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Local Rate] = [Multiplier]
	FROM dbo.CurrencyRatesDB
	WHERE dbo.TopdayTradesDB.Currency = dbo.CurrencyRatesDB.[Curr Symbol]
	AND	[Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Value 1Point] = [FUT_VAL_PT]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND [Value 1Point] IS NULL
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Tick_Size] = [FUT_TICK_SIZE]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND [Tick_Size] IS NULL
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Tick_Value] = [FUT_TICK_VAL]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND [Tick_Value] IS NULL
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Settle Date] = [LAST_SETTLE_DATE]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND	[Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)

	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Settle Price] = [LAST_SETTLE_PRICE]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND	[Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Last Price] = [LAST_PRICE]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND	[Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	);
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Bid] = dbo.Todays_MarketsDB.[BID]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND	[Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	);
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Ask] = dbo.Todays_MarketsDB.[ASK]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND	[Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	);
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Delta Live]= 
	dbo.CreateLiveDelta([Trade_Price_Converted],[COMPANY1_Multiplier],[Last Price],[Bid],[Ask])
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[P&L Live] =
	case
		when dbo.TopdayTradesDB.[Value 1Point] = 0.9999 then
			ISNULL([Delta Live],0) * (( 1 / CAST([Tick_Size] AS numeric(18,5))) * ISNULL([Tick_Value],0)) * [Local Rate] * [Qty_Net]
		else
			ISNULL([Delta Live],0) * [Value 1Point] * [Local Rate] * [Qty_Net]
	end
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Delta Settlement] = 
	dbo.CreateSettlementDelta([Settle Date],[Settle Price],[Trade_Price_Converted],[COMPANY1_Multiplier],[Last Price],[Bid],[Ask])
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[P&L Settlement] =
	case
		when dbo.TopdayTradesDB.[Value 1Point] = 0.9999 then
			ISNULL([Delta Settlement],0) * (( 1 / CAST([Tick_Size] AS numeric(18,5))) * ISNULL([Tick_Value],0)) * [Local Rate] * [Qty_Net]
		else
			ISNULL([Delta Settlement],0) * [Value 1Point] * [Local Rate] * [Qty_Net]
	end
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Description] = dbo.Todays_MarketsDB.[NAME]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND [Description] IS NULL;
	
	UPDATE dbo.TopdayTradesDB
	SET [Short Description] = dbo.CreateShortDescription([Description])
	WHERE [Short Description] IS NULL
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Margin per lot Local] = dbo.Todays_MarketsDB.[FUT_INIT_SPEC_ML]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND [Margin per lot Local] IS NULL;
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Notional per lot Local] = dbo.Todays_MarketsDB.[CONTRACT_VALUE]
	FROM dbo.Todays_MarketsDB
	WHERE dbo.TopdayTradesDB.BloombergSymbol = dbo.Todays_MarketsDB.BBM_Symbol
	AND [Notional per lot Local] IS NULL;

	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Margin per lot USD] = ISNULL([Margin per lot Local],0) * [Local Rate]
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)

	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Notional per lot USD] = ISNULL([Notional per lot Local],0) * [Local Rate]
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Margin Total USD] = [Margin per lot USD] * ABS([Qty_Net])
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
	UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Notional Total USD] = [Notional per lot USD] * ABS([Qty_Net])
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)
	
END	

Open in new window

0
Comment
Question by:solarissf
  • 2
  • 2
5 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 200 total points
Comment Utility
" Is my code horribly inefficient?"

in my opinion yes if you need to run a stored proc like above so often in order to update data in permanent tables. In my opinion this (all or most of it) should be handeled by your UI code and not done on the back end SQL database tables.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
I agree with that...

though you could simplify some of the queries into 1, for example:

UPDATE dbo.TopdayTradesDB
	SET dbo.TopdayTradesDB.[Margin Total USD] = [Margin per lot USD] * ABS([Qty_Net])
           ,    dbo.TopdayTradesDB.[Notional Total USD] = [Notional per lot USD] * ABS([Qty_Net])
	WHERE [Upload_Timestamp] = 
	(
		SELECT TOP 1 [Upload_Timestamp]
		FROM DBO.TopdayTradesDB
		ORDER BY [Upload_Timestamp] DESC
	)

Open in new window

0
 

Author Comment

by:solarissf
Comment Utility
my original thought into my madness was...
if I do all the heavy lifting calculations in sql, then any user interface I create... whether winform, app, website... can just grab the results of the calculations already done in the database.

and if I do it in UIcode, then every interface I write I have to recreate the calculations.

am I wrong in thinking this way?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if you put the calculation into a stored procedure that does the insert/update(s), you can reuse that procedure for every UI ...
0
 

Author Comment

by:solarissf
Comment Utility
i must be mis-understanding something completely....
if you put the calculation into a stored procedure that does the insert/update(s), you can reuse that procedure for every UI ...

isnt that exactly what I am doing... meaning, all my update statements are in a stored procedure.

and my c# code just re-uses that SP every 5 seconds
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

16 Experts available now in Live!

Get 1:1 Help Now