Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql updating columns in terms of efficiency - 2008 r2 express

Posted on 2013-01-17
5
Medium Priority
?
232 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 40

Assisted Solution

by:lcohan
lcohan earned 800 total points
ID: 38788149
" 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 38792272
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
ID: 38792750
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38792779
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
ID: 38792790
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 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