sql optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql optimization

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-11-15 : 17:59:14

How can I optimize this sp? Currently it causes locks.
declare @Deal_Control_Id int,
@pageNumber int ,
@PAGESIZE int ,
@sortColumn varchar(50) ,
@sortDirection varchar(4),
@totalRows int

set @Deal_Control_Id = 1488
set @pageNumber = 1
set @PAGESIZE = 10
set @sortColumn = 'deal_id'
set @sortDirection = 'desc'
DECLARE @RowCount int

DECLARE @page_temp TABLE (tempID int)

DECLARE @Deals TABLE (
[DEAL_ID] int ,
[DEAL_CONTROL_ID] int ,
[ORIGINAL_NOMINAL] decimal(18,8),
[CURRENT_NOMINAL] decimal(18,8),
[TRADED_NOMINAL] decimal(18,8),
[ACCOUNT_NAME] varchar(50),
[ACCOUNT_CODE] varchar(5),
[ACCOUNT_STATUS] varchar(50),
[MANAGERS_NOTES] varchar(1000),
[MANAGERS_NOTES_SHORT] varchar(20),
[SECURITY_NAME] varchar(100),
[SECUR_CODE] varchar(7),
[BROKER_NAME] varchar(50),
[LIMIT_PRICE] decimal(18,8),
[ACTION] int,
[IMPART_CODE] char(7),
[ORDER_ID] int,
[RELEASED_DATE] datetime,
[Currency_Code] char(5),
[PRICE_ORDERED] decimal(18,8)
)

INSERT INTO @Deals (DEAL_ID,DEAL_CONTROL_ID,ORIGINAL_NOMINAL,CURRENT_NOMINAL,TRADED_NOMINAL,ACCOUNT_NAME,ACCOUNT_CODE,ACCOUNT_STATUS, MANAGERS_NOTES,SECURITY_NAME,SECUR_CODE,BROKER_NAME,LIMIT_PRICE,ACTION,IMPART_CODE,ORDER_ID, RELEASED_DATE,Currency_Code, PRICE_ORDERED)

SELECT DISTINCT A.DEAL_ID,A.DEAL_CONTROL_ID,ORIGINAL_NOMINAL,CURRENT_NOMINAL,TRADED_NOMINAL,SHORT_TITLE,A.ACCOUNT_CODE,'',F.MANAGERS_NOTES,SECURITY_NAME,A.SECUR_CODE,A.BROKER,LIMIT_PRICE,A.ACTION,C.SETTLEMENT_REFERENCE_CODE, F.INITIATION_ID, E.RELEASED_DATE, B.Currency_code, ORD.PRICE_ORDERED
FROM ST_DEAL_DETAIL_EQUITY A INNER JOIN ST_SECURITIES_EQUITIES B ON A.SECUR_CODE = B.SECUR_CODE
INNER JOIN ST_ACCOUNT_MAIN C ON A.ACCOUNT_CODE = C.ACCOUNT_CODE
INNER JOIN ST_CONSOLIDATE_COMPONENTS D ON A.DEAL_ID = D.DEAL_ID AND A.DEAL_CONTROL_ID = D.DEAL_CONTROL_ID
INNER JOIN ST_DEAL_CONTROL E ON D.DEAL_CONTROL_ID = E.DEAL_CONTROL_ID
INNER JOIN ST_ORDER_CONTROL F ON E.INITIATION_ID = F.INITIATION_ID
INNER JOIN ST_RECOMMENDATION_CONTROL REC ON F.INITIATION_ID = REC.INITIATION_ID
INNER JOIN ST_ORDER_DETAILS_EQUITY ORD ON REC.RECOMMENDATION_ID = ORD.RECOMMENDATION_ID
AND A.ACCOUNT_CODE = ORD.ACCOUNT_CODE
AND A.SECUR_CODE = ORD.SECUR_CODE
WHERE CONSOLIDATION_ID = @DEAL_CONTROL_ID
AND D.CANCELLED_DATE is null

union ALL

SELECT DISTINCT A.DEAL_ID,A.DEAL_CONTROL_ID,ORIGINAL_NOMINAL,CURRENT_NOMINAL,TRADED_NOMINAL,SHORT_TITLE,A.ACCOUNT_CODE,'',F.MANAGERS_NOTES,SECURITY_NAME,A.SECUR_CODE,A.BROKER,LIMIT_PRICE,A.ACTION, C.SETTLEMENT_REFERENCE_CODE, F.INITIATION_ID, E.RELEASED_DATE, B.Currency_code, ORD.PRICE_ORDERED
FROM ST_DEAL_DETAIL_BOND A INNER JOIN ST_SECURITIES_BONDS B ON A.SECUR_CODE = B.SECUR_CODE
INNER JOIN ST_ACCOUNT_MAIN C ON A.ACCOUNT_CODE = C.ACCOUNT_CODE
INNER JOIN ST_CONSOLIDATE_COMPONENTS D ON A.DEAL_ID = D.DEAL_ID AND A.DEAL_CONTROL_ID = D.DEAL_CONTROL_ID
INNER JOIN ST_DEAL_CONTROL E ON D.DEAL_CONTROL_ID = E.DEAL_CONTROL_ID
INNER JOIN ST_ORDER_CONTROL F ON E.INITIATION_ID = F.INITIATION_ID
INNER JOIN ST_RECOMMENDATION_CONTROL REC ON F.INITIATION_ID = REC.INITIATION_ID
INNER JOIN ST_ORDER_DETAILS_BOND ORD ON REC.RECOMMENDATION_ID = ORD.RECOMMENDATION_ID
AND A.ACCOUNT_CODE = ORD.ACCOUNT_CODE
AND A.SECUR_CODE = ORD.SECUR_CODE
WHERE CONSOLIDATION_ID = @DEAL_CONTROL_ID
AND D.CANCELLED_DATE is null

