SQL Server Performance

Transactions/Sec

Discussion in 'Getting Started' started by avmreddy17, Jul 1, 2007.

  1. avmreddy17 New Member

    I have a table with around 240 columns and one of the column in the Table is the Inserttime ( DATETIME ) and I using a GETDATE() function in the stored Proc, when we insert data into the table. In the same Milli second 2007-06-27 09:32:58.303 , I have around 7600 records in the database. The Stored Proc is called for each Individual record and we don't bunch the transactions. Is this possible.

    I did some bench marking on this server and I can insert only 700 - 800 records approx / sec on this particular table.

    Thanks
  2. FrankKalis Moderator

    The DATETIME data type has a resolution of 3,33 milliseconds. If more than 1 row is inserted within this timeframe, all these rows will have the same timeframe.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  3. avmreddy17 New Member

    Franks... Thanks so much for u're reply.<br /><br />Here is the total Scenario.... Seeing the below table structure what do you think is the good way to measure how many Inserts were done per second in the below table <br /><br />1. We are inserting row by row. ( using the Stored Proc )<br />2. The table has the default Value ( GETDATE() )on the InsUpdtime Column.<br />3. Table is CtOrders and Stored Proc is usp_InsUpdCTOrders.<br />4. There is a Trigger on the CTOrders table to move the data to CTOrdershistory table for any Updates.<br /><br />Here is the table Structure and the Stored Proc<br /><br /><br />CREATE TABLE [dbo].[CTOrders] (<br />[Account] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[AccountType] [int] NULL ,<br />[AncestorOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[AvgPx] [numeric](15, 6) NULL ,<br />[BookingUnit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ChainOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ClOrdID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ClOrdLinkID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Commission] [numeric](15, 6) NULL ,<br />[CommissionCurrency] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[CommissionType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ComplianceID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ContractAmtCurreny] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ContractAmtType] [int] NULL ,<br />[ContractAmtValue] [numeric](15, 6) NULL ,<br />[ContractMultiplier] [numeric](15, 6) NULL ,<br />[CoveredorUncovered] [int] NULL ,<br />[CumQty] [int] NULL ,<br />[Currency] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[CustomerOrderCapacity] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DayAvgPx] [numeric](15, 6) NULL ,<br />[DayBookingInst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DayCumQty] [int] NULL ,<br />[DeliverToCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Designation] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DeskID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DiscretionFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DiscretionInst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DiscretionOffset] [numeric](15, 6) NULL ,<br />[EffectiveDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ExDestination] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ExecInst] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ExpireDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ExpireTime] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[FirmID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[FirmOrderCapacity] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ForexReq] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[HandlInst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[LeavesQty] [int] NULL ,<br />[LocateReqd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[LocationReference] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MaturityDay] [int] NULL ,<br />[MaturityMonthYear] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MaxFloor] [int] NULL ,<br />[MaxShow] [int] NULL ,<br />[MinQty] [int] NULL ,<br />[OnBehalfOfCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />[OrderQty] [int] NULL ,<br />[OrderQty2] [int] NULL ,<br />[OrderRestrictions] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />[OrdStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrdType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrigClOrdID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ParentOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[PegOffset] [numeric](15, 6) NULL ,<br />[PrevClosePx] [numeric](15, 6) NULL ,<br />[Price] [numeric](15, 6) NULL ,<br />[Price2] [numeric](15, 6) NULL ,<br />[PriceType] [int] NULL ,<br />[PositionEffect] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ProcessCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[PutorCall] [int] NULL ,<br />[QtyOnStreet] [int] NULL ,<br />[QtyType] [int] NULL ,<br />[RegistID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ReleasePx] [numeric](15, 6) NULL ,<br />[ReleaseQty] [int] NULL ,<br />[RouteID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SecondaryClOrdID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SecondaryOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SecurityID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SecurityIDSource] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SecurityType] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SenderCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SenderLocationID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SenderSubID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SettleCurrency] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SettleDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SettleDate2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SettleType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Side] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SolicitedFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[StopPx] [numeric](15, 6) NULL ,<br />[StrikePrice] [numeric](15, 6) NULL ,<br />[Suffix] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Symbol] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TargetCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TargetLocationID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TargetSubID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TIF] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TraderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TradeDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TransactionTime] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[UncommittedQty] [numeric](15, 6) NULL ,<br />[UpdateTime] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Version] [int] NULL ,<br />[InsUpdTime] [datetime] NULL ,<br />[AskPx] [numeric](15, 6) NULL ,<br />[AttachedProps] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[BasketID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[BidPx] [numeric](15, 6) NULL ,<br />[CancelRequestID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[CFICode] [varchar] (<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DefaultSystem] [int] NULL ,<br />[ExcludeSystems] [int] NULL ,<br />[ExplantionText] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[LastUpdatedBy] [varchar] (<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ManualAckRequired] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MultiLegID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrderAttribute] [int] NULL ,<br />[OrderGroupID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrderID2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrdRejReason] [int] NULL ,<br />[OrdStatus2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[PegOffsetType] [int] NULL ,<br />[PortfolioID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[RepOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SystemID] [varchar] (<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TickDir] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[UseOrderID2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[LastTrade] [numeric](15, 6) NULL ,<br />[ParentClOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrigOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[GiveUpID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OrderInterrfaceID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OnBehalfOfSubID] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ExternalSymbol] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ExternalSuffix] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Reserved1] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Reserved2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Reserved3] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Reserved4] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Reserved5] [int] NULL ,<br />[Reserved6] [int] NULL ,<br />[ComplianceText] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SpecialInst] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[SetupQty] [int] NULL ,<br />[UnwindQty] [int] NULL<br />) ON [PRIMARY]<br />GO<br /><br />ALTER TABLE [dbo].[CTOrders] WITH NOCHECK ADD<br />CONSTRAINT [PK_CTOrders] PRIMARY KEY CLUSTERED<br />(<br />[OrderID]<br />) ON [PRIMARY]<br />GO<br /><br />ALTER TABLE [dbo].[CTOrders] ADD<br />CONSTRAINT [DF_CTOrders_InsUpdTime] DEFAULT (getdate()) FOR [InsUpdTime]<br />GO<br /><br />Here is the Stored Proc<br /><br />CREATE PROC [dbo].usp_InsUpdCTOrders<br />@psAccount VARCHAR(32) ,<br />@piAccountType INT ,<br />@psAncestorOrderID VARCHAR(32) ,<br />@pnAvgPx NUMERIC(15,6) ,<br />@psBookingUnit CHAR(1) ,<br />@psChainOrderID VARCHAR(32) ,<br />@psClOrdID VARCHAR(32) ,<br />@psClOrdLinkID VARCHAR(32) ,<br />@pnCommission NUMERIC(15,6) ,<br />@psCommissionCurrency VARCHAR(32) ,<br />@psCommissionType CHAR(1) ,<br />@psComplianceID VARCHAR(32) ,<br />@psContractAmtCurreny VARCHAR(32) ,<br />@piContractAmtType INT ,<br />@pnContractAmtValue NUMERIC(15,6) ,<br />@pnContractMultiplier NUMERIC(15,6) ,<br />@piCoveredorUncovered INT ,<br />@piCumQty INT ,<br />@psCurrency VARCHAR(32) ,<br />@psCustomerOrderCapacity CHAR(1) ,<br />@pnDayAvgPx NUMERIC(15,6) ,<br />@psDayBookingInst CHAR(1) ,<br />@piDayCumQty INT ,<br />@psDeliverToCompID VARCHAR(32) ,<br />@psDesignation VARCHAR(32) ,<br />@psDeskID VARCHAR(32) ,<br />@psDiscretionFlag CHAR(1) ,<br />@psDiscretionInst CHAR(1) ,<br />@pnDiscretionOffset NUMERIC(15,6) ,<br />@psEffectiveDate VARCHAR(32) ,<br />@psExDestination VARCHAR(32) ,<br />@psExecInst VARCHAR(32) ,<br />@psExpireDate VARCHAR(32) ,<br />@psExpireTime VARCHAR(32) ,<br />@psFirmID VARCHAR(32) ,<br />@psFirmOrderCapacity CHAR(1) ,<br />@psForexReq CHAR(1) ,<br />@psHandlInst CHAR(1) ,<br />@piLeavesQty INT ,<br />@psLocateReqd CHAR(1) ,<br />@psLocationReference VARCHAR(32) ,<br />@piMaturityDay INT ,<br />@psMaturityMonthYear VARCHAR(32) ,<br />@piMaxFloor INT ,<br />@piMaxShow INT ,<br />@piMinQty INT ,<br />@psOnBehalfOfCompID VARCHAR(32) ,<br />@psOrderID VARCHAR(32) ,<br />@piOrderQty INT ,<br />@piOrderQty2 INT ,<br />@psOrderRestrictions VARCHAR(32) ,<br />@psOrdStatus CHAR(1) ,<br />@psOrdType CHAR(1) ,<br />@psOrigClOrdID VARCHAR(32) ,<br />@psParentOrderID VARCHAR(32) ,<br />@pnPegOffset NUMERIC(15,6) ,<br />@pnPrevClosePx NUMERIC(15,6) ,<br />@pnPrice NUMERIC(15,6) ,<br />@pnPrice2 NUMERIC(15,6) ,<br />@piPriceType INT ,<br />@psPositionEffect CHAR(1) ,<br />@psProcessCode CHAR(1) ,<br />@piPutorCall INT ,<br />@piQtyOnStreet INT ,<br />@piQtyType INT ,<br />@psRegistID VARCHAR(32) ,<br />@pnReleasePx NUMERIC(15,6) ,<br />@piReleaseQty INT ,<br />@psRouteID VARCHAR(32) ,<br />@psSecondaryClOrdID VARCHAR(32) ,<br />@psSecondaryOrderID VARCHAR(32) ,<br />@psSecurityID VARCHAR(32) ,<br />@psSecurityIDSource VARCHAR(32) ,<br />@psSecurityType VARCHAR(32) ,<br />@psSenderCompID VARCHAR(32) ,<br />@psSenderLocationID VARCHAR(32) ,<br />@psSenderSubID VARCHAR(32) ,<br />@psSettleCurrency VARCHAR(32) ,<br />@psSettleDate VARCHAR(32) ,<br />@psSettleDate2 VARCHAR(32) ,<br />@psSettleType CHAR(1) ,<br />@psSide CHAR(1) ,<br />@psSolicitedFlag CHAR(1) ,<br />@pnStopPx NUMERIC(15,6) ,<br />@pnStrikePrice NUMERIC(15,6) ,<br />@psSuffix VARCHAR(16) ,<br />@psSymbol VARCHAR(16) ,<br />@psTargetCompID VARCHAR(32) ,<br />@psTargetLocationID VARCHAR(32) ,<br />@psTargetSubID VARCHAR(32) ,<br />@psText VARCHAR(256) ,<br />@psTIF CHAR(1) ,<br />@psTraderID VARCHAR(32) ,<br />@psTradeDate VARCHAR(32) ,<br />@psTransactionTime VARCHAR(32) ,<br />@pnUncommittedQty NUMERIC(15,6) ,<br />@psUpdateTime VARCHAR(32) ,<br />@piVersion INT ,<br />@pnAskPx NUMERIC(15,6) ,<br />@psAttachedProps VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> ,<br />@psBasketID VARCHAR(32) ,<br />@pnBidPx NUMERIC(15,6) ,<br />@psCancelRequestID VARCHAR(32) ,<br />@psCFICode VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> ,<br />@piDefaultSystem INT ,<br />@piExcludeSystems INT ,<br />@psExplantionText VARCHAR(64) ,<br />@psLastUpdatedBy VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> ,<br />@psManualAckRequired CHAR(1) ,<br />@psMultiLegID VARCHAR(32) ,<br />@piOrderAttribute INT ,<br />@psOrderGroupID VARCHAR(32) ,<br />@psOrderID2 VARCHAR(32) ,<br />@piOrdRejReason INT ,<br />@psOrdStatus2 CHAR(1) ,<br />@piPegOffsetType INT ,<br />@psPortfolioID VARCHAR(32) ,<br />@psRepOrderID VARCHAR(32) ,<br />@psSystemID VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> ,<br />@psTickDir CHAR(1) ,<br />@psUseOrderID2 CHAR(1) ,<br />@pnLastTrade NUMERIC(15,6) ,<br />@psParentClOrdID VARCHAR(32) ,<br />@psOrigOrderID VARCHAR(32) ,<br />@lsGiveUpID VARCHAR(16) ,<br />@lsOrderInterrfaceID VARCHAR(16) ,<br />@psOnBehalfOfSubID VARCHAR(64) ,<br />@psExternalSymbol VARCHAR(16) ,<br />@psExternalSuffix VARCHAR(16) ,<br />@psReserved1 VARCHAR(32) ,<br />@psReserved2 VARCHAR(32) ,<br />@psReserved3 VARCHAR(32) ,<br />@psReserved4 VARCHAR(32) ,<br />@piReserved5 INT ,<br />@piReserved6 INT<br />AS<br />BEGIN<br />SET NOCOUNT ON<br /><br />IF EXISTS ( SELECT OrderID<br />FROM CTOrders<br />WHERE OrderID = @psOrderID )<br />BEGIN<br /><br /><br />UPDATE CTOrders<br />SET Account = @psAccount ,<br />AccountType = @piAccountType ,<br />AncestorOrderID = @psAncestorOrderID ,<br />AvgPx = @pnAvgPx ,<br />BookingUnit = @psBookingUnit ,<br />ChainOrderID = @psChainOrderID ,<br />ClOrdID = @psClOrdID ,<br />ClOrdLinkID = @psClOrdLinkID ,<br />Commission = @pnCommission ,<br />CommissionCurrency = @psCommissionCurrency ,<br />CommissionType = @psCommissionType ,<br />ComplianceID = @psComplianceID ,<br />ContractAmtCurreny = @psContractAmtCurreny ,<br />ContractAmtType = @piContractAmtType ,<br />ContractAmtValue = @pnContractAmtValue ,<br />ContractMultiplier = @pnContractMultiplier ,<br />CoveredorUncovered = @piCoveredorUncovered ,<br />CumQty = @piCumQty ,<br />Currency = @psCurrency ,<br />CustomerOrderCapacity = @psCustomerOrderCapacity ,<br />DayAvgPx = @pnDayAvgPx ,<br />DayBookingInst = @psDayBookingInst ,<br />DayCumQty = @piDayCumQty ,<br />DeliverToCompID = @psDeliverToCompID ,<br />Designation = @psDesignation ,<br />DeskID = @psDeskID ,<br />DiscretionFlag = @psDiscretionFlag ,<br />DiscretionInst = @psDiscretionInst ,<br />DiscretionOffset = @pnDiscretionOffset ,<br />EffectiveDate = @psEffectiveDate ,<br />ExDestination = @psExDestination ,<br />ExecInst = @psExecInst ,<br />[ExpireDate] = @psExpireDate ,<br />ExpireTime = @psExpireTime ,<br />FirmID = @psFirmID ,<br />FirmOrderCapacity = @psFirmOrderCapacity ,<br />ForexReq = @psForexReq ,<br />HandlInst = @psHandlInst ,<br />LeavesQty = @piLeavesQty ,<br />LocateReqd = @psLocateReqd ,<br />LocationReference = @psLocationReference ,<br />MaturityDay = @piMaturityDay ,<br />MaturityMonthYear = @psMaturityMonthYear ,<br />MaxFloor = @piMaxFloor ,<br />MaxShow = @piMaxShow ,<br />MinQty = @piMinQty ,<br />OnBehalfOfCompID = @psOnBehalfOfCompID ,<br />OrderQty = @piOrderQty ,<br />OrderQty2 = @piOrderQty2 ,<br />OrderRestrictions = @psOrderRestrictions ,<br />OrdStatus = @psOrdStatus ,<br />OrdType = @psOrdType ,<br />OrigClOrdID = @psOrigClOrdID ,<br />ParentOrderID = @psParentOrderID ,<br />PegOffset = @pnPegOffset ,<br />PrevClosePx = @pnPrevClosePx ,<br />Price = @pnPrice ,<br />Price2 = @pnPrice2 ,<br />PriceType = @piPriceType ,<br />PositionEffect = @psPositionEffect ,<br />ProcessCode = @psProcessCode ,<br />PutorCall = @piPutorCall ,<br />QtyOnStreet = @piQtyOnStreet ,<br />QtyType = @piQtyType ,<br />RegistID = @psRegistID ,<br />ReleasePx = @pnReleasePx ,<br />ReleaseQty = @piReleaseQty ,<br />RouteID = @psRouteID ,<br />SecondaryClOrdID = @psSecondaryClOrdID ,<br />SecondaryOrderID = @psSecondaryOrderID ,<br />SecurityID = @psSecurityID ,<br />SecurityIDSource = @psSecurityIDSource ,<br />SecurityType = @psSecurityType ,<br />SenderCompID = @psSenderCompID ,<br />SenderLocationID = @psSenderLocationID ,<br />SenderSubID = @psSenderSubID ,<br />SettleCurrency = @psSettleCurrency ,<br />SettleDate = @psSettleDate ,<br />SettleDate2 = @psSettleDate2 ,<br />SettleType = @psSettleType ,<br />Side = @psSide ,<br />SolicitedFlag = @psSolicitedFlag ,<br />StopPx = @pnStopPx ,<br />StrikePrice = @pnStrikePrice ,<br />Suffix = @psSuffix ,<br />Symbol = @psSymbol ,<br />TargetCompID = @psTargetCompID ,<br />TargetLocationID = @psTargetLocationID ,<br />TargetSubID = @psTargetSubID ,<br />Text = @psText ,<br />TIF = @psTIF ,<br />TraderID = @psTraderID ,<br />TradeDate = @psTradeDate ,<br />TransactionTime = @psTransactionTime ,<br />UncommittedQty = @pnUncommittedQty ,<br />UpdateTime = @psUpdateTime ,<br />Version = @piVersion ,<br />AskPx = @pnAskPx ,<br />AttachedProps = @psAttachedProps ,<br />BasketID = @psBasketID ,<br />BidPx = @pnBidPx ,<br />CancelRequestID = @psCancelRequestID ,<br />CFICode = @psCFICode ,<br />DefaultSystem = @piDefaultSystem ,<br />ExcludeSystems = @piExcludeSystems ,<br />ExplantionText = @psExplantionText ,<br />LastUpdatedBy = @psLastUpdatedBy ,<br />ManualAckRequired = @psManualAckRequired ,<br />MultiLegID = @psMultiLegID ,<br />OrderAttribute = @piOrderAttribute ,<br />OrderGroupID = @psOrderGroupID ,<br />OrderID2 = @psOrderID2 ,<br />OrdRejReason = @piOrdRejReason ,<br />OrdStatus2 = @psOrdStatus2 ,<br />PegOffsetType = @piPegOffsetType ,<br />PortfolioID = @psPortfolioID ,<br />RepOrderID = @psRepOrderID ,<br />SystemID = @psSystemID ,<br />TickDir = @psTickDir ,<br />UseOrderID2 = @psUseOrderID2 ,<br />LastTrade = @pnLastTrade ,<br />ParentClOrderID = @psParentClOrdID ,<br />OrigOrderID = @psOrigOrderID ,<br />GiveUpID = @lsGiveUpID ,<br />OrderInterrfaceID = @lsOrderInterrfaceID ,<br />OnBehalfOfSubID = @psOnBehalfOfSubID ,<br />ExternalSymbol = @psExternalSymbol ,<br />ExternalSuffix = @psExternalSuffix ,<br />Reserved1 = @psReserved1 ,<br />Reserved2 = @psReserved2 ,<br />Reserved3 = @psReserved3 ,<br />Reserved4 = @psReserved4 ,<br />Reserved5 = @piReserved5 ,<br />Reserved6 = @piReserved6<br />WHERE OrderID = @psOrderID<br /><br />END<br />ELSE<br />BEGIN<br /><br /><br />INSERT INTO CTOrders<br />(<br />Account ,<br />AccountType ,<br />AncestorOrderID ,<br />AvgPx ,<br />BookingUnit ,<br />ChainOrderID ,<br />ClOrdID ,<br />ClOrdLinkID ,<br />Commission ,<br />CommissionCurrency ,<br />CommissionType ,<br />ComplianceID ,<br />ContractAmtCurreny ,<br />ContractAmtType ,<br />ContractAmtValue ,<br />ContractMultiplier ,<br />CoveredorUncovered ,<br />CumQty ,<br />Currency ,<br />CustomerOrderCapacity ,<br />DayAvgPx ,<br />DayBookingInst ,<br />DayCumQty ,<br />DeliverToCompID ,<br />Designation ,<br />DeskID ,<br />DiscretionFlag ,<br />DiscretionInst ,<br />DiscretionOffset ,<br />EffectiveDate ,<br />ExDestination ,<br />ExecInst ,<br />ExpireDate ,<br />ExpireTime ,<br />FirmID ,<br />FirmOrderCapacity ,<br />ForexReq ,<br />HandlInst ,<br />LeavesQty ,<br />LocateReqd ,<br />LocationReference ,<br />MaturityDay ,<br />MaturityMonthYear ,<br />MaxFloor ,<br />MaxShow ,<br />MinQty ,<br />OnBehalfOfCompID ,<br />OrderID ,<br />OrderQty ,<br />OrderQty2 ,<br />OrderRestrictions ,<br />OrdStatus ,<br />OrdType ,<br />OrigClOrdID ,<br />ParentOrderID ,<br />PegOffset ,<br />PrevClosePx ,<br />Price ,<br />Price2 ,<br />PriceType ,<br />PositionEffect ,<br />ProcessCode ,<br />PutorCall ,<br />QtyOnStreet ,<br />QtyType ,<br />RegistID ,<br />ReleasePx ,<br />ReleaseQty ,<br />RouteID ,<br />SecondaryClOrdID ,<br />SecondaryOrderID ,<br />SecurityID ,<br />SecurityIDSource ,<br />SecurityType ,<br />SenderCompID ,<br />SenderLocationID ,<br />SenderSubID ,<br />SettleCurrency ,<br />SettleDate ,<br />SettleDate2 ,<br />SettleType ,<br />Side ,<br />SolicitedFlag ,<br />StopPx ,<br />StrikePrice ,<br />Suffix ,<br />Symbol ,<br />TargetCompID ,<br />TargetLocationID ,<br />TargetSubID ,<br />Text ,<br />TIF ,<br />TraderID ,<br />TradeDate ,<br />TransactionTime ,<br />UncommittedQty ,<br />UpdateTime ,<br />Version ,<br />AskPx ,<br />AttachedProps ,<br />BasketID ,<br />BidPx ,<br />CancelRequestID ,<br />CFICode ,<br />DefaultSystem ,<br />ExcludeSystems ,<br />ExplantionText ,<br />LastUpdatedBy ,<br />ManualAckRequired ,<br />MultiLegID ,<br />OrderAttribute ,<br />OrderGroupID ,<br />OrderID2 ,<br />OrdRejReason ,<br />OrdStatus2 ,<br />PegOffsetType ,<br />PortfolioID ,<br />RepOrderID ,<br />SystemID ,<br />TickDir ,<br />UseOrderID2 ,<br />LastTrade ,<br />ParentClOrderID ,<br />OrigOrderID ,<br />GiveUpID ,<br />OrderInterrfaceID ,<br />OnBehalfOfSubID ,<br />ExternalSymbol ,<br />ExternalSuffix ,<br />Reserved1 ,<br />Reserved2 ,<br />Reserved3 ,<br />Reserved4 ,<br />Reserved5 ,<br />Reserved6<br />)<br />VALUES<br />(<br />@psAccount ,<br />@piAccountType ,<br />@psAncestorOrderID ,<br />@pnAvgPx ,<br />@psBookingUnit ,<br />@psChainOrderID ,<br />@psClOrdID ,<br />@psClOrdLinkID ,<br />@pnCommission ,<br />@psCommissionCurrency ,<br />@psCommissionType ,<br />@psComplianceID ,<br />@psContractAmtCurreny ,<br />@piContractAmtType ,<br />@pnContractAmtValue ,<br />@pnContractMultiplier ,<br />@piCoveredorUncovered ,<br />@piCumQty ,<br />@psCurrency ,<br />@psCustomerOrderCapacity ,<br />@pnDayAvgPx ,<br />@psDayBookingInst ,<br />@piDayCumQty ,<br />@psDeliverToCompID ,<br />@psDesignation ,<br />@psDeskID ,<br />@psDiscretionFlag ,<br />@psDiscretionInst ,<br />@pnDiscretionOffset ,<br />@psEffectiveDate ,<br />@psExDestination ,<br />@psExecInst ,<br />@psExpireDate ,<br />@psExpireTime ,<br />@psFirmID ,<br />@psFirmOrderCapacity ,<br />@psForexReq ,<br />@psHandlInst ,<br />@piLeavesQty ,<br />@psLocateReqd ,<br />@psLocationReference ,<br />@piMaturityDay ,<br />@psMaturityMonthYear ,<br />@piMaxFloor ,<br />@piMaxShow ,<br />@piMinQty ,<br />@psOnBehalfOfCompID ,<br />@psOrderID ,<br />@piOrderQty ,<br />@piOrderQty2 ,<br />@psOrderRestrictions ,<br />@psOrdStatus ,<br />@psOrdType ,<br />@psOrigClOrdID ,<br />@psParentOrderID ,<br />@pnPegOffset ,<br />@pnPrevClosePx ,<br />@pnPrice ,<br />@pnPrice2 ,<br />@piPriceType ,<br />@psPositionEffect ,<br />@psProcessCode ,<br />@piPutorCall ,<br />@piQtyOnStreet ,<br />@piQtyType ,<br />@psRegistID ,<br />@pnReleasePx ,<br />@piReleaseQty ,<br />@psRouteID ,<br />@psSecondaryClOrdID ,<br />@psSecondaryOrderID ,<br />@psSecurityID ,<br />@psSecurityIDSource ,<br />@psSecurityType ,<br />@psSenderCompID ,<br />@psSenderLocationID ,<br />@psSenderSubID ,<br />@psSettleCurrency ,<br />@psSettleDate ,<br />@psSettleDate2 ,<br />@psSettleType ,<br />@psSide ,<br />@psSolicitedFlag ,<br />@pnStopPx ,<br />@pnStrikePrice ,<br />@psSuffix ,<br />@psSymbol ,<br />@psTargetCompID ,<br />@psTargetLocationID ,<br />@psTargetSubID ,<br />@psText ,<br />@psTIF ,<br />@psTraderID ,<br />@psTradeDate ,<br />@psTransactionTime ,<br />@pnUncommittedQty ,<br />@psUpdateTime ,<br />@piVersion ,<br />@pnAskPx ,<br />@psAttachedProps ,<br />@psBasketID ,<br />@pnBidPx ,<br />@psCancelRequestID ,<br />@psCFICode ,<br />@piDefaultSystem ,<br />@piExcludeSystems ,<br />@psExplantionText ,<br />@psLastUpdatedBy ,<br />@psManualAckRequired ,<br />@psMultiLegID ,<br />@piOrderAttribute ,<br />@psOrderGroupID ,<br />@psOrderID2 ,<br />@piOrdRejReason ,<br />@psOrdStatus2 ,<br />@piPegOffsetType ,<br />@psPortfolioID ,<br />@psRepOrderID ,<br />@psSystemID ,<br />@psTickDir ,<br />@psUseOrderID2 ,<br />@pnLastTrade ,<br />@psParentClOrdID ,<br />@psOrigOrderID ,<br />@lsGiveUpID ,<br />@lsOrderInterrfaceID ,<br />@psOnBehalfOfSubID ,<br />@psExternalSymbol ,<br />@psExternalSuffix ,<br />@psReserved1 ,<br />@psReserved2 ,<br />@psReserved3 ,<br />@psReserved4 ,<br />@piReserved5 ,<br />@piReserved6<br />)<br /><br />END<br /><br />END<br />
  4. satya Moderator

    Are extracting from CTOrders table or CTOrdershistory table for

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. avmreddy17 New Member

    Can you please re-phrase the Question?.
  6. satya Moderator

    I mean to say whether you need such values from CTOrders or CTOrderHistory?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page