Update vs Truncate and Insert 1

[Moebius01]

Looking for opinions here. My company is switching to an Avaya phone system, so I'm having to update the realtime data collection/reporting system I manage.

With Avaya, their realtime socket will send all the data every 10-15 seconds. This will amount to around 350 to up to 999 rows of data with about 20-50 columns. (We have to design a report that the data pull will be based on...I really don't like Avaya

As this is realtime data, there's no need to store the previous update. So I'm trying to determine the best method to get the data in each refresh. I'm guessing I can either update the rows, or just wipe the table and re-insert each time. As this data has such a short life cycle, there's no logging on the db, and all queries allow dirty reads (nolock).

What would folks around here recommend for the best performance way to get the data into the db?

 

[amrita418]

How often does the report run? If you allow dirty reads then more often than not the report does not represent correct data, is that fine with you?

Regards,
AA

 

[NoCoolHandle]

If you are comparing the idea of an update to a truncate insert, I would actually advise you to "try each option"

Reason.. Trucnate table = 3 rows of Logging
insert = 1 per row + begin and end tran (logging)
However Bulk Load would really only add the same 3 or 5 rows of logging as it is a nonlogged opp.

Vrs an update. The update of your 999 rows wouldn't take too long if the update was an update inplace.. i.e. The rows are the same size and they don't need to be shifted from one page to an other. (this isn't as likley in my experence, but if it works it is only 1 row of logging per row of data vrs a min of 2 for a normal update (+ index reshuffel and rebuild). 1 for the insert and 1 for the update.)

Now ... why I said I would try both options.
after a truncate you still need to allocat pages to the table when the table is populated. this takes time. vrs an inplace update where the pages are already inplace and you don't need to shuffle the data to make it fit your index structure.

If the data going in isn't in the order of the index then it will probably result in fragmentation of your data and indexes.

Sorry if it just makes things muddy, but in your case I think the best option is to try both options and see which as the worst impact.

Rob
PS My guess is that in inplace update (with apprpriate index structures) might be faster.

 

[Moebius01]

Thanks for the suggestions. I'm thinking I'll try updates first as the rows will definately be static in size, and index value, so shifting shouldn't occur. Also, there's no logging, so that doesn't factor in here. In this particular case, logging is pointless. There would never be a need to restore the data as it's dead 10 seconds after entry. Same reason I allow dirty reads on this. The pages that pull this data run on a 30 second refresh, so at worst, the data would be 10 seconds too old.

 

[NoCoolHandle]

Moebius..

If you modify rows. It is logged. (even if you are not intending to have recoverabliliy - it just can't be turned off without non-logged opps like bulk copy or truncate table)..

1 other thought. Update should be much safer for what you want as "there will be data" when someone asks for a report. If you did a truncate, followed by a bulkinsert you would stand a chance of the data not being there.

Rob

 

[johnherman]

Generally speaking, and INSERT counts as 2 and an UPDATE counts as 3 based on a SELECT = 1. (DELETE is 2). This is a general rule of thumb, but as NoCoolHandle suggests, I would try each possible solution because constraints, logging, indexes, etc can influence your elapsed times.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[Moebius01]

My mistake, I assumed with a simple recovery model that the transaction logging did not occur. I'm guessing then that it just re-uses log space as needed instead.

In the case of trying the update method, is there anything I should set up special for indexes or the like? The data stored is pretty static in size (mostly integer values). In the end, I'll have one identifying column based on the split/skill number (1-999) and the switch ID concatenated, so obiviously the index will be on that, but is there anything else I should do to help things stay on the same page in the index?

 

[NoCoolHandle]

When updates come in where "where" clause should be indexed.
It won't matter if it is nonclustered or clusted as it sounds like is will be via a "series" of single row updates. based on a "Value" to Key type of update rather than a update all x to 23. However I would tend to advise you to make it nonclustered. Due to the nature of hte update being singleton.

CLustering should be done on a col that you often return sorted or as a range of values (range being the most important) , but be carefull not to have too manu cols in the clustering key as it will "puff up" all your other indexes.

HTH


Rob