Update, Insert, Delete with Indexes -> Waits, without indexes -> nowaits

Question

Update, Insert, Delete with Indexes -> Waits, without indexes -> nowaits

christian_t

Hall of Fame

Points: 3588
More actions
April 20, 2010 at 10:45 am

#223553

Hi guys,
Let's say a have that I have a table that is queried by an SQL (select with some joins) in an ETL Dataflow.
The result-set is used to perform deletes, update and inserts in that particular table.
However I discovered in the activty monitor, that there where a lot of waits for this jobs.
When I stripped the index from that table the ETL was faster and the activity monitor showed no waits.
However I think that the SQL would be quite faster with indexes but with the waits it is in total slower.
I read that if the wrong index is used for the query there can be locks while update the same table (and the locked index) and also that parallism in an execution plan indicates that there may be some optimising in the indexes.
But the Execution plan and the Tuning Advisor don't give me any hints on what to do better.
Is there a definition how to create an index, so an delete, update, insert does not lead to a wait-situation?
kr
Mitch

Viewing 6 posts - 1 through 5 (of 5 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

In general indexes increase SELECT performance but any kind of insert/update/delete activity requires the data AND index be modified so there is a cost there. So by taking the index off you cut down on the updates to it. How is your dataflow structured, does it try to do all these actions at once or what?

CEWII

christian_t Hall of Fame Points: 3588 More actions · Answer 2

The dataflow is working like this

SELECT --> conditional split --> Update --> union all --> insert

--> Delete -->

In the dataflow you can see that while the first 10.000 rows passed the update and are processed in the insert the next 10.000 are processed in the update.

So I guess the update and insert may try to lock the same pages/pages on the indexes and there you have wait.

I was thinking about a staging solution, meaning the I write all delete/updates/insert in temp-tables so in this dataflow does not use the same tables that are selected. In the next step I would write 3 sql-statements that execute the deletes/updates/inserts.

Is there a better way to do it?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I tend to do the deletes first, then the updates, then the inserts.

What are you using for the updates and deletes? OLEDB Command Transform?

Also what is the average mix of I/U/D?

Also, how do you know which action to perform?

CEWII

christian_t Hall of Fame Points: 3588 More actions · Answer 4

I looked it up.

There are just a few deletes < 10.000.

But for every row I update there will be an insert row to.

In one ETL there will be about 250.000 Updates and 270.000 Inserts.

As this ETL is processing in a table in a DWH the updates will set the valid_to date

and the insert will put in the new data.

Originally I builded it one dataflow with OLEDB Command Transform for delete/update and an oledb destination for inserts.

As I posted before I was thinking to insert the transformations into temp-tables and then us an "execute sql task" to delete update and insert the changes.

>> Also, how do you know which action to perform?

The select of the source of the dataflow provides the information so I can do a conditional split in the flow.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

That transaction mix may change my thoughts. Let me think about it some more.. The one-for-one with the updates changes things.. That feels like a type 2 SCD..

CEWII