union ALL

SELECT DISTINCT A.DEAL_ID,A.DEAL_CONTROL_ID,ORIGINAL_NOMINAL,CURRENT_NOMINAL,TRADED_NOMINAL,SHORT_TITLE,A.ACCOUNT_CODE,'',F.MANAGERS_NOTES,SECURITY_NAME,A.SECUR_CODE,A.BROKER,LIMIT_PRICE,A.ACTION , C.SETTLEMENT_REFERENCE_CODE, F.INITIATION_ID, E.RELEASED_DATE, C.DISPLAY_CURRENCY, ORD.PRICE_ORDERED
FROM ST_DEAL_DETAIL_OPTION A INNER JOIN ST_SECURITIES_OPTIONS B ON A.SECUR_CODE = B.SECUR_CODE
INNER JOIN ST_ACCOUNT_MAIN C ON A.ACCOUNT_CODE = C.ACCOUNT_CODE
INNER JOIN ST_CONSOLIDATE_COMPONENTS D ON A.DEAL_ID = D.DEAL_ID AND A.DEAL_CONTROL_ID = D.DEAL_CONTROL_ID
INNER JOIN ST_DEAL_CONTROL E ON D.DEAL_CONTROL_ID = E.DEAL_CONTROL_ID
INNER JOIN ST_ORDER_CONTROL F ON E.INITIATION_ID = F.INITIATION_ID
INNER JOIN ST_RECOMMENDATION_CONTROL REC ON F.INITIATION_ID = REC.INITIATION_ID
INNER JOIN ST_ORDER_DETAILS_OPTION ORD ON REC.RECOMMENDATION_ID = ORD.RECOMMENDATION_ID
AND A.ACCOUNT_CODE = ORD.ACCOUNT_CODE
AND A.SECUR_CODE = ORD.SECUR_CODE
WHERE CONSOLIDATION_ID = @DEAL_CONTROL_ID
AND D.CANCELLED_DATE is null

union ALL

SELECT DISTINCT A.DEAL_ID,A.DEAL_CONTROL_ID,ORIGINAL_NOMINAL,CURRENT_NOMINAL,TRADED_NOMINAL,SHORT_TITLE,A.ACCOUNT_CODE,'',F.MANAGERS_NOTES,SECURITY_NAME,A.SECUR_CODE,A.BROKER,LIMIT_PRICE,A.ACTION , C.SETTLEMENT_REFERENCE_CODE, F.INITIATION_ID, E.RELEASED_DATE, C.BASE_CURRENCY, ORD.PRICE_ORDERED
FROM ST_DEAL_DETAIL_FUTURE A INNER JOIN ST_SECURITIES_FUTURES B ON A.SECUR_CODE = B.SECUR_CODE
INNER JOIN ST_ACCOUNT_MAIN C ON A.ACCOUNT_CODE = C.ACCOUNT_CODE
INNER JOIN ST_CONSOLIDATE_COMPONENTS D ON A.DEAL_ID = D.DEAL_ID AND A.DEAL_CONTROL_ID = D.DEAL_CONTROL_ID
INNER JOIN ST_DEAL_CONTROL E ON D.DEAL_CONTROL_ID = E.DEAL_CONTROL_ID
INNER JOIN ST_ORDER_CONTROL F ON E.INITIATION_ID = F.INITIATION_ID
INNER JOIN ST_RECOMMENDATION_CONTROL REC ON F.INITIATION_ID = REC.INITIATION_ID
INNER JOIN ST_ORDER_DETAILS_FUTURE ORD ON REC.RECOMMENDATION_ID = ORD.RECOMMENDATION_ID
AND A.ACCOUNT_CODE = ORD.ACCOUNT_CODE
AND A.SECUR_CODE = ORD.SECUR_CODE
WHERE CONSOLIDATION_ID = @DEAL_CONTROL_ID
AND D.CANCELLED_DATE is null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-15 : 18:08:24

Please post the DDL for all tables involved. Please include primary keys and indexes when you script them out.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources