Inserting 30k prices each minute, how can I skip an entry if the price did not change?

I receive around 30k price updates for various articles every minute and I need to write the new prices into our database (Which is a MySQL Database running InnoDB)

The table containing the prices right now has only three columns: Article_id, InsertTime, and ArticlePrice

My Problem is: I do not want to insert duplicate prices, but I want to store historical data. So when the price goes up or down I insert it. When it stays the same I skip it. Since I receive a lot of data I can not query first and then decide if I can insert or not, as this approach will be too time consuming.

Can this be solved on a database level? For example by adding a time column and using it as a primary key?

I also thought of solving it in the application itself, for example by keeping a List of the previous prices and compare them there before inserting into the database, but I want to guarantee a consistent database and this seems complicated.

Any help is appreciated.

Replay

Apologies, don't do MySQL but hopefully the approach can be adapted from this SQL Server example. For SQL Server I'd use OUTER APPLY / TOP 1 to get the latest insert per article but MySQL doesn't appear to support it.

Create a table as per your schema and a stored procedure for the insert:

USE tempdb;
GO

BEGIN TRY DROP PROCEDURE dbo.ArticlePrice_Insert END TRY BEGIN CATCH END CATCH
GO

CREATE PROCEDURE dbo.ArticlePrice_Insert
(
    @Article_id INT,
    @InsertTime DATETIME,
    @ArticlePrice DECIMAL(18, 2)
)
AS
BEGIN

    INSERT
        #Article
        (
        Article_id
        , InsertTime
        , ArticlePrice
        )
    SELECT
        vars.Article_Id
        , vars.InsertTime
        , vars.ArticlePrice
    FROM
        (
        SELECT
            Article_id = @Article_Id
            , InsertTime = @InsertTime
            , ArticlePrice = @ArticlePrice
        ) vars
    LEFT JOIN
        (
        SELECT
            a.Article_Id
            , a.ArticlePrice
        FROM
            #Article a
        INNER JOIN
            (
            SELECT
                Article_id
                , InsertTime = MAX(InsertTime)
            FROM
                #Article
            GROUP BY
                Article_id
            ) LastInsert
        ON  LastInsert.Article_id = a.Article_id
        AND LastInsert.InsertTime = a.InsertTime
        ) LastPrice
    ON  LastPrice.Article_Id = vars.Article_Id
    WHERE
        vars.ArticlePrice != ISNULL(LastPrice.ArticlePrice , -1)
END
GO

BEGIN TRY DROP TABLE #Article END TRY BEGIN CATCH END CATCH

CREATE TABLE #Article
(
    Article_id INT,
    InsertTime DATETIME,
    ArticlePrice DECIMAL(18, 2)
)
GO

Insert two articles, then existing article with different price, followed by existing article with same price:

SELECT 'Starting Data'

EXEC dbo.ArticlePrice_Insert 1, '2016-07-29 10:00:00', 1.0
EXEC dbo.ArticlePrice_Insert 2, '2016-07-29 10:01:00', 1.0

SELECT * FROM #Article

SELECT 'Existing article, different price'

EXEC dbo.ArticlePrice_Insert 1, '2016-07-29 10:02:00', 2.0

SELECT * FROM #Article

SELECT 'Existing article, same price'

EXEC dbo.ArticlePrice_Insert 2, '2016-07-29 10:02:00', 1.0

SELECT * FROM #Article

Category: mysql Time: 2016-07-29 Views: 3

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.222 (s). 12 q(s)