Solved

sql updating columns in terms of efficiency - 2008 r2 express

Posted on 2013-01-17
5
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 200 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 300 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

635 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