Performance when adding PK
-
I had a procedure that runs every day that normally takes 20 minutes or so...without a primary key. I added a PK to one of my tables (the PK was 2 fields) and the procedure ran for over 5 hours and never completed.
Is there something I can do the PK the table and not have such a hit in performance?
-
In order that anyone here on this forum can give a decent answer, it's best that you provide the table schema, the query and the execution plans before and after the addition of the PK.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
With no detailed information this is my guess...
problem is not PK, but clustered index. By default PK becomes clustered index. By creating seperate clustered index (identity column) should resolve this issue.
Changin clustered index may affect other queries, so required testing is recommended
-
chriskellerx10 (8/2/2012)
I had a procedure that runs every day that normally takes 20 minutes or so...without a primary key. I added a PK to one of my tables (the PK was 2 fields) and the procedure ran for over 5 hours and never completed.Is there something I can do the PK the table and not have such a hit in performance?
Did you drop the clustered index automagically created when you added the PK? Without an index, you'd get a table scan for every inserted row.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Here is how my tables look now.
Customer
CustIDDealerIDFirstLast
1100BillJones
2100Mary Smith
3100JimBell
1133SteveClamp
2133FrankTom
1200JaneFoster
Mail
DealerIDCustIDMailIDMailDate
1001F22.1231247/1/2012
1002F22.1231247/1/2012
1331C16.1123237/3/2012
2001S15.4345526/30/2012
I am joining these 2 tables based on DealerID and CustID. The powers that be here at my office want the customer table as it is. It would be better just to Auto number CustID...but they do not want that. My motivation in creating the PK for the (CustID,DealerID) fields would not allow a duplicate DealerID/CustID record.
I was not sure of another way of doing that.
I am good with querying the database and such, but as far as the overall management of it...I am no expert.
-
chriskellerx10 (8/2/2012)
Here is how my tables look now.Customer
CustIDDealerIDFirstLast
1100BillJones
2100Mary Smith
3100JimBell
1133SteveClamp
2133FrankTom
1200JaneFoster
Mail
DealerIDCustIDMailIDMailDate
1001F22.1231247/1/2012
1002F22.1231247/1/2012
1331C16.1123237/3/2012
2001S15.4345526/30/2012
You need to post the table schema, and schema of any indexes on the tables. sample data is not a whole lot of good here.
on the limited data you did proivde its difficult to figure why it took so long, but if the table had no clustering key, sql server will make the PK the clustering key.
If your table is huge and in a complete state of disorganization this could take a while but Im not sure how much data you would need to ensure this operation took hours.
-
What is the best way to give you this information? As far as the schema and clustering? I have never created and clusters before.
Sorry for my limited knowledge here.
-
chriskellerx10 (8/2/2012)
What is the best way to give you this information? As far as the schema and clustering? I have never created and clusters before.Sorry for my limited knowledge here.
no problem;
easiest way is with the Management Studio GUI: simply right click the table, choose Script Table As....
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here are the table schemas
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CustID] [int] NOT NULL,
[DealerID] [int] NOT NULL,
[Distance] [int] NULL,
[SSN] [nvarchar](11) NULL,
[Last] [nvarchar](50) NULL,
[First] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](35) NULL,
[State] [nvarchar](2) NULL,
[Zipcode] [nvarchar](15) NULL,
[HPhone] [varchar](50) NULL,
[WPhone] [varchar](50) NULL,
[DOB] [smalldatetime] NULL,
[spouse] [nvarchar](30) NULL,
[spouseDOB] [smalldatetime] NULL,
[occupation] [varchar](50) NULL,
[Interests] [varchar](50) NULL,
[EMail] [nvarchar](60) NULL,
[MarkHot] [bit] NULL,
[isValid] [bit] NULL,
[OKServ] [bit] NULL,
[OkSales] [bit] NULL,
[Business] [bit] NULL,
[AssignedTo] [nvarchar](30) NULL,
[CustomerNo] [varchar](50) NULL,
[CPhone] [varchar](50) NULL,
[DNC] [bit] NULL,
[cDNC] [bit] NULL,
[DoNotMail] [bit] NULL,
[DoNotEmail] [bit] NULL,
[NCOA] [bit] NULL,
[NCOADate] [date] NULL,
[LastService] [date] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Campaigns](
[ID] [nvarchar](50) NULL,
[CampaignID] [nvarchar](5) NULL,
[DealerID] [int] NULL,
[CustID] [int] NULL,
[OrderID] [int] NULL,
[BeginDate] [smalldatetime] NULL,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orderlist](
[OrderID] [int] NOT NULL,
[DealerID] [int] NULL,
[PVSale] [nvarchar](3) NULL,
[Campaign] [nvarchar](30) NULL,
[Match] [nvarchar](50) NULL,
[DealNo] [varchar](50) NULL,
[BuyerID] [int] NULL,
[Salesman] [nvarchar](30) NULL,
[Date] [smalldatetime] NULL,
[Active] [bit] NULL,
[PaidOff] [bit] NULL,
[Category] [nvarchar](4) NULL,
[VehType] [nvarchar](8) NULL,
[Source] [nvarchar](12) NULL,
[PurchaseType] [nvarchar](9) NULL,
[Serial] [nvarchar](17) NULL,
[Stock] [char](10) NULL,
[Miles] [int] NULL,
[Trade1] [varchar](50) NULL,
[Trade2] [varchar](50) NULL,
[Rate] [numeric](18, 4) NULL,
[Term] [int] NULL,
[Days1st] [int] NULL,
[Payment] [money] NULL,
[Clife] [money] NULL,
[Ahealth] [money] NULL,
[Warranty] [money] NULL,
[FinanceAmount] [money] NULL,
[Residual] [money] NULL,
[LienHolder] [varchar](50) NULL,
[GroupNumber] [char](10) NULL,
[Valid] [bit] NULL,
[MthMiles] [int] NULL,
[EnterBy] [char](10) NULL,
[UVC] [nvarchar](6) NULL,
[PaymentsLeft] [int] NULL,
[FirstPaymentDate] [smalldatetime] NULL,
[LastPaymentDate] [smalldatetime] NULL,
[PaymentsMade] [int] NULL,
[MonthsOwned] [int] NULL,
[EquityValue] [numeric](18, 2) NULL,
[NetFinanceBalance] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[Year] [char](4) NULL,
[Make] [nvarchar](50) NULL,
[Model] [nvarchar](50) NULL,
[BodyStyle] [nvarchar](50) NULL,
[Series] [nvarchar](50) NULL,
[ClassCategory] [nvarchar](50) NULL,
[YearCode] [char](10) NULL,
[DateEntered] [smalldatetime] NULL,
[var1] [nvarchar](50) NULL,
[var2] [nvarchar](50) NULL,
[var3] [nvarchar](50) NULL,
[var4] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And here is one of the queries that were taking soo long after I added the PK
with OrderL
as
(
select c.First,c.Last,c.Address,c.City,c.State,o.*
from
Orderlist as o
inner join
Customer as c
on
o.DealerID = c.DealerID
and
o.BuyerID = c.CustID
and
o.DealerID = @DealerID
),
Camp
as
(
select n.*,c.First,c.Last,c.Address,c.City,c.state
from
Customer as c
inner join
Campaigns as n
on
c.CustID = n.CustID
and
c.DealerID = n.DealerID
and
c.DealerID = @Dealerid
)
--Last Name and Address Match
update Orderlist
set
PVSale = 'PV',
Match = 'L' + CAST(m.CustID as varchar),
Campaign = m.id
from
(
select c.ID,o.DealerID,o.orderid,o.First,o.Last,o.Address,o.City,o.State,o.Date,c.custid
from Camp as c,
OrderL as o
where
c.Last = o.Last
and
c.Address = o.Address
and
c.City = o.City
and
o.Date between c.BeginDate and DATEADD(dd,63,c.BeginDate)
and
o.BuyerID <> c.CustID
) as m
where
orderlist.OrderID = m.OrderID
and
orderlist.DealerID = m.DealerID
and
(Orderlist.PVSale <> 'PV' or Orderlist.PVSale is null)
-
Chris - could you post also the schema of any indexes on those tables?
Not all gray hairs are Dinosaurs!
-
Miles Neale (8/2/2012)
Chris - could you post also the schema of any indexes on those tables?Again, forgive my skill level here. What is the best way to show this?
-
Miles Neale (8/2/2012)
Chris - could you post also the schema of any indexes on those tables?Sorry, How would I show this?
-
Same way you did the tables. In SSMS under the table you will see a folder 'Indexes' expand that then right click on the index and script it out.
-
Chris,
I am not certain of the status of the problem as it stands right now, you have not posted any update. However, there are all kinds of solutions available to you and if you can further define the problem there are a number of folks here who can assist.
Being a rookie and trying to make significant database changes without knowing really what could or should be done is very trying. There are those who can help if you tell us what the indices look like now.
And how you show us the idiocies? Instead of pasting only part of the table structure like you did, please post it all SQL for the table including the alter statements on the bottom.
Thanks, and I hope you have already solved this.
M.
Not all gray hairs are Dinosaurs!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply