SQL studs and studettes!!!Please recommend something on this...Thx

  • 😀 All,

    This stored procedure is taking a long time to process updates on the data in two tables BatchTrans (400 to 500k rows avg) and ForeignBins (7500 rows). Anybody have any idea how to make this more efficient and this server is 64-bit too?

    sp_UpdateTrans

    CREATE PROCEDURE [dbo].[sp_UpdateTrans]

    @firstTransId decimal,

    @lastTransId decimal

    AS

    BEGIN

    SET NOCOUNT ON

    set @firstTransId = @firstTransId + 1;

    UPDATE BatchTrans

    SET CustCode = (SELECT CASE WHEN

    (SELECT COUNT(*) FROM ForeignBins

    WHERE CONVERT(INT, SUBSTRING(CardNum, 1, 9)) BETWEEN TopRange AND BottomRange) > 0

    THEN 'Y' ELSE 'N' END),

    CardNum = dbo._Func_EncodeString(CardNum),

    ExpireDate = dbo._Func_EncodeString(ExpireDate)

    WHERE TransId BETWEEN @firstTransId AND @lastTransId;

    /*

    UPDATE BatchTrans

    SET CustCode = (select case when

    (select count(*) from ForeignBins where CONVERT(INT, SUBSTRING(CardNum, 1, 9)) between TopRange and BottomRange)>0

    then 'Y' ELSE 'N' END)

    WHERE TransId between @firstTransId AND @lastTransId;

    UPDATE BatchTrans

    SET CardNum = dbo._Func_EncodeString(CardNum),

    ExpireDate = dbo._Func_EncodeString(ExpireDate)

    WHERE TransId between @firstTransId AND @lastTransId;

    */

    END

  • Can you please post table structure, index definitions, the execution plan (as a .sqlplan file, zipped and attached) and an estimate of the number of rows in the table and the rows affected by the query?

    As a first guess, I'd suggest moving the subquery into the from clause of the update if it's a correlated subquery.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I see a couple of things offhand, but you should still get the information that Gail requested.

    davidsalazar01 (9/26/2008)


    CREATE PROCEDURE [dbo].[sp_UpdateTrans]

    @firstTransId decimal,

    @lastTransId decimal

    AS

    BEGIN

    SET NOCOUNT ON

    set @firstTransId = @firstTransId + 1;

    UPDATE BatchTrans

    SET CustCode = (SELECT CASE WHEN

    (SELECT COUNT(*) FROM ForeignBins

    WHERE CONVERT(INT, SUBSTRING(CardNum, 1, 9)) BETWEEN TopRange AND BottomRange) > 0

    THEN 'Y' ELSE 'N' END),

    CardNum = dbo._Func_EncodeString(CardNum),

    ExpireDate = dbo._Func_EncodeString(ExpireDate)

    WHERE TransId BETWEEN @firstTransId AND @lastTransId;

    END

    - You defined the @firstTransID and @lastTransID as DECIMAL, Is the datatype of the TransID column really a decimal? If it's just an IDENTITY column or something similar it probably should just be INT or BIGINT instead of DECIMAL.

    - Instead of doing a CASE WHEN (SELECT COUNT(*) FROM ForeignBins...) > 0, it will be faster to do CASE WHEN EXISTS (SELECT NULL FROM ForeignBins...)

  • Yes... there's a huge problem... you have a double nested correlated subquery in the SET clause... and THAT will be executed, in it's entirety, once for every row qualified by the WHERE clause. To top it off, you also have two functions in the SELECT list. Those functions may each be a performance problem in and of themselves.

    Last, but not least, you've defined both input parameters simply as "decimal". Is the TransID column defined the same way? If not, you may be doomed to only getting table scans because of implicit conversions no matter what you do to the rest of the code.

    If you want better help, then we'll need you to follow Gail's request. You may also want to study the article in the link in my signature a bit before doing so.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Gilamonster,

    Here's the table def and indexes :P:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[BatchTrans](

    [TransID] [int] NOT NULL,

    [LineNum] [int] NOT NULL,

    [CardTypeID] [smallint] NULL CONSTRAINT [DF_BatchTrans_Temp_CardTypeID] DEFAULT (0),

    [CardNum] [varchar](50) NOT NULL,

    [TransDate] [datetime] NOT NULL,

    [TransAmt1] [money] NOT NULL,

    [TransAmt2] [money] NULL,

    [TransType] [char](2) NULL CONSTRAINT [DF_BatchTrans_Temp_TransType] DEFAULT ('00'),

    [RefNum] [varchar](11) NULL,

    [POSEntryMode] [char](2) NULL CONSTRAINT [DF_BatchTrans_Temp_POSEntryMode] DEFAULT ('ZZ'),

    [ExpireDate] [varchar](50) NULL,

    [AuthCode] [varchar](6) NULL,

    [AuthSource] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_AuthSource] DEFAULT ('Z'),

    [CardIDMethod] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_CardIDMethod] DEFAULT ('Z'),

    [AuthIndicator] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_AuthIndicator] DEFAULT ('0'),

    [BankTransID] [varchar](15) NULL,

    [ValidCode] [char](4) NULL CONSTRAINT [DF_BatchTrans_Temp_ValidCode] DEFAULT ('ZZZZ'),

    [DowngradeReason] [char](2) NULL CONSTRAINT [DF_BatchTrans_Temp_DowngradeReason] DEFAULT ('00'),

    [POSCapability] [char](2) NULL CONSTRAINT [DF_BatchTrans_Temp_POSCapability] DEFAULT ('ZZ'),

    [TerminalType] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_TerminalType] DEFAULT ('Z'),

    [TransCode] [char](2) NULL CONSTRAINT [DF_BatchTrans_Temp_TransCode] DEFAULT ('00'),

    [VoidIndicator] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_VoidIndicator] DEFAULT ('0'),

    [DebitIndicator] [char](1) NULL,

    [AuthResponse] [varchar](2) NULL,

    [RPSV] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_RPSV] DEFAULT ('0'),

    [AVS] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_AVS] DEFAULT ('0'),

    [SupplAmt] [money] NULL,

    [CVV2] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_CVV2] DEFAULT ('Z'),

    [IndustryType] [varchar](3) NULL CONSTRAINT [DF_BatchTrans_Temp_IndustryType] DEFAULT ('ZZZ'),

    [ChargeDescr] [varchar](2) NULL CONSTRAINT [DF_BatchTrans_Temp_ChargeDescr] DEFAULT ('ZZ'),

    [ExtraChargeAmt] [money] NULL,

    [ArrivalDate] [smalldatetime] NULL,

    [DepartDate] [smalldatetime] NULL,

    [OrderNum] [char](9) NULL,

    [MailIndicator] [char](1) NULL CONSTRAINT [DF_BatchTrans_Temp_MailIndicator] DEFAULT ('Z'),

    [TipAmt] [money] NULL,

    [ServerNum] [varchar](8) NULL,

    [DetailType] [varchar](2) NULL CONSTRAINT [DF_BatchTrans_Temp_DetailType] DEFAULT ('ZZ'),

    [CashBackAmt] [money] NULL,

    [SurchargeAmt] [money] NULL,

    [NetworkID] [varchar](6) NULL,

    [CustCode] [varchar](17) NULL,

    [IsSettled] [bit] NULL CONSTRAINT [DF_BatchTrans_Temp_IsSettled] DEFAULT (1),

    [IsHeld] [bit] NULL CONSTRAINT [DF_BatchTrans_Temp_IsHeld] DEFAULT (0),

    [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),

    [LastUpdateTime] [datetime] NULL CONSTRAINT [DF_BatchTrans_LastUpdateTime] DEFAULT (getdate()),

    [SitePriority] [tinyint] NULL CONSTRAINT [DF_BatchTrans_sitepriority] DEFAULT (1),

    [Last4] [varchar](4) NULL,

    [OrderNumRetail] [varchar](46) NULL,

    [discount_amt] [money] NULL DEFAULT (0),

    CONSTRAINT [PK_BatchTrans_Temp] PRIMARY KEY CLUSTERED

    (

    [TransID] ASC,

    [LineNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[BatchTrans] WITH NOCHECK ADD CONSTRAINT [FK_BatchTrans_CVV2] FOREIGN KEY([CVV2])

    REFERENCES [dbo].[CVV2] ([CVV2Code])

    NOT FOR REPLICATION

    GO

    ALTER TABLE [dbo].[BatchTrans] CHECK CONSTRAINT [FK_BatchTrans_CVV2]/

  • The "TransID" field is actually defined as INT in the table. So you suggest changing this to INT in the sproc instead of using decimal? The developer set the parameters as decimal.

  • The average rows in the BatchTrans table is usually 400k to 500k (hundred thousand) and ForeignBins is (7500 rows).

  • Chris,

    I'm confused. I tried using the EXISTS syntax you showed me as an example and it wouldn't compile. Can you clarify?

    Thanks,

    Dave :w00t:

  • At my age, I can't resist being called a SQL Stud.

    You'll need to pop a persisted, computed, column based on the formula CONVERT(INT, SUBSTRING(CardNum, 1, 9)) into the BATCHTRANS table, and put an index on it. then use that column for the WHERE clause where you test BETWEEN TopRange AND BottomRange

    Best wishes,
    Phil Factor

  • here's a WHERE EXISTS example for your code...

    ...

    CASE WHEN EXISTS

    (SELECT NULL FROM ForeignBins

    WHERE CONVERT(INT, SUBSTRING(CardNum, 1, 9)) BETWEEN TopRange AND BottomRange) THEN 'Y' ELSE 'N' END

    ...

    You should consider the other advice too, like the DECIMAL variables and trying to simplify the formula in this WHERE clause to something that can better use an index.

  • davidsalazar01 (9/28/2008)


    The "TransID" field is actually defined as INT in the table. So you suggest changing this to INT in the sproc instead of using decimal? The developer set the parameters as decimal.

    Indeed. If the data types don't match, they you'll get an implicit conversion and with an implicit conversion there's no chance of index seeks on that column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The "big one" from a performance perspective, that I see, is the query embedded within the SET statement, which is being executed for EVERY record the UPDATE statement touches. As the TopRange and BottomRange objects are NOT within the BatchTrans table, that appears to eliminate that embedded query from being correlated, so I would suggest taking it entirely outside of the UPDATE statement, and assigning it's value to a variable just once, and then using that variable within the update statement. Also, you really need to make sure your variables for parameters match the data type of the field they'll be compared against. My guess is, this proc will perform significantly better with just those changes.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply