Post reply

Dynamically Creating Indexes

  • February 11, 2009 at 1:44 am

    #155266

    Hi

    I have a situation where I have to create a couple of indexes dynamically.

    Can I pack them in an sp and in turn call that sp inside my transaction sp and then at the end of the main sp drop all the indexes created thru another Sp?

    what are the downfalls of such an approach?

    and how do i view transaction logs in sql server?

    Thanks in advance.

  • February 11, 2009 at 11:47 pm

    #942630

    Hi,

    I need more information in order to 'see' the entire picture and to help you with a good solution.

    1. What sql version and sp ?

    2. Is fregmentation your source of problem there ?

    Please answer back.

  • February 11, 2009 at 11:56 pm

    #942634

    The problem is like this.

    I have a really complex logic in my application.

    It takes nearly 30 mins to complete.

    I want to reduce that time.

    I have identified indexes to be created.

    But i dont know the effect of these indexes on other transactions.

    So i want to create these indexes dynamically inside the process and then after the process i want to drop them.

  • February 12, 2009 at 12:20 am

    #942641

    Listen,

    It sounds to me that you need to get deeper in the running sql code and not playing with the indexes, 30 minutes for a batch it sounds me as a bad developed code inside, stop with the index issue for now.

    Please try to understand why your batch is running so much, ask yourself questions like if there are big sort of data or if there are big joins with derived tables, check existing triggers on the updated tables, check if you have foreign keys toward other tables without indexes on those fields.

    Also check if you run on a very large amount of data and trying to update all of them together in one transaction, if this is the case you should use a temp table with little chunks.

    You see now the picture, don't run immediately to the indexes.

    Tell me what you found, don't run to change the existing indexes !!

  • February 16, 2009 at 11:33 am

    #944750

    It might take longer to create the index on the fly than the transaction is using at this time (30 minutes).

  • February 16, 2009 at 9:30 pm

    #944931

    aravind (2/11/2009)

    Hi

    how do i view transaction logs in sql server?

    Thanks in advance.

    you can use this

    http://www.apexsql.com/sql_tools_log.asp"> http://www.apexsql.com/sql_tools_log.asp

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • June 6, 2012 at 4:45 pm

    #1497953

    I'm weighing in on this discussion later on because I really do believe that dynamic index creation is a necessity in some circumstances.

    In my case I am processing reasonably large amounts of historical data (3.5mm rows) for a virtual data warehouse and I use an SP to handle repeated update statements.

    The problem is that every time an update statement is executed in such a batch, it performs an "index update" on all indexes that are impacted by set clause of the statement. That is a big performance hit. And so I found the process runs a lot faster if all indexes are dropped as of the start. Then I create the ones I need only when they are needed.

    The design schema is fairly good and so there are no other visible areas for improvement that I can see other than indexes-on-the-fly.

    Just wanted to put in my 2 cents about it, because it seems like the whole concept of dynamic indexing is getting short shrift in the SQL forums in general.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply