July 1, 2002 at 10:59 am
Hello everyone, I have the following problem. The following two inserts are similar, but the first insert takes over 4 hours and the second insert takes over 5 minutes. They both affect the same number of rows. I like to know why the first insert takes so much longer.
insert into dbo.dptitleownedf
(UPID, fromTablePK, fromTableListId, fromTableSegmentID)
selectap.upid, b.ListIdentity, b.list, b.segment
from dbo.dpAggregatedprospect ap
join disneycampaign.dbo.ListAnimatedDVD b
on (
ap.SourceRecKey = b.ListIdentity
andap.sourcelist = b.list
andap.sourcelistseg = b.segment
)
go
-- dptitleownedf has over 6.2 million rows and does not have any indexes
-- dpAggregatedprospect has over 14 million rows and has index on primary key, index on SourceRecKey, sourcelist, sourcelistseg columns (1 combo index).
-- ListAnimatedDVD has over 44 thousand rows and has index on ListIdentity, list, segment (1 combo key) but does not have a primary key index.
The following is the showplan_text output:
|--Table Insert(OBJECT:([DISNEYPROD].[dbo].[dpTitleOwnedF]), SET:([dpTitleOwnedF].[datePopulated]=NULL, [dpTitleOwnedF].[fromTableSegmentID]=.[Segment], [dpTitleOwnedF].[fromTableListId]=[Expr1003], [dpTitleOwnedF].[assumedOrKnown]=NULL, [dpTitleOwned
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1002]=Convert(.[ListIdentity]), [Expr1003]=Convert(.[List])))
|--Parallelism(Gather Streams)
|--Filter(WHERE:(Convert([ap].[SourceRecKey])=.[ListIdentity]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([DISNEYPROD].[dbo].[dpAggregatedProspect] AS [ap]))
|--Nested Loops(Inner Join, OUTER REFERENCES:(.[List], .[Segment]) WITH PREFETCH)
|--Index Scan(OBJECT:([DisneyCampaign].[dbo].[ListAnimatedDVD].[listid_list_seg] AS ))
|--Index Seek(OBJECT:([DISNEYPROD].[dbo].[dpAggregatedProspect].[ix_SourceList+Seg] AS [ap]), SEEK:([ap].[SourceList]=.[List] AND [ap].[SourceListSeg]=.[Segment]) ORDERED FORWARD)
insert into dbo.dptitleownedf
(UPID, fromTablePK, fromTableListId, fromTableSegmentID)
selectap.upid, b.ListIdentity, b.list_id, b.segment_id
from dbo.dpAggregatedprospect ap
join disneycampaign.dbo.ListBugsLifeRebate b
on (
ap.SourceRecKey = b.ListIdentity
andap.sourcelist = b.list_id
andap.sourcelistseg = b.segment_id
)
go
-- dptitleownedf has over 6.2 million rows and does not have any indexes
-- dpAggregatedprospect has over 14 million rows and has index on primary key, index on SourceRecKey, sourcelist, sourcelistseg columns (1 combo index).
-- ListBugsLifeRebate has over 40 thousand rows and has index on ListIdentity, list, segment_id (1 combo key) but does not have a primary key index.
The following is the showplan_text output:
|--Table Insert(OBJECT:([DISNEYPROD].[dbo].[dpTitleOwnedF]), SET:([dpTitleOwnedF].[datePopulated]=NULL, [dpTitleOwnedF].[fromTableSegmentID]=.[Segment_id], [dpTitleOwnedF].[fromTableListId]=[Expr1003], [dpTitleOwnedF].[assumedOrKnown]=NULL, [dpTitleOw
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1002]=Convert(.[ListIdentity]), [Expr1003]=Convert(.[List_id])))
|--Hash Match(Inner Join, HASH:(.[List_id], .[Segment_id], .[ListIdentity])=([ap].[SourceList], [ap].[SourceListSeg], [Expr1006]), RESIDUAL:((.[List_id]=[ap].[SourceList] AND .[Segment_id]=[ap].[SourceListSeg]) AND [Expr1006
|--Index Scan(OBJECT:([DisneyCampaign].[dbo].[ListBugsLifeRebate].[listid_list_seg] AS ))
|--Compute Scalar(DEFINE:([Expr1006]=Convert([ap].[SourceRecKey])))
|--Clustered Index Scan(OBJECT:([DISNEYPROD].[dbo].[dpAggregatedProspect].[ix_pk] AS [ap]))
July 5, 2002 at 9:31 am
I don't know if this will help, but there's a new network monitoring tool available, called AppDancer/FA. It won the Best of Interop at Networld+Interop in Network Management this past May. There is a 14 day free trial available on their website, http://www.appdancer.com, so it might be worth a try in order to solve your problem. It has some new ways to view your SQL user transactions in real-time.
Ronnie Genser
July 5, 2002 at 1:11 pm
Havent looked at the tool, not sure that network monitoring will add much...but never know!
First think I noticed is that the second query is using a hash join, first is not. Also second query is using a clustered index scan. Would be interesting to force the first to use a hash. Have you updated statistics on all tables?
Andy
July 8, 2002 at 1:22 pm
How do I update statistics?
July 8, 2002 at 1:31 pm
update statistics <table>
Steve Jones
July 10, 2002 at 9:10 pm
First depending on how many records change and how often deletes and inserts occurr I would make the indexes on ListAnimatedDVD and ListBugsLifeRebate clustered and this will should improve both queries.
Now as for the problem with first query slowing you down on it. Sounds like a couple of things can be your issue.
One the combo index ix_SourceList+Seg may need to be rebuilt to defrag it (or if using 2000 defragged).
Run DBCC SHOWCONTIG to get an idea of issues with the index (See BOL for help).
Ultimately I would run DBCC DBREINDEX if 7 (but you need to make sure you have enough free space and time) or DBCC INDEXDEFRAG in 2000 (see BOL for more help).
Then run sp_updatestats (runs UPDATE STATISTICS with resample on all tables and I prefer this in my maintainence).
Then DBCC UPDATEUSAGE to make sure sysindexes is reporting properly (again see BOL).
After this then run just the select portion of you queries with showplan_text on and see if anything changes.
If this helps any then you need to review your maintainence proceedures and make sure this is handled periodically to ensure highest proccessing times.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply