Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
noms
Starting Member
22 Posts |
 Posted - 2009-02-18 : 03:28:51
|
| hii have a table with 113458 records and i added two fields and wrote a cursor to update those fields. the query has been running for more than an hour and still running.i'm not sure why it's taking this long. i'm using SQLExpress, do you think that might the the problem?what should i do inorder to increase the performance?? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-02-18 : 05:20:58
|
Probably because its a cursor! (that may not have been written properly  ), why not post it, then maybe someone will be able to help you.. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-02-18 : 06:40:46
|
| Yes post the cursor you are running and let us have a look at it. It can most likely be optimized quite a bit.- Lumbago |
|
|
|
noms
Starting Member
22 Posts |
Posted - 2009-02-18 : 07:01:43
|
| thanks for respondinghere's the script below:declare @AssetID dm_ID, @DepID dm_ID, @TransID dm_ID, @SDate dm_Date declare lcursor CURSOR LOCAL FOR SELECT astAssets.ID, vw_astDepreciationLinks.StartDate, vw_astDepreciationTransactions.DepreciationLinksID, vw_astDepreciationTransactions.ID FROM astAssets INNER JOIN vw_astDepreciationLinks ON astAssets.ID = vw_astDepreciationLinks.RecordID INNER JOIN vw_astDepreciationTransactions ON vw_astDepreciationLinks.ID = vw_astDepreciationTransactions.DepreciationLinksID INNER JOIN finPeriods ON finPeriods.ID = vw_astDepreciationTransactions.FinPeriodID INNER JOIN finGLCodes ON finGLCodes.ID = vw_astDepreciationTransactions.GLID INNER JOIN finCostCentres ON finCostCentres.ID = vw_astDepreciationTransactions.CostCentreID INNER JOIN astTypes ON astTypes.ID = dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID) WHERE astAssets.IsActive = 1 AND finGLCodes.SiteID = 1000 AND finPeriods.ID <> 0 OPEN lcursor WHILE 0 = 0 BEGIN FETCH NEXT FROM lcursor INTO @AssetID,@SDate,@DepID,@TransID IF @@fetch_status <> 0 BREAK UPDATE dbo.UDMAssetValueReport SET StartDate = @SDate, DepreciationLinksID = @DepID WHERE AssetID = @AssetID and TransactionID = @TransID END CLOSE lcursor DEALLOCATE lcursor |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-02-18 : 07:29:30
|
Please test it before running in a production environment but I belive the syntax is correct:UPDATE a SET StartDate = b.StartDate, DepreciationLinksID = b.DepreciationLinksIDFROM dbo.UDMAssetValueReport a INNER JOIN ( SELECT astAssets.ID AS AssetID, vw_astDepreciationLinks.StartDate, vw_astDepreciationTransactions.DepreciationLinksID, vw_astDepreciationTransactions.ID AS TransactionID FROM astAssets INNER JOIN vw_astDepreciationLinks ON astAssets.ID = vw_astDepreciationLinks.RecordID INNER JOIN vw_astDepreciationTransactions ON vw_astDepreciationLinks.ID = vw_astDepreciationTransactions.DepreciationLinksID INNER JOIN finPeriods ON finPeriods.ID = vw_astDepreciationTransactions.FinPeriodID INNER JOIN finGLCodes ON finGLCodes.ID = vw_astDepreciationTransactions.GLID INNER JOIN finCostCentres ON finCostCentres.ID = vw_astDepreciationTransactions.CostCentreID INNER JOIN astTypes ON astTypes.ID = dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID) WHERE astAssets.IsActive = 1 AND finGLCodes.SiteID = 1000 AND finPeriods.ID <> 0) AS b ON a.AssetID = b.AssetID AND a.TransactionID = b.TransactionID - Lumbago |
|
|
|
|
|
|
|
|
performance