locked
If i drop index on target table what happend in ssis package? RRS feed

  • Question

  • Hi

    If i drop index  on target table but that table is using lookup transformation what happens in my ssis package?

    My package is fails are it will run successfully?

    Thanks

    JH Kiran.

    Sunday, April 7, 2013 4:26 PM

Answers

  • Dropping an index on the target table would reduce the total time to load the table. If the same table is used to perform a lookup then the time to retrieve the lookup result set may decrease if it is benefeting from the index. Now to answer you question, your package will not fail after removing the index if it was not failing with index.

    Nitesh Rai- Please mark the post as answered if it answers your question

    • Proposed as answer by SSISJoostMVP Monday, April 8, 2013 6:04 AM
    • Marked as answer by Mike Yin Thursday, April 11, 2013 8:23 AM
    Sunday, April 7, 2013 9:06 PM
  • Hi there,

    No doubt it will run if you do not change any other dependencies. Infect it may be faster but very hard to confirm. It all depends on what you are doing inside.

    In side ssis package when it starts reading your table it will go by every row and look for the data in your look up table. So there may not be any issue from your look up table because nothing changes.

    Reading from your table (one you dropped index) is your key measure. It depends on what index you dropped. Keeping a clustered index on a column may improve some performance. Having too many indexes do not improve performance in fact reduces.

    I hope that answers your question.

    Thanks

    Kumar

    • Marked as answer by Mike Yin Thursday, April 11, 2013 8:23 AM
    Sunday, April 7, 2013 10:42 PM

All replies

  • Dropping an index on the target table would reduce the total time to load the table. If the same table is used to perform a lookup then the time to retrieve the lookup result set may decrease if it is benefeting from the index. Now to answer you question, your package will not fail after removing the index if it was not failing with index.

    Nitesh Rai- Please mark the post as answered if it answers your question

    • Proposed as answer by SSISJoostMVP Monday, April 8, 2013 6:04 AM
    • Marked as answer by Mike Yin Thursday, April 11, 2013 8:23 AM
    Sunday, April 7, 2013 9:06 PM
  • Hi there,

    No doubt it will run if you do not change any other dependencies. Infect it may be faster but very hard to confirm. It all depends on what you are doing inside.

    In side ssis package when it starts reading your table it will go by every row and look for the data in your look up table. So there may not be any issue from your look up table because nothing changes.

    Reading from your table (one you dropped index) is your key measure. It depends on what index you dropped. Keeping a clustered index on a column may improve some performance. Having too many indexes do not improve performance in fact reduces.

    I hope that answers your question.

    Thanks

    Kumar

    • Marked as answer by Mike Yin Thursday, April 11, 2013 8:23 AM
    Sunday, April 7, 2013 10:42 PM