Does anyone know of a way to get rid of a Table/Eager Spool? We're doing an INSERT statement like: INSERT INTO TABLE SELECT n FROM n This could sometimes INSERT up to 1000 rows. We are seeing 4 Eager Spools, I believe for each of the 4 indexes on the table. This consumes 75% of the query. When inserting about 1000 rows, it takes about 6 or 7 secs. Is this normal? Any suggestions? Thanks! H
It appears to me that the situation would occur when there are multiple rows to handle and it is using default transaction isolation level and concurrency handling. So long as there is only one row to be inserted at a time, then it should be OK. The INSERT for multiple rows will be treated as a single transaction which is not committed until all rows are written. A SELECT only reads committed information. Therefore when it performs the function in relation to the second row to be inserted, the first is not yet committed, so it cannot see the value that was placed in it. May try using READ UNCOMMITTED and see it gives any help. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
quote:Originally posted by hominamad Does anyone know of a way to get rid of a Table/Eager Spool? We're doing an INSERT statement like: INSERT INTO TABLE SELECT n FROM n This could sometimes INSERT up to 1000 rows. We are seeing 4 Eager Spools, I believe for each of the 4 indexes on the table. This consumes 75% of the query. When inserting about 1000 rows, it takes about 6 or 7 secs. Is this normal? Any suggestions? Thanks! H
mjhancock, am I missing your comment?!? -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs
