Update query stalls server. 1

[sparkbyte]

Not sure why, but this simple update query brought the server to it's knees.

Update  dbo.tblTrackingTable
	Set FileNumber = Upper(dbo.tblTrackingTable.FileNumber),
		BoxNumber = Upper(dbo.tblTrackingTable.BoxNumber)
	from dbo.tblTrackingTable

And trying to use it in a trigger caused delays in the UI (Access 2003).

USE [MailroomTracking]
GO
/****** Object:  Trigger [dbo].[CheckFileNumbers]    Script Date: 07/06/2010 12:20:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		John Fuhrman
-- Create date: 
-- Description:	
-- =============================================
ALTER TRIGGER [dbo].[CheckFileNumbers] 
   ON  [dbo].[tblTrackingTable] 
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Update  dbo.tblTrackingTable
	Set FileNumber = Upper(dbo.tblTrackingTable.FileNumber),
		BoxNumber = Upper(dbo.tblTrackingTable.BoxNumber)
	from dbo.tblTrackingTable
          Inner Join Inserted As I
            On dbo.tblTrackingTable.Tracking_ID = I.Tracking_ID
END

Is there a better way to do this?? All I wanted to do is make sure all entries in the DB are UpperCase.

Thanks!!!


Thanks

John Fuhrman

 

[sparkbyte]

Oh, forgot to add.

There are approx 5,700,000 rows of data that need to be updated in this table.

CREATE TABLE [dbo].[tblTrackingTable](
	[Tracking_ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
	[Reship] [bit] NULL,
	[BoxNumberOriginal] [varchar](50) NULL,
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED

I am also looking into forcing the case in the Access UI, but i still need to update the table data and the mailroom is 24/7. They send out about 10 thousand files per day.

The average break down is about 13 Files per Tracking Number.

So causing the server to peek it processors also causes a backup in the mailroom. (not the best option)

Thanks!!

Thanks

John Fuhrman

 

[gmmastros]

How many rows are in the table?

You realize that ALL rows will be updated, whether they need it or not.

It's usually best to only update the rows that need updating.

I'd be tempted to break it up in to 2 updates because I'm guessing that MOST of your data is already in upper case, so...

Update  dbo.tblTrackingTable
    Set FileNumber = Upper(dbo.tblTrackingTable.FileNumber)
   from dbo.tblTrackingTable
  Where FileNumber Collate Latin1_General_Bin <> Upper(FileNumber)

Update  dbo.tblTrackingTable
    Set BoxNumber = Upper(dbo.tblTrackingTable.BoxNumber)
   from dbo.tblTrackingTable
  Where BoxNumber Collate Latin1_General_Bin <> Upper(BoxNumber)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

How many records in the table do you have? I suggest to do the original code in batches (you'll decide on the batch size).

Say,

update top (N)  dbo.tblTrackingTable
    Set FileNumber = Upper(dbo.tblTrackingTable.FileNumber),
        BoxNumber = Upper(dbo.tblTrackingTable.BoxNumber)
    from dbo.tblTrackingTable
where FileNumber <> upper(FileNumber) COLLATE SQL_Latin1_General_CP1_CS_AS

-- pick N as appropriate

and then you can use same in insert and update trigger (add condition to not do update when unnecessary)

PluralSight Learning Library

 

[bborissov]

It should be, if you have recursive triggers thet trigger will be fired over and over and over and over again.
You UPDATED the table in the TRIGGER that should be fired after the UPDATE.

I am NOT sure if this will work also (I never tried to manipulate INSERTED/DELETED tables before )

USE [MailroomTracking]
GO
/****** Object:  Trigger [dbo].[CheckFileNumbers]    Script Date: 07/06/2010 12:20:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        John Fuhrman
-- Create date:
-- Description:    
-- =============================================
ALTER TRIGGER [dbo].[CheckFileNumbers]
   ON  [dbo].[tblTrackingTable]
   AFTER INSERT,UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

Update Inserted Set FileNumber = Upper(FileNumber),
                    BoxNumber = Upper(BoxNumber)
END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

Can you please explain why you need these 2 columns to be upper case? I mean.... is this for printing labels or something?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Boris,

Don't think you can change values in these tables?

George - very reasonable question. Why does it matter how the items are stored in SQL Server? Or you don't want to use UPPER function in every select statement (in field lists)?

PluralSight Learning Library

 

[gmmastros]

Markros,

I was thinking... if there is only a small handful of queries that NEED upper case, it might be better to modify those queries instead of going through the pain of making sure everything is always Upper case. Seems like it would be a lot easier to force upper case only when it is needed instead of changing (what could be) a lot of code.

I was also thinking about computed columns.

Something like this:

Declare @Temp Table(BoxNumber VarChar(30), DisplayBoxNumber As Upper(BoxNumber))

Insert Into @Temp(BoxNumber) 
Select 'Apple' Union All
Select 'BANANA' Union All
Select 'grape'

Select BoxNumber, DisplayBoxNumber
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

I am trying to keep the data being entered into the tables uniform.

The data being entered via barcodes that I do not have direct control of.
e.g. UPS, FedEx, USPS, and DHS wide FileNumber lables

The other data is automaticaly entered.
TrackingDate
EmployeeID
MachineName

The Reship, and BoxNumberOriginal fields are used for tracking number corrections and damaged boxing reshippment. The use just inputs the original tracking number and a script does the rest.

USE [MailroomTracking]
GO
/****** Object:  StoredProcedure [dbo].[usp_ResendTrackingNumberLookup]    Script Date: 07/06/2010 14:31:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==========================================================
-- ==========================================================
-- Author:		John F Fuhrman III
-- Create date: 05/03/2010
-- Description:	usp_ResendTrackingNumberLookup
-- ==========================================================
-- ==========================================================

ALTER PROCEDURE [dbo].[usp_ResendTrackingNumberLookup] 
	-- Add the parameters for the stored procedure here
	@strTrackingNumber varchar(50),
	@strNewTrackingNumber varchar(50),
	@strEmployeeID VarChar(50),
	@strMachineName VarChar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- ==========================================================
-- ==========================================================
--	Find all records for the Tracking Number to be 
--	replicated.
-- ==========================================================
-- ==========================================================

SELECT     TOP (100) PERCENT 
		dbo.tblTrackingTable.Tracking_ID, 
		EmployeeFullName = Case
				When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
			End,
		dbo.tblEmployee.EmployeeFN, 
		dbo.tblEmployee.EmployeeLN, 
		dbo.tblTrackingTable.EmployeeID, 
		dbo.tblTrackingTable.MachineName, 
		UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber, 
		UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber, 
		dbo.tblTrackingTable.TrackingDate,
		dbo.tblTrackingTable.Reship,
		dbo.tblTrackingTable.BoxNumberOriginal

Into #TempTracking

FROM         dbo.tblTrackingTable FULL OUTER JOIN
                      dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID

WHERE		(BoxNumber = Upper(@strTrackingNumber))
		AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.') 
		AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)

-- ==========================================================
-- ==========================================================
--	Update File Numbers with New Tracking Number and  
--	Insert them into the Tracking Database
-- ==========================================================
-- ==========================================================

Update #TempTracking
	Set BoxNumber = @strNewTrackingNumber,
		TrackingDate = CURRENT_TIMESTAMP,
		Reship = 1,
		BoxNumberOriginal = @strTrackingNumber,
		EmployeeID = @strEmployeeID,
		MachineName = @strMachineName
End 

Insert into dbo.tblTrackingTable
	Select
		  EmployeeID
		, MachineName
		, BoxNumber
		, FileNumber
		, TrackingDate
		, Reship
		, BoxNumberOriginal

from #TempTracking

drop table #TempTracking

One reason is for readability.

The other is for curiosity. If all the data is uniform, (All uppercase) wouldn't the indexes perform better? Expecially as the table grows? This tables grows by about 10K rows daily.

Thanks

John Fuhrman

 

[gmmastros]

If all the data is uniform, (All uppercase) wouldn't the indexes perform better?

I really doubt it. And if so, I think the performance gains would be negligible.

There are a couple things that can have a dramatic impact on index performance. The data itself has a big impact. For example, if you have a multi-column index with 2 columns, and both are integers, you will fit approximately 1,000 rows in a single data page. Another 2-column index that has an integer and a varchar(50) will only fit about 80 rows in an index. That's a big difference. Index fragmentation, when it gets really bad, can have a dramatic impact on performance too.

Looking at the code above... why do you join to the employee table? It looks like you do a join, set some columns, and then you don't do anything with that data. In fact, it looks like it would be easy to convert this process in to a single insert/select query, which would likely speed it up considerably.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

To ignore case wouldn't the idex have to do something simular to

Select FileNumPrefix
From dbo.tblFileNumPrefix
	Where Ascii(FileNumPrefix) in ('65','97') -- Upper and Lower case 'a'

or...
But this forces an index (Right?)

Select FileNumPrefix
From dbo.tblFileNumPrefix
	Where FileNumPrefix LIKE '[aA]'

Thanks

John Fuhrman

 

[sparkbyte]

You know what I think you right abou the Join in the usp_ResendTrackingNumberLookup. It was towards the end of the day and I was rushing to correct an issue and was cut/pasting from another query.

I will need to relook at this SP and correct that.

Thanks!!!

A star.



Thanks

John Fuhrman

 

[sparkbyte]

George seperating the updates and using your where clause did it.

Thanks!!!!

Thanks

John Fuhrman