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

Question

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

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
September 26, 2008 at 8:46 pm

#382849

😀 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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

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...)

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 3

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)

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 4

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]/

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 5

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.

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 6

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

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 7

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:

Phil Factor SSC-Insane Points: 20244 More actions · Answer 8

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 9

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

sgmunson SSC Guru Points: 110640 More actions · Answer 11

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)