SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically Creating Indexes


Dynamically Creating Indexes

Author
Message
aravind-427817
aravind-427817
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 22
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.
Victor Shahar
Victor Shahar
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 249
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.
aravind-427817
aravind-427817
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 22
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.
Victor Shahar
Victor Shahar
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 249
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 !!
rburgess
rburgess
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 171
It might take longer to create the index on the fly than the transaction is using at this time (30 minutes).
Ahmad Osama
Ahmad Osama
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3615 Visits: 1660
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

Regards,
Sqlfrenzy
ze.emobile
ze.emobile
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 51
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search