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