Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamically Creating Indexes Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2010 11:32 PM
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.
Post #654521
Posted Wednesday, February 11, 2009 11:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:05 AM
Points: 55, Visits: 214
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.
Post #655422
Posted Wednesday, February 11, 2009 11:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2010 11:32 PM
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.
Post #655427
Posted Thursday, February 12, 2009 12:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:05 AM
Points: 55, Visits: 214
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 !!
Post #655436
Posted Monday, February 16, 2009 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 9, 2011 8:47 AM
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).
Post #657974
Posted Monday, February 16, 2009 9:30 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:54 AM
Points: 1,475, Visits: 1,630
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

Post #658182
Posted Wednesday, June 6, 2012 4:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 1:57 PM
Points: 3, Visits: 48
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.
Post #1312239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse