Re: [SQL] avoid update - insert churn

On 03/05/2017 10:20 AM, Kevin Duffy wrote:

Hello All:

I am looking for suggestions on how to optimize a relatively simple problem.

Say I have a group of shoe stores.  The store report sales daily and
daily I
calc stats off on the sales numbers.  Ie rolling averages & volatility
numbers, with a couple of different look backs.

I am storing the raw sales figures in one table and the calc'ed stats in
another.

Here is the interesting part,  it is possible and it does happen that a
store will not
report sales for a day or two or prior figures reported will get revised.
There is at max a five day window on this.

For the prior dates there are two possibilities, I will be updating
a record or inserting a new record in sales table.  For the current
date's sales it will be all inserts.
Currently what I do is loop through the data by store and shoe style
attempt an update and if that fails
do an insert.

Same deal for the stat's table except there are multiple records for
each Store/Shoe style pair.


So here is my question/issue:  Is this there a better design pattern for
doing this?
I could gather up the data for the prior dates and do one "bulk"
update.  But that would
fail for sales that have not been reported todate.
The current date's sales are of course an insert.  And I could fix this
to be one "bulk" insert.

Was just chewing this over in my heading wondering if there is a more
efficient way to this.
Other than attempt update if fail insert.  Seems to be a bit of churn to me.

You do not say what version of Postgres you are using, but if 9.5+

https://www.postgresql.org/docs/9.5/static/sql-insert.html

"ON CONFLICT Clause

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action."

It wraps the INSERT/UPDATE as an UPSERT in one command.

But at the end of the day, it do believe Keep It Simple.

Many thanks for considering this problem.

KD

--
Adrian Klaver
adrian.klaver@aklaver.com