Transactions/Sec - 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

General SQL Server Forums

New to SQL Server Programming

Transactions/Sec

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-07-01 : 10:16:52

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-01 : 10:25:19

can you show us your insert query?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-07-01 : 10:30:35

The datetime datatype only has a resolution of 3.33 ms. But as the previous poster said can you post your code?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-01 : 12:11:25

suspect you aren't inserting row by row

insert tbl
select ...., getdate()
from tbl2

all these rows will have the same date.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-07-01 : 16:23:00

1. We are inserting row by row. ( using the Stored Proc )
2. The table has the default Value ( GETDATE() )on the InsUpdtime Column.
3. Table is CtOrders and Stored Proc is usp_InsUpdCTOrders.
4. There is a Trigger on the CTOrders table to move the data to CTOrdershistory table for any Updates.

Here is the table Structure and the Stored Proc

CREATE TABLE [dbo].[CTOrders] (
[Account] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountType] [int] NULL ,
[AncestorOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AvgPx] [numeric](15, 6) NULL ,
[BookingUnit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChainOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClOrdID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClOrdLinkID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Commission] [numeric](15, 6) NULL ,
[CommissionCurrency] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommissionType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ComplianceID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContractAmtCurreny] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContractAmtType] [int] NULL ,
[ContractAmtValue] [numeric](15, 6) NULL ,
[ContractMultiplier] [numeric](15, 6) NULL ,
[CoveredorUncovered] [int] NULL ,
[CumQty] [int] NULL ,
[Currency] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerOrderCapacity] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DayAvgPx] [numeric](15, 6) NULL ,
[DayBookingInst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DayCumQty] [int] NULL ,
[DeliverToCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Designation] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeskID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiscretionFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiscretionInst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiscretionOffset] [numeric](15, 6) NULL ,
[EffectiveDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExDestination] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExecInst] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExpireDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExpireTime] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirmID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirmOrderCapacity] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ForexReq] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HandlInst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LeavesQty] [int] NULL ,
[LocateReqd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LocationReference] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaturityDay] [int] NULL ,
[MaturityMonthYear] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaxFloor] [int] NULL ,
[MaxShow] [int] NULL ,
[MinQty] [int] NULL ,
[OnBehalfOfCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OrderQty] [int] NULL ,
[OrderQty2] [int] NULL ,
[OrderRestrictions] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OrdStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrdType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrigClOrdID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ParentOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PegOffset] [numeric](15, 6) NULL ,
[PrevClosePx] [numeric](15, 6) NULL ,
[Price] [numeric](15, 6) NULL ,
[Price2] [numeric](15, 6) NULL ,
[PriceType] [int] NULL ,
[PositionEffect] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcessCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PutorCall] [int] NULL ,
[QtyOnStreet] [int] NULL ,
[QtyType] [int] NULL ,
[RegistID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReleasePx] [numeric](15, 6) NULL ,
[ReleaseQty] [int] NULL ,
[RouteID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryClOrdID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecurityID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecurityIDSource] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecurityType] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderLocationID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderSubID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettleCurrency] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettleDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettleDate2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettleType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Side] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SolicitedFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StopPx] [numeric](15, 6) NULL ,
[StrikePrice] [numeric](15, 6) NULL ,
[Suffix] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Symbol] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TargetCompID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TargetLocationID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TargetSubID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIF] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TraderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionTime] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UncommittedQty] [numeric](15, 6) NULL ,
[UpdateTime] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Version] [int] NULL ,
[InsUpdTime] [datetime] NULL ,
[AskPx] [numeric](15, 6) NULL ,
[AttachedProps] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BasketID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BidPx] [numeric](15, 6) NULL ,
[CancelRequestID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CFICode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultSystem] [int] NULL ,
[ExcludeSystems] [int] NULL ,
[ExplantionText] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdatedBy] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManualAckRequired] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MultiLegID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderAttribute] [int] NULL ,
[OrderGroupID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderID2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrdRejReason] [int] NULL ,
[OrdStatus2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PegOffsetType] [int] NULL ,
[PortfolioID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RepOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TickDir] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UseOrderID2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastTrade] [numeric](15, 6) NULL ,
[ParentClOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrigOrderID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GiveUpID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderInterrfaceID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OnBehalfOfSubID] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExternalSymbol] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExternalSuffix] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved1] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved3] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved4] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved5] [int] NULL ,
[Reserved6] [int] NULL ,
[ComplianceText] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpecialInst] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SetupQty] [int] NULL ,
[UnwindQty] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CTOrders] WITH NOCHECK ADD
CONSTRAINT [PK_CTOrders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CTOrders] ADD
CONSTRAINT [DF_CTOrders_InsUpdTime] DEFAULT (getdate()) FOR [InsUpdTime]
GO

Here is the Stored Proc

CREATE PROC [dbo].usp_InsUpdCTOrders
@psAccount VARCHAR(32) ,
@piAccountType INT ,
@psAncestorOrderID VARCHAR(32) ,
@pnAvgPx NUMERIC(15,6) ,
@psBookingUnit CHAR(1) ,
@psChainOrderID VARCHAR(32) ,
@psClOrdID VARCHAR(32) ,
@psClOrdLinkID VARCHAR(32) ,
@pnCommission NUMERIC(15,6) ,
@psCommissionCurrency VARCHAR(32) ,
@psCommissionType CHAR(1) ,
@psComplianceID VARCHAR(32) ,
@psContractAmtCurreny VARCHAR(32) ,
@piContractAmtType INT ,
@pnContractAmtValue NUMERIC(15,6) ,
@pnContractMultiplier NUMERIC(15,6) ,
@piCoveredorUncovered INT ,
@piCumQty INT ,
@psCurrency VARCHAR(32) ,
@psCustomerOrderCapacity CHAR(1) ,
@pnDayAvgPx NUMERIC(15,6) ,
@psDayBookingInst CHAR(1) ,
@piDayCumQty INT ,
@psDeliverToCompID VARCHAR(32) ,
@psDesignation VARCHAR(32) ,
@psDeskID VARCHAR(32) ,
@psDiscretionFlag CHAR(1) ,
@psDiscretionInst CHAR(1) ,
@pnDiscretionOffset NUMERIC(15,6) ,
@psEffectiveDate VARCHAR(32) ,
@psExDestination VARCHAR(32) ,
@psExecInst VARCHAR(32) ,
@psExpireDate VARCHAR(32) ,
@psExpireTime VARCHAR(32) ,
@psFirmID VARCHAR(32) ,
@psFirmOrderCapacity CHAR(1) ,
@psForexReq CHAR(1) ,
@psHandlInst CHAR(1) ,
@piLeavesQty INT ,
@psLocateReqd CHAR(1) ,
@psLocationReference VARCHAR(32) ,
@piMaturityDay INT ,
@psMaturityMonthYear VARCHAR(32) ,
@piMaxFloor INT ,
@piMaxShow INT ,
@piMinQty INT ,
@psOnBehalfOfCompID VARCHAR(32) ,
@psOrderID VARCHAR(32) ,
@piOrderQty INT ,
@piOrderQty2 INT ,
@psOrderRestrictions VARCHAR(32) ,
@psOrdStatus CHAR(1) ,
@psOrdType CHAR(1) ,
@psOrigClOrdID VARCHAR(32) ,
@psParentOrderID VARCHAR(32) ,
@pnPegOffset NUMERIC(15,6) ,
@pnPrevClosePx NUMERIC(15,6) ,
@pnPrice NUMERIC(15,6) ,
@pnPrice2 NUMERIC(15,6) ,
@piPriceType INT ,
@psPositionEffect CHAR(1) ,
@psProcessCode CHAR(1) ,
@piPutorCall INT ,
@piQtyOnStreet INT ,
@piQtyType INT ,
@psRegistID VARCHAR(32) ,
@pnReleasePx NUMERIC(15,6) ,
@piReleaseQty INT ,
@psRouteID VARCHAR(32) ,
@psSecondaryClOrdID VARCHAR(32) ,
@psSecondaryOrderID VARCHAR(32) ,
@psSecurityID VARCHAR(32) ,
@psSecurityIDSource VARCHAR(32) ,
@psSecurityType VARCHAR(32) ,
@psSenderCompID VARCHAR(32) ,
@psSenderLocationID VARCHAR(32) ,
@psSenderSubID VARCHAR(32) ,
@psSettleCurrency VARCHAR(32) ,
@psSettleDate VARCHAR(32) ,
@psSettleDate2 VARCHAR(32) ,
@psSettleType CHAR(1) ,
@psSide CHAR(1) ,
@psSolicitedFlag CHAR(1) ,
@pnStopPx NUMERIC(15,6) ,
@pnStrikePrice NUMERIC(15,6) ,
@psSuffix VARCHAR(16) ,
@psSymbol VARCHAR(16) ,
@psTargetCompID VARCHAR(32) ,
@psTargetLocationID VARCHAR(32) ,
@psTargetSubID VARCHAR(32) ,
@psText VARCHAR(256) ,
@psTIF CHAR(1) ,
@psTraderID VARCHAR(32) ,
@psTradeDate VARCHAR(32) ,
@psTransactionTime VARCHAR(32) ,
@pnUncommittedQty NUMERIC(15,6) ,
@psUpdateTime VARCHAR(32) ,
@piVersion INT ,
@pnAskPx NUMERIC(15,6) ,
@psAttachedProps VARCHAR(128) ,
@psBasketID VARCHAR(32) ,
@pnBidPx NUMERIC(15,6) ,
@psCancelRequestID VARCHAR(32) ,
@psCFICode VARCHAR(8) ,
@piDefaultSystem INT ,
@piExcludeSystems INT ,
@psExplantionText VARCHAR(64) ,
@psLastUpdatedBy VARCHAR(8) ,
@psManualAckRequired CHAR(1) ,
@psMultiLegID VARCHAR(32) ,
@piOrderAttribute INT ,
@psOrderGroupID VARCHAR(32) ,
@psOrderID2 VARCHAR(32) ,
@piOrdRejReason INT ,
@psOrdStatus2 CHAR(1) ,
@piPegOffsetType INT ,
@psPortfolioID VARCHAR(32) ,
@psRepOrderID VARCHAR(32) ,
@psSystemID VARCHAR(8) ,
@psTickDir CHAR(1) ,
@psUseOrderID2 CHAR(1) ,
@pnLastTrade NUMERIC(15,6) ,
@psParentClOrdID VARCHAR(32) ,
@psOrigOrderID VARCHAR(32) ,
@lsGiveUpID VARCHAR(16) ,
@lsOrderInterrfaceID VARCHAR(16) ,
@psOnBehalfOfSubID VARCHAR(64) ,
@psExternalSymbol VARCHAR(16) ,
@psExternalSuffix VARCHAR(16) ,
@psReserved1 VARCHAR(32) ,
@psReserved2 VARCHAR(32) ,
@psReserved3 VARCHAR(32) ,
@psReserved4 VARCHAR(32) ,
@piReserved5 INT ,
@piReserved6 INT
AS
BEGIN
SET NOCOUNT ON

IF EXISTS ( SELECT OrderID
FROM CTOrders
WHERE OrderID = @psOrderID )
BEGIN

UPDATE CTOrders
SET Account = @psAccount ,
AccountType = @piAccountType ,
AncestorOrderID = @psAncestorOrderID ,
AvgPx = @pnAvgPx ,
BookingUnit = @psBookingUnit ,
ChainOrderID = @psChainOrderID ,
ClOrdID = @psClOrdID ,
ClOrdLinkID = @psClOrdLinkID ,
Commission = @pnCommission ,
CommissionCurrency = @psCommissionCurrency ,
CommissionType = @psCommissionType ,
ComplianceID = @psComplianceID ,
ContractAmtCurreny = @psContractAmtCurreny ,
ContractAmtType = @piContractAmtType ,
ContractAmtValue = @pnContractAmtValue ,
ContractMultiplier = @pnContractMultiplier ,
CoveredorUncovered = @piCoveredorUncovered ,
CumQty = @piCumQty ,
Currency = @psCurrency ,
CustomerOrderCapacity = @psCustomerOrderCapacity ,
DayAvgPx = @pnDayAvgPx ,
DayBookingInst = @psDayBookingInst ,
DayCumQty = @piDayCumQty ,
DeliverToCompID = @psDeliverToCompID ,
Designation = @psDesignation ,
DeskID = @psDeskID ,
DiscretionFlag = @psDiscretionFlag ,
DiscretionInst = @psDiscretionInst ,
DiscretionOffset = @pnDiscretionOffset ,
EffectiveDate = @psEffectiveDate ,
ExDestination = @psExDestination ,
ExecInst = @psExecInst ,
[ExpireDate] = @psExpireDate ,
ExpireTime = @psExpireTime ,
FirmID = @psFirmID ,
FirmOrderCapacity = @psFirmOrderCapacity ,
ForexReq = @psForexReq ,
HandlInst = @psHandlInst ,
LeavesQty = @piLeavesQty ,
LocateReqd = @psLocateReqd ,
LocationReference = @psLocationReference ,
MaturityDay = @piMaturityDay ,
MaturityMonthYear = @psMaturityMonthYear ,
MaxFloor = @piMaxFloor ,
MaxShow = @piMaxShow ,
MinQty = @piMinQty ,
OnBehalfOfCompID = @psOnBehalfOfCompID ,
OrderQty = @piOrderQty ,
OrderQty2 = @piOrderQty2 ,
OrderRestrictions = @psOrderRestrictions ,
OrdStatus = @psOrdStatus ,
OrdType = @psOrdType ,
OrigClOrdID = @psOrigClOrdID ,
ParentOrderID = @psParentOrderID ,
PegOffset = @pnPegOffset ,
PrevClosePx = @pnPrevClosePx ,
Price = @pnPrice ,
Price2 = @pnPrice2 ,
PriceType = @piPriceType ,
PositionEffect = @psPositionEffect ,
ProcessCode = @psProcessCode ,
PutorCall = @piPutorCall ,
QtyOnStreet = @piQtyOnStreet ,
QtyType = @piQtyType ,
RegistID = @psRegistID ,
ReleasePx = @pnReleasePx ,
ReleaseQty = @piReleaseQty ,
RouteID = @psRouteID ,
SecondaryClOrdID = @psSecondaryClOrdID ,
SecondaryOrderID = @psSecondaryOrderID ,
SecurityID = @psSecurityID ,
SecurityIDSource = @psSecurityIDSource ,
SecurityType = @psSecurityType ,
SenderCompID = @psSenderCompID ,
SenderLocationID = @psSenderLocationID ,
SenderSubID = @psSenderSubID ,
SettleCurrency = @psSettleCurrency ,
SettleDate = @psSettleDate ,
SettleDate2 = @psSettleDate2 ,
SettleType = @psSettleType ,
Side = @psSide ,
SolicitedFlag = @psSolicitedFlag ,
StopPx = @pnStopPx ,
StrikePrice = @pnStrikePrice ,
Suffix = @psSuffix ,
Symbol = @psSymbol ,
TargetCompID = @psTargetCompID ,
TargetLocationID = @psTargetLocationID ,
TargetSubID = @psTargetSubID ,
Text = @psText ,
TIF = @psTIF ,
TraderID = @psTraderID ,
TradeDate = @psTradeDate ,
TransactionTime = @psTransactionTime ,
UncommittedQty = @pnUncommittedQty ,
UpdateTime = @psUpdateTime ,
Version = @piVersion ,
AskPx = @pnAskPx ,
AttachedProps = @psAttachedProps ,
BasketID = @psBasketID ,
BidPx = @pnBidPx ,
CancelRequestID = @psCancelRequestID ,
CFICode = @psCFICode ,
DefaultSystem = @piDefaultSystem ,
ExcludeSystems = @piExcludeSystems ,
ExplantionText = @psExplantionText ,
LastUpdatedBy = @psLastUpdatedBy ,
ManualAckRequired = @psManualAckRequired ,
MultiLegID = @psMultiLegID ,
OrderAttribute = @piOrderAttribute ,
OrderGroupID = @psOrderGroupID ,
OrderID2 = @psOrderID2 ,
OrdRejReason = @piOrdRejReason ,
OrdStatus2 = @psOrdStatus2 ,
PegOffsetType = @piPegOffsetType ,
PortfolioID = @psPortfolioID ,
RepOrderID = @psRepOrderID ,
SystemID = @psSystemID ,
TickDir = @psTickDir ,
UseOrderID2 = @psUseOrderID2 ,
LastTrade = @pnLastTrade ,
ParentClOrderID = @psParentClOrdID ,
OrigOrderID = @psOrigOrderID ,
GiveUpID = @lsGiveUpID ,
OrderInterrfaceID = @lsOrderInterrfaceID ,
OnBehalfOfSubID = @psOnBehalfOfSubID ,
ExternalSymbol = @psExternalSymbol ,
ExternalSuffix = @psExternalSuffix ,
Reserved1 = @psReserved1 ,
Reserved2 = @psReserved2 ,
Reserved3 = @psReserved3 ,
Reserved4 = @psReserved4 ,
Reserved5 = @piReserved5 ,
Reserved6 = @piReserved6
WHERE OrderID = @psOrderID

END
ELSE
BEGIN

INSERT INTO CTOrders
(
Account ,
AccountType ,
AncestorOrderID ,
AvgPx ,
BookingUnit ,
ChainOrderID ,
ClOrdID ,
ClOrdLinkID ,
Commission ,
CommissionCurrency ,
CommissionType ,
ComplianceID ,
ContractAmtCurreny ,
ContractAmtType ,
ContractAmtValue ,
ContractMultiplier ,
CoveredorUncovered ,
CumQty ,
Currency ,
CustomerOrderCapacity ,
DayAvgPx ,
DayBookingInst ,
DayCumQty ,
DeliverToCompID ,
Designation ,
DeskID ,
DiscretionFlag ,
DiscretionInst ,
DiscretionOffset ,
EffectiveDate ,
ExDestination ,
ExecInst ,
ExpireDate ,
ExpireTime ,
FirmID ,
FirmOrderCapacity ,
ForexReq ,
HandlInst ,
LeavesQty ,
LocateReqd ,
LocationReference ,
MaturityDay ,
MaturityMonthYear ,
MaxFloor ,
MaxShow ,
MinQty ,
OnBehalfOfCompID ,
OrderID ,
OrderQty ,
OrderQty2 ,
OrderRestrictions ,
OrdStatus ,
OrdType ,
OrigClOrdID ,
ParentOrderID ,
PegOffset ,
PrevClosePx ,
Price ,
Price2 ,
PriceType ,
PositionEffect ,
ProcessCode ,
PutorCall ,
QtyOnStreet ,
QtyType ,
RegistID ,
ReleasePx ,
ReleaseQty ,
RouteID ,
SecondaryClOrdID ,
SecondaryOrderID ,
SecurityID ,
SecurityIDSource ,
SecurityType ,
SenderCompID ,
SenderLocationID ,
SenderSubID ,
SettleCurrency ,
SettleDate ,
SettleDate2 ,
SettleType ,
Side ,
SolicitedFlag ,
StopPx ,
StrikePrice ,
Suffix ,
Symbol ,
TargetCompID ,
TargetLocationID ,
TargetSubID ,
Text ,
TIF ,
TraderID ,
TradeDate ,
TransactionTime ,
UncommittedQty ,
UpdateTime ,
Version ,
AskPx ,
AttachedProps ,
BasketID ,
BidPx ,
CancelRequestID ,
CFICode ,
DefaultSystem ,
ExcludeSystems ,
ExplantionText ,
LastUpdatedBy ,
ManualAckRequired ,
MultiLegID ,
OrderAttribute ,
OrderGroupID ,
OrderID2 ,
OrdRejReason ,
OrdStatus2 ,
PegOffsetType ,
PortfolioID ,
RepOrderID ,
SystemID ,
TickDir ,
UseOrderID2 ,
LastTrade ,
ParentClOrderID ,
OrigOrderID ,
GiveUpID ,
OrderInterrfaceID ,
OnBehalfOfSubID ,
ExternalSymbol ,
ExternalSuffix ,
Reserved1 ,
Reserved2 ,
Reserved3 ,
Reserved4 ,
Reserved5 ,
Reserved6
)
VALUES
(
@psAccount ,
@piAccountType ,
@psAncestorOrderID ,
@pnAvgPx ,
@psBookingUnit ,
@psChainOrderID ,
@psClOrdID ,
@psClOrdLinkID ,
@pnCommission ,
@psCommissionCurrency ,
@psCommissionType ,
@psComplianceID ,
@psContractAmtCurreny ,
@piContractAmtType ,
@pnContractAmtValue ,
@pnContractMultiplier ,
@piCoveredorUncovered ,
@piCumQty ,
@psCurrency ,
@psCustomerOrderCapacity ,
@pnDayAvgPx ,
@psDayBookingInst ,
@piDayCumQty ,
@psDeliverToCompID ,
@psDesignation ,
@psDeskID ,
@psDiscretionFlag ,
@psDiscretionInst ,
@pnDiscretionOffset ,
@psEffectiveDate ,
@psExDestination ,
@psExecInst ,
@psExpireDate ,
@psExpireTime ,
@psFirmID ,
@psFirmOrderCapacity ,
@psForexReq ,
@psHandlInst ,
@piLeavesQty ,
@psLocateReqd ,
@psLocationReference ,
@piMaturityDay ,
@psMaturityMonthYear ,
@piMaxFloor ,
@piMaxShow ,
@piMinQty ,
@psOnBehalfOfCompID ,
@psOrderID ,
@piOrderQty ,
@piOrderQty2 ,
@psOrderRestrictions ,
@psOrdStatus ,
@psOrdType ,
@psOrigClOrdID ,
@psParentOrderID ,
@pnPegOffset ,
@pnPrevClosePx ,
@pnPrice ,
@pnPrice2 ,
@piPriceType ,
@psPositionEffect ,
@psProcessCode ,
@piPutorCall ,
@piQtyOnStreet ,
@piQtyType ,
@psRegistID ,
@pnReleasePx ,
@piReleaseQty ,
@psRouteID ,
@psSecondaryClOrdID ,
@psSecondaryOrderID ,
@psSecurityID ,
@psSecurityIDSource ,
@psSecurityType ,
@psSenderCompID ,
@psSenderLocationID ,
@psSenderSubID ,
@psSettleCurrency ,
@psSettleDate ,
@psSettleDate2 ,
@psSettleType ,
@psSide ,
@psSolicitedFlag ,
@pnStopPx ,
@pnStrikePrice ,
@psSuffix ,
@psSymbol ,
@psTargetCompID ,
@psTargetLocationID ,
@psTargetSubID ,
@psText ,
@psTIF ,
@psTraderID ,
@psTradeDate ,
@psTransactionTime ,
@pnUncommittedQty ,
@psUpdateTime ,
@piVersion ,
@pnAskPx ,
@psAttachedProps ,
@psBasketID ,
@pnBidPx ,
@psCancelRequestID ,
@psCFICode ,
@piDefaultSystem ,
@piExcludeSystems ,
@psExplantionText ,
@psLastUpdatedBy ,
@psManualAckRequired ,
@psMultiLegID ,
@piOrderAttribute ,
@psOrderGroupID ,
@psOrderID2 ,
@piOrdRejReason ,
@psOrdStatus2 ,
@piPegOffsetType ,
@psPortfolioID ,
@psRepOrderID ,
@psSystemID ,
@psTickDir ,
@psUseOrderID2 ,
@pnLastTrade ,
@psParentClOrdID ,
@psOrigOrderID ,
@lsGiveUpID ,
@lsOrderInterrfaceID ,
@psOnBehalfOfSubID ,
@psExternalSymbol ,
@psExternalSuffix ,
@psReserved1 ,
@psReserved2 ,
@psReserved3 ,
@psReserved4 ,
@piReserved5 ,
@piReserved6
)

END

END

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-07-01 : 18:30:55

Do all the rows have the same datetime value or do just a couple have the same value?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-07-01 : 20:09:43

Around 7600 rows have the same time stamp of 2007-06-27 09:32:58.303

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources