October 22, 2010 at 8:31 am
I have the following sproc which I'm running as a job. As of this moment, it's been running since Wednesday and it is on 603 of 69878 rows; it will take months to complete.
The problem, as I see it, (besides my poor SQL skills that is) is the table TD_Asset contains 3436458 rows and TD_Asset_Text contains 19349520; combined with the 69878 rows in CTS_SCO that's an <expletive> number of traversals. I don't know how else to achieve the results I'm looking for. Does anyone?
declare @SystemID int, @ClassID int, @ObjectID int, @Changed int
declare @SystemDesc varchar(80), @ClassDesc varchar(80), @ObjectDesc varchar(80)
declare @Assets table (AssetID int, ISISID varchar(8), HasText bit)
declare cSCOs cursor fast_forward for
select in_SystemID, in_ClassID, in_ObjectID
from dbo.CTS_SCO
open cSCOs
fetch next from cSCOs into @SystemID, @ClassID, @ObjectID
while @@fetch_status = 0
begin
insert into @Assets(AssetID, ISISID)
select in_AssetID, tx_ISISID
from ISIS.TD_Asset a
join dbo.CTS_Accounts i on a.tx_ISISID = i.ISISID
where a.in_SystemID = @SystemID and a.in_ClassID = @ClassID and a.in_ObjectID = @ObjectID
update dbo.CTS_SCO
set in_AssetCount = (select count(*) from @Assets)
where in_SystemID = @SystemID and in_ClassID = @ClassID and in_ObjectID = @ObjectID
update @Assets
set HasText = 1
from @Assets a
join ISIS.TD_Asset_Text t on a.AssetID = t.in_AssetID and a.ISISID = t.tx_ISISID
select @Changed = count(*) from @Assets where HasText = 1
select @Changed = @Changed + count(*)
from ISIS.TD_Asset_Text a1
join SCOEditor.dbo.TD_Asset_Text a2 on a1.in_CorpAssetTextID = a2.in_CorpAssetTextID
where (a1.in_SystemID = @SystemID or a1.in_ClassID = @ClassID or a1.in_ObjectID = @ObjectID)
and a2.dt_LastChanged < a1.dt_LastChanged
update dbo.CTS_SCO
set in_ChangedCount = @Changed
where in_SystemID = @SystemID and in_ClassID = @ClassID and in_ObjectID = @ObjectID
delete from @Assets
fetch next from cSCOs into @SystemID, @ClassID, @ObjectID
end
close cSCOs
deallocate cSCOs
October 22, 2010 at 8:43 am
We need way more info than that to optimize this query.
We need table definitions (create table scripts) along with all the keys, indexes and triggers if any.
We also need sample data and required output from that data to give you a tested results.
That being said I'm sure we can bring that execution down to a few hours or minutes rather than months :w00t:. So your further help even disguised as work can really help us help you!
October 22, 2010 at 9:29 am
I agree that we'll need quite a bit more to help you with this. If you read the first link in my signature, you'll see the kinda of data and format that will help you get the best answers the fastest.
In addition, probably the best way to speed up this procedure will be the removal of your cursor and using proper set based code to do you updates in on shot as opposed to millions of single update statements.
For example, your first insert into the @assets table could perhaps be done in one query as such instead of 69878 individual inserts
insert into @Assets(AssetID, ISISID)
select in_AssetID, tx_ISISID
from ISIS.TD_Asset a
INNER join dbo.CTS_Accounts i
on a.tx_ISISID = i.ISISID
INNER JOIN dbo.CTS_SCO sco
ONa.in_SystemID = sco.in_SystemID
AND a.in_ClassID = sco.in_ClassID
AND a.in_ObjectID = sco.in_ObjectID
where a.in_SystemID = @SystemID
and a.in_ClassID = @ClassID
and a.in_ObjectID = @ObjectID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply