Post reply

Small impact script inside a trigger to check index fragmentation on a table and, if over a threshhold, reorganize

  • May 6, 2011 at 9:53 am

    #239766

    Hi, I'm loading a large amount of data from a SSIS package into an empty database. I have triggers on various tables that do lookups to ensure that multiple values aren't being inserted more than once. This works beautifully and quickly for all of my imports, save one, where the unique values is, unfortunately, a string. I've put an index on this column but it, for obvious reasons, quickly becomes fragmented. I'm looking at two possibilities...

    *Remove the unique string value from the main table and then create a PK/FK relationship between the two so that the lookup is being done against an INT rather than a a string. I'd prefer not to do this as these string values are completely unique and, once indexed fully, should be fairly quick to lookup, and I don't really relish creating a one-to-one relationship in such a scenario. Call it personal preference...call me wrong.

    OR

    *Try to find a low-impact script (at least, as low-impact as possible) that I could append to my trigger which would check the indexes on a certain table (or even better, just the index that I'm concerned with, IX_PERMID) for fragmentation levels and, if over a certain threshhold, do a ReOrg.

    Either way, this feels hackish, but I'm just trying to get this initial import out of the way.

  • May 6, 2011 at 11:28 am

    #1321192

    put a unique constraint on the column and just do a try catch around your insert if you are doing them one at a time.

    Otherwise, perhaps insert them all to a different table and then just go get the ones you need after you are done and put them into the final table using something like Select Insert into FinalTable (COLUMNS) select StageTable.* from (Select NAME, min(ID) as ID from StageTable group by Name) ABC where ABC.ID = StageTable.ID

    Either way, I'd dump the trigger. That trigger is costing you a lot if you are doing big inserts into that table, even if the trigger does nothing at all. Much better to handle it in your ETL with a set-based solution like #2 above.

Viewing 2 posts - 1 through 1 (of 1 total)

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