October 3, 2010 at 8:36 am
I am trying to update a table with 37 million rows. I wrote the following code, hoping to avoid problems with the log and/or tempdb running out of space.
I was thinking this code would update 500,000 records with a null Modified_Date and then loop to the next set of records. It appears that it is simply modifying the same 500,000 records over and over.
Instead of using TOP, I was using ROWCOUNT, however, it appears to have the same effect.
I would appreciate any suggestions you may have.
Rob
declare @rows int
select @rows = 1
begin tran
while @rows > 0
begin
UPDATE TOP (500000)
EOLI
SET
Modified_Date = COALESCE(EPOL.Update_Date, GETDATE())
from
EOLI LEFT OUTER JOIN AS EPOL
ON EOLI.ORDER_ID = EPOL.ORDER_ID
WHERE
EOLI.Modified_Date is null
select @rows = @@rowcount
commit tran
if @rows >0
begin tran
end
October 3, 2010 at 11:47 am
It would help if you provided the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (as a series of INSERT INTO statements), and expected results based on the sample data. You may be updating 37 million rows, but we only need about 10 rows of sample data to start with.
October 3, 2010 at 12:42 pm
I agree with Lynn, seeing the sample data and DDL for the tables involved will help here.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2010 at 4:45 pm
Thank you for your offer of assistance. Below is the code for the two tables. Attached is a sample of the data from the tables.
In the interest of full disclosure, I am also trying to update a field called MODIFIED_HASHBYTE. I have updated my UPDATE code to include the logic for that field as well. I'm thinking it shouldn't be part of the issue, however, I'm not sure.
MODIFIED_DATE and MODIFIED_HASHBYTE are both new fields and are null in all records. Once I update them here, the ETL will maintain all future updates.
Rob
declare @rows int
select @rows = 1
begin tran
while @rows > 0
begin
UPDATE TOP (500000)
EOLI
SET
Modified_HashByte =
HASHBYTES('SHA',
COALESCE(
CONVERT(nvarchar(25),
COALESCE(
EOLI.MJE_Date,
EOLI.REV_RECOGNIZED_DATE,
EOLI.SAP_FULLY_BILLED_DATE,
EOLI.DATE_ORDER_CLOSED,
EOLI.REVISED_REVENUE_RECOG_DATE,
EOLI.EXPECTED_REVENUE_RECOG_DATE,
EOLI.SAP_REQUESTED_DELIVERY_DATE)),
'None') ),
Modified_Date = COALESCE(EPOL.Update_Date, GETDATE())
from
EOLI LEFT OUTER JOIN AS EPOL
ON EOLI.ORDER_ID = EPOL.ORDER_ID
WHERE
EOLI.Modified_Date is null
select @rows = @@rowcount
commit tran
if @rows >0
begin tran
end
EPOL table:
CREATE TABLE [dbo].[EPOL](
[PROJECT_CODE] [nvarchar](50) NULL,
[SAP_ORDER_ID] [numeric](10, 0) NULL,
[EXPECTED_REVENUE_RECOG_DATE] [datetime2](7) NULL,
[REVISED_REVENUE_RECOG_DATE] [datetime2](7) NULL,
[DATE_ORDER_CLOSED] [datetime2](7) NULL,
[MODIFIED_DATE] [datetime2](7) NULL
) ON [PRIMARY]
EOLI table:
CREATE TABLE [dbo].[EOLI](
[SAP_ORDER_ID] [numeric](10, 0) NOT NULL,
[ORDERNO] [nvarchar](11) NULL,
[SOURCE] [nvarchar](64) NULL,
[LINEITEM_NUMBER] [nvarchar](6) NOT NULL,
[SAP_BILL_STATUS] [nvarchar](1) NULL,
[SAP_ORDER_TYPE] [nvarchar](4) NULL,
[SAP_SOLDTO_NUMBER] [nvarchar](10) NULL,
[SAP_HIERARCHY_NUMBER] [nvarchar](10) NULL,
[SAP_CREATED_DATE] [datetime2](7) NULL,
[SAP_REQUESTED_DELIVERY_DATE] [datetime2](7) NULL,
[SAP_FULLY_BILLED_DATE] [datetime2](7) NULL,
[SAP_LAST_UPDATED_DATE] [datetime2](7) NULL,
[MATERIAL_CODE] [nvarchar](18) NULL,
[QUANTITY] [nvarchar](15) NULL,
[NET_PRICE] [numeric](15, 2) NULL,
[SAP_GENERATING_QUOTE_NUM] [nvarchar](10) NULL,
[SAP_PAYERID] [nvarchar](10) NULL,
[CURRENCY] [nvarchar](4) NULL,
[SHIPTO_NUMBER] [nvarchar](10) NULL,
[SAP_SOLDTO_COUNTRY] [nvarchar](3) NULL,
[REV_RECOGNIZED_DATE] [datetime2](7) NULL,
[BackLog_Date] [datetime2](7) NOT NULL,
[Type] [nvarchar](1) NOT NULL,
[Project_Code] [nvarchar](50) NULL,
[Expected_Revenue_Recog_Date] [datetime2](7) NULL,
[Revised_Revenue_Recog_Date] [datetime2](7) NULL,
[Date_Order_Closed] [datetime2](7) NULL,
[MJE_Date] [datetime2](7) NULL,
[Modified_Date] [datetime2](7) NULL,
[Modified_HashByte] [nvarchar](20) NULL,
CONSTRAINT [PK_Orders_LineItem] PRIMARY KEY CLUSTERED
(
[SAP_ORDER_ID] ASC,
[LINEITEM_NUMBER] ASC,
[Type] ASC,
[BackLog_Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
October 3, 2010 at 7:05 pm
Try removing the begin tran and commit tran - every time you run your update, the same rows are selected because the update hasn't actually committed the changes yet.
If you need this to be done inside a transaction, then you will need some way to identify the rows (e.g. identity column) and update in batches based on that column.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 4, 2010 at 6:14 am
Jeffrey Williams-493691 (10/3/2010)
Try removing the begin tran and commit tran - every time you run your update, the same rows are selected because the update hasn't actually committed the changes yet.If you need this to be done inside a transaction, then you will need some way to identify the rows (e.g. identity column) and update in batches based on that column.
This change worked. Thank you very much.
I use SQL Server for reporting and data warehouse functions. I've never had to use transactions and so I'm not very familiar with them. I've done a little reading but I find it hard to obtain a good understanding of something that I cannot apply effectively.
Thank you again.
Rob
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply