Insert query taking forever to run and no obvious reason

[aolb]

I am generating insert queries and then executing them.

On this insert query it is taking 1 second to query 100 records but almost 30 seconds to insert 100 records.

a different generated query which is basically the same inset query inserting into the same table but getting the data from a different view is fine, 4200 records inserted in 1 second.

The data being queried is not being affected by the insert query.

I am grasping at straws to identify the problem. I have tried to drop the constraits, run traces, looked at execution plans. I am out of ideas!!!

I know I have raised a thread before to do with the same problem about a week ago but this time I have identified that the updates are taking place, just very slowley.

Please help me solve this whilst I still have hair!

This is the query that is causing the problems. Insert query works fine if I comment out the "-- DuplicateAssetNo is null and " on the where clause!!



insert into tblAssetDataSourceIdentifier(
ADSI_A_Id, ADSI_AssetIdentifier, ADSI_Datasource, ADSI_ValidateDT)
select top 100 A_Id, AIR2_Id, 'AIR', 'Feb 4 2005 1:01PM'
from tblAsset
left join tblAssetDataSourceIdentifier on ADSI_A_Id = A_Id
inner join AIR.dbo.vZZPrinters on A_AssetNo = AssetNo
left join (
select dbo.fUnknown(AssetNo) as DuplicateAssetNo,
count(dbo.fUnknown(AssetNo)) as DuplicateAssetNoCount
from AIR.dbo.vZZPrinters
where dbo.fUnknown(AssetNo) <> 'Unknown'
group by dbo.fUnknown(AssetNo)
having count(dbo.fUnknown(AssetNo)) > 1) as DuplicateAssetNo
on AssetNo = DuplicateAssetNo
left join (
select dbo.fUnknown(SerialNo) as DuplicateSerialNo,
count(dbo.fUnknown(SerialNo)) as DuplicateSerialNoCount
from AIR.dbo.vZZPrinters
where dbo.fUnknown(SerialNo) <> 'Unknown'
group by dbo.fUnknown(SerialNo)
having count(dbo.fUnknown(SerialNo)) > 1) as DuplicateSerialNo
on DuplicateSerialNo = SerialNo

where
DuplicateAssetNo is null and
DuplicateSerialNo is null and
ADSI_Id is null and dbo.fUnknown(AssetNo) <> 'Unknown'

 

[hmckillop]

Can you show the execution plan of the SELECT statement before trying to INSERT - also does this take the same time or is the delay only on inserting the data.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[aolb]

No cules in the execution plan. the insert is 0%.

Have now identified that the problem is in the view but this is a simple query. The problem must bein the join with tblAssetPrint as the only difference between this view and the Computer view is I inner joins to tblAssetComputer instead of tblAssetPrinter.

Keys are all integers.

Any thoughts?


SELECT 'AIR' AS DataSource, cast(tblAsset.A_Id as varchar(9)) AS AIR2_Id, A_AssetNo AS AssetNo, AH_SerialNo AS SerialNo, AH_DeviceName AS DeviceName
FROM tblAsset
INNER JOIN tblAssetHardware ON AH_A_Id = A_Id
INNER JOIN tblAssetPrinter ON A_Id = AP_A_Id

 

[jhall01]

Indexes? Check the indexes on these tables.

I once had a system that took 3 minutes to insert thousands of rows from different text files. our server crashed and lost it so when i restored it it took about 30 minutes to do the same exact job.

Turned out i didn't add the indexes back on when i moved the data over and when i added the indexes back i was off and running at the 3 minute mark again.

Indexes will decrease the inserts if inserting into an indexed table but adding indexes on tables that you are joining to insert into the table improve performance drastically!

 

[aolb]

Good thought but this is not the issue.

Whilst there are indexes on the table I am inserting into, the Computer query inserts 4500 records in 2 seconds whilst it takes 30 seconds to do 100 records for printers. Note I am inserting into the same table.

Selecting the printers takes 1 second to run.