May 28, 2015 at 1:21 pm
Hi All,
Server:
MSSQL Server 2008 enterprise Edition
Dell R710
60GB RAM
2 Processors Xeon E5520.
Dedicated only for Database.
I have following tables:
CREATE TABLE [dbo].[RepSheet0_](
[Source] [char](40) NULL,
[stop] [datetime] NULL,
[Customer] [varchar](20) NULL,
[Vendor] [varchar](20) NULL,
[CustomerID] [char](17) NULL,
[VendorID] [char](17) NULL,
[OriginationIP] [varchar](20) NULL,
[TerminationIP] [varchar](20) NULL,
[DialedNumber] [varchar](50) NULL,
[TxNumber] [varchar](50) NULL,
[Duration] [float] NULL,
[Country] [varchar](64) NULL,
[Region] [varchar](64) NULL,
[Type] [varchar](10) NULL,
[Class] [varchar](10) NULL,
[LMC] [varchar](80) NULL,
[RegionPrefix] [varchar](20) NULL,
[CustPrefix] [varchar](20) NULL,
[VendorPrefix] [varchar](20) NULL,
[Cost] [float] NULL,
[Price] [float] NULL,
[CustRounding] [int] NULL,
[VendorRounding] [int] NULL,
[CustBillMinutes] [float] NULL,
[VendorBillMinutes] [float] NULL,
[CPterms] [varchar](20) NULL,
[VPterms] [varchar](20) NULL,
[RA] [smallint] NULL,
[routetype] [varchar](1) NOT NULL,
[routename] [varchar](50) NULL,
[VendorTag] [char](10) NULL,
[BT] [varchar](2) NULL,
[DialedNumber09] [varchar](10) NULL
)
CREATE TABLE [dbo].[VendorBreakout](
[idVendorBreakout] [int] NOT NULL,
[VendorName] [varchar](20) NOT NULL,
[Prefix] [varchar](16) NOT NULL,
[Cost] [smallmoney] NOT NULL,
[Modified] [datetime] NULL,
[lenprefix] AS (len([Prefix]))
)
VendorBreakout
9,202,876 rows.
Nonclustered Index on lenprefix include Vendorname, prefix, cost
RepSheet0_
6,570,862 rows
Nonclustered Index on Cost include Vendor and DialedNumber
I'm trying to update rows on RepSheet0_ using following view:
SELECT dbo.RepSheet0_.Cost AS SetCost, CDRDB.dbo.VendorBreakout_borrame.Cost AS Value, dbo.RepSheet0_.VendorPrefix AS SetPrefix,
CDRDB.dbo.VendorBreakout_borrame.Prefix
FROM dbo.RepSheet0_ INNER JOIN
CDRDB.dbo.VendorBreakout_borrame ON LEFT(dbo.RepSheet0_.DialedNumber, 9) = CDRDB.dbo.VendorBreakout_borrame.Prefix AND
dbo.RepSheet0_.Vendor = CDRDB.dbo.VendorBreakout_borrame.VendorName
WHERE (dbo.RepSheet0_.Cost = 0) AND CDRDB.dbo.VendorBreakout_borrame.lenPrefix = 9
Actually Update Execution time 45s, we need to decrease this time, I tried a lot of things but time still the same.
Please look into the Attachment, execution Plan.
Thank you so much for all your help and time.
Regards
May 28, 2015 at 1:34 pm
Hi and welcome to the forums. It would be very helpful if you could post the actual execution plan. Additionally the table definition and index definitions would be really helpful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2015 at 3:52 pm
Hi, thank you for your response. I already upload the execution plan and here are the index definition, table definitions are above, please look into my first post.
CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[VendorBreakout]
(
[lenprefix] ASC
)
INCLUDE ( [VendorName],
[Prefix],
[Cost]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[RepSheet0_]
(
[Cost] ASC
)
INCLUDE ( [Vendor],
[DialedNumber]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Regards
May 28, 2015 at 3:58 pm
rocho09 (5/28/2015)
Hi, thank you for your response. I already upload the execution plan and here are the index definition, table definitions are above, please look into my first post.CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[VendorBreakout]
(
[lenprefix] ASC
)
INCLUDE ( [VendorName],
[Prefix],
[Cost]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[RepSheet0_]
(
[Cost] ASC
)
INCLUDE ( [Vendor],
[DialedNumber]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Regards
No, you uploaded a picture of the execution plan which really is useless. You need to save the actual execution plan as a .SQLPLAN file and upload that.
May 28, 2015 at 4:17 pm
Ohhh I see, so sorry my mistake. Here is the Execution Plan.
Thank you for your comments.
Regards
May 29, 2015 at 7:09 am
rocho09 (5/28/2015)
Ohhh I see, so sorry my mistake. Here is the Execution Plan.Thank you for your comments.
Regards
Any chance you can upload the actual execution plan instead of the estimated one?
So neither of these tables have a clustered index?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 7:12 am
How about doing an
update VendorCostPrefix09_ set SetCost=value, SetPrefix=Prefix--
where setcost <> value or setprefix<>prefix
Could be you are updating the entire table where maybe only 20 rows actually need the update.
Since you attached an estimated plan and not an actual plan, I am feeling around in a dimly lit room.
May 29, 2015 at 11:00 am
Hi All,
Here is the Actual Execution Plan.
Thanks!
Regards
Rodrigo
May 29, 2015 at 11:47 am
You mentioned above that your update is running within 45 seconds. It's unclear why this would be unacceptable. If you are updating this table multiple times per day, then you may want to rethink whatever it is you're doing. That amount of mass DML operations done on a routine basis will negatively impact your database, not just runtime, but also in terms of backups, I/O, and data fragmentation.
I'm assuming that the tables are properly indexed for the join, and the issue is that you're attempting to update a large number of rows.
Generally speaking, most of the duration spent for a large (100,000 rows or more) UPDATE or DELETE operation will be transaction logging. From what I've seen, and I have a lot of experience running massive updates against data warehouse tables, the duration required for writing to the transaction log increases exponentially (not linearly). If you are going to run an update operation that affects more than one million rows, then you would benefit from batch updating.
The following technique will yield better performance regardless of recovery model; simple, full, or bulk. You can cancel at any time, and any rows updated to that point are committed, so long as don't wrap the entire loop in a transaction.
set nocount on;
declare @batch_rows int = 0, @total_rows int = 0, @UpdateDate datetime = getdate();
while 1 = 1
begin
-- to minimize transaction log growth, checkpointing and pausing
-- each batch will help re-use space:
waitfor delay '00:00:05';
checkpoint;
-- update batch of rows:
update top (1000000) MyLargeTable
set Price = Price * 1.10
, UpdateDate = @UpdateDate
where UpdateDate is null or UpdateDate < @UpdateDate;
select @batch_rows = @@rowcount;
select @total_rows = @total_rows + @batch_rows;
-- print status message:
raiserror('Rows affected: %d', 0, 1, @total_rows) with nowait;
-- if no rows were deleted, then break from loop:
if @batch_rows = 0 break;
end;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 29, 2015 at 4:59 pm
Hi Eric,
Thank your for your help and comments.
Yes, I metioned above this update takes 45 seconds, reason why this unacceptable is because this is one query of 11 I need to run to the same table and total execution time for 11 querys is 7 minutes.
Each Query substring field "Dialednumber" from RepSheet0_ from left to right and the result is compared with field Prefix on VendorBreakout that has the same lenght of the substring result. Example:
Dialednumber = "1234567890123"
First Update
LEFT(Dialednumber,11) = "12345678901"
"12345678901" = VendorBreakout.prefix AND VendorBreakout.prefixlen = 11 AND RepSheet0_.Cost = 0
Second Update
LEFT(Dialednumber,10) = "1234567890"
"1234567890" = VendorBreakout.prefix AND VendorBreakout.prefixlen = 10AND RepSheet0_.Cost = 0
Third Update
LEFT(Dialednumber,9) = "123456789"
"123456789" = VendorBreakout.prefix AND VendorBreakout.prefixlen = 9 AND RepSheet0_.Cost = 0
And continue until 1
I adjust your technique and the result is:
set nocount on;
declare @batch_rows int = 0, @total_rows int = 0;
while 1 = 1
begin
-- to minimize transaction log growth, checkpointing and pausing
-- each batch will help re-use space:
waitfor delay '00:00:05';
checkpoint;
-- update batch of rows:
update top (1000000) dbo.RepSheet0_
set dbo.RepSheet0_.Cost=dbo.VendorBreakout.Cost,
dbo.RepSheet0_.VendorPrefix=dbo.VendorBreakout.Prefix
FROM dbo.RepSheet0_ INNER JOIN
dbo.VendorBreakout ON LEFT(dbo.RepSheet0_.DialedNumber, 6) = dbo.VendorBreakout.Prefix
AND dbo.RepSheet0_.Vendor = CDRDB.dbo.VendorBreakout.VendorName
WHERE (dbo.RepSheet0_.Cost = 0) AND (dbo.VendorBreakout.lenprefix= 6)
select @batch_rows = @@rowcount;
select @total_rows = @total_rows + @batch_rows;
-- print status message:
raiserror('Rows affected: %d', 0, 1, @total_rows) with nowait;
-- if no rows were deleted, then break from loop:
if @batch_rows = 0 break;
end;
Execution time was 1:19 seconds instead of 1:17 seconds with the actual method.
Expected Target would be 2 minutes for the 11 querys.
Thank you!
Regards
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply