Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically Creating Indexes


Dynamically Creating Indexes

Author
Message
aravind-427817
aravind-427817
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 247
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
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 247
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1652
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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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