none
SQL Insert with Index Scan Problem....

    Question

  • Hi,

     

    I'm having a very strange problem when inserting rows into a table. Let me try to explain a little better:

    Table1 (for example Book) with a lot of fields and one foreign key to Table2 (idVolume) and a primary key (identity - idBook). This table has a clustered index only on the primary Key. (this table has something like 10 Millions rows)

    Table2 (for example Volume) with a lot of fields and a primary key (idVolume) identity. (this table has hundreds of rows)

     

    Now, the problem starts when I make a insertion on Table2. When I made an insertion, it was taking minutes to finish, so I stopped it.

    After looking into the execution plan of the query, there was something very very strange, there seems to be a clustered index scan on Table1... the problem is that since Table1 has so many rows, this index scan takes forever...

    Can anyone explain me why does the insertion on Table2 makes an clustered index scan on Table1? It really doesn't make any sense to me.

     

    Thanks in advance.

    João Teixeira

    Wednesday, September 01, 2010 8:26 AM

Answers

  • I think we would need to see the table definitions, the INSERT statement and the execution plans to say anything useful.

    But I can offer one speculation: there is indexed view involving the tables that needs to be updated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by KJian_ Tuesday, September 07, 2010 6:47 AM
    Wednesday, September 01, 2010 10:44 AM

All replies

  • As you said table2 is linked to table 1 with a foreign key. So when you are inserting data in tble 2 it is actually scanning table1 due to foreign key constraint.

    To make you insertion easy you may drop foreign key index on table 2 and then insert and recreate index on table 2


    -ankur
    Wednesday, September 01, 2010 8:54 AM
  • ankurverma,

     

    I'm not sure I've explained myself correctly, but the connection is something like this:

     

      Table1 (Book)                                                    Table2 (Volume)

      idBook                                                                idVolume

      idVolume           ----------------------------->          

     

    So, I understand the clustered index seek that happens when I make an Insertion in Table1 (because, we have to verify if the selected idVolume exists on Table2).

    What I don't understand is why the ____ when I make an insertion on Table2 we need to make a clustered index scan on Table1... this to me is totally unexpected.

    I just wonder what's the explanation to this "event".

     

    Best regards,

    João Teixeira

    Wednesday, September 01, 2010 9:13 AM
  • Perhaps trigger on that table?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 01, 2010 9:23 AM
    Answerer
  • Uri Dimant,

     

    No, there's no triggers in any table.

     

    Best regards,

    João Teixeira

    Wednesday, September 01, 2010 10:33 AM
  • Well, can you show us the execution plan of the query?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 01, 2010 10:37 AM
    Answerer
  • I think we would need to see the table definitions, the INSERT statement and the execution plans to say anything useful.

    But I can offer one speculation: there is indexed view involving the tables that needs to be updated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by KJian_ Tuesday, September 07, 2010 6:47 AM
    Wednesday, September 01, 2010 10:44 AM