Select from "inserted: very Slow

[eichmat]

I've got some triggers that fire on insert that is used to push a copy of each inserted record into an audit database. Lately, inserts into a particular table have started to bog down considerably (~7 seconds per row!).

Data is inserted into the table row-by-row (order picking detail records [where stuff was picked for ), and the on-insert trigger does an "INSERT INTO otherdb..table (fields) SELECT fields FROM inserted" (no where clause or joins).

When running a batch that inserts just 12 records, it takes nearly 90-seconds just to complete the audit triggers.

Any help would be greatly appreciated!

 

[SQLSister]

Row by row processing is bad. You want to do one insert not 12. Are you by chance using a cursor?

Post your trigger code. HArd to say how to optimize it without seeing code.

Questions about posting. See faq183-874

 

[eichmat]

I know row-by-row isn't the best, but this is a process that I inherited that I cannot change in the near-term.

The process itself is such: when picking an order, for each item in the order, the warehouse location to be used is determined based on some business rules, a call is made to the (separate) warehouse database to move the product from the location, a record is placed in my local table indicating from where the product is to be physically picked (later used to create a pick list). We could look to store what would be the local table entries in a temp table, then push them all in at once--but that only helps our automated picking process. Sometimes a user must manually select the locations to be used for each line of the order--once they've made their choices, a VB-loop calls the same SP the automated loop calls to perform the WHMS move and record the record in our table.

As for the contents of the trigger: (some names changed to protect the innocent)

ALTER TRIGGER T_Order_Picked_Items_Insert ON dbo.Order_Picked_Items
  FOR INSERT
  AS
    INSERT 
      INTO  AuditDB.dbo.Order_Picked_Items
           (_fields_, A_Date, A_User, A_Machine)
     SELECT _fields_, GETDATE(), USER_NAME(), HOST_NAME()
       FROM inserted
GO

Nothing fancy. the table contains about a dozen fields--most are either Int or Varchar fields (no blobs, nothing larger than vc-255), with one decimal (19, 9), one date/time and one bit field. Primary key (on the main table) is three int fields (site, order, sequence number). The destination is a heap with no indexes or keys.

This exact same trigger logic is used in the Insert-triggers of every table in the database (including the order-items table which has the same level of activity but superior performance). Only this table has the performance problems we are experiencing...

The WHMS has similar triggers as well for their auditing--and according to the Execution Plan in QA, their Location-table update trigger has a sub-tree cost of ~1.22, while my insert trigger has a sub-tree cost of 33.9 (the actual insert into the audit table is only 0.0108--the inserted scan is costing 33.85, so I know it isn't the destination data table since that is almost no-cost)!

We've checked the integrity of the database--no errors. Could indexing have an adverse affect on the FOR INSERT trigger? That's my only suspicion at this point, seeing how long that inserted scan portion of the trigger is taking.

Thanks in advance for any help.

 

[SQLSister]

Do you by chance have a clustered index? maybe it's slowingdown because it has to continually rearrange the data?

Questions about posting. See faq183-874

 

[eichmat]

Ooo... by golly there is a clustered index on a non-key field (part number)... Most likely put on sometime ago for some type of reporting.

I'll have to have that changed to non-clustered over the weekend (the database is also replicated and I've got to halt replication before changing the index), then rebiuld the indexes (the only other one is the PK which is not clustered).

Thanks. I'll let you know Monday if that helped.

 

[SQLSister]

Be awre that this change might unaccepatbly slow down the reporting side of things. These things are a trade-off.

Questions about posting. See faq183-874

 

[eichmat]

Oh, I understand that--but I have a feeling that the ability to pick large orders in under a minute instead of the 10-minutes it takes now may take priority over some slower reporting...

Thanks again.