Get current value in real time in table

Question

Get current value in real time in table

Navigation

#1 by (0 votes)
#2 by (0 votes)

3

I have the following problem, I am currently working on a pricing system with SQL Server database, in which I have to work Dynamic pricing .

For which I have the following table.

ID_TARIFA : Primary Key Auto-increment.
ID_PRODUCTO : Identifier of the product sold.
PRECIO : Product price.
FECHA_INICIO and FECHA_FIN : Range of dates where the price is available.
CANTIDAD : Indicates the quantity of products that can be sold at a certain price.
ACTIVO : It tells me if the price is active or not.

I put this information as an example:

The problem is that there are many products and many fees and there is a need to consult this information in real time.

Taking into account this information, a first set of tests was carried out in which:

A field was created in the table that counted the number of products sold at the indicated price VENDIDOS .
A process was created that every time a product was sold it updated the aforementioned field VENDIDOS .
Finally, a job was created that was executed every five minutes to update the active prices.

The result of these first tests was that updating the table when selling a product and in turn, after five minutes with the job, ended up blocking the system so that it could not continue to be sold, so it was discarded.

Then in a second set of tests:

The VENDIDOS field was changed to a calculated field that executed a function to obtain products sold.
The ACTIVO field was changed to a calculated field that executed a function that based on the parameters returned if the record was active or was not active.

Finally, this second set of tests worked well in a backup environment, however, when it was raised to production, the registration of products sold and the amount of calculations that were made at the time of the consultations ended up collapsing the processor.

The question is, what would be the best way to implement this validation?

I have the theory of eliminating the field ACTIVO and always make queries in hot, with WHERE , AND , and verify the tickets sold at that moment. However, I do not know if it's the best approach.

Edition 01 / 26-10-2016

The size of the tables is as follows, in approximate amounts:

Sales Table - 120,000,000 (Arox)
Stock Chart - 6,000,000 (Arox)
Price Table - 6,000,000 (Approx)

The number of active connections varies between 100 and 2000 in the periods of greatest demand.

The indexes of all the tables are optimized, through analysis tests of the execution plan and SQL Server Profiler.

To show the products they are listed through filters that do not have greater complexity or delay, however it is by consulting the "dynamic prices" (There is always one for each product) where the saturation occurs.

sql-server real-time

asked by Svalinn 26.10.2016 в 04:24

source

2 answers

facebook login android Run a Jasper Reports report from a Python script

score 0 · Answer 1

Encapsulate the operations and block the registration from SQL with BEGIN TRANSACTION / COMMIT as explained on the Microsoft website:

link

So that when you do the update and addiction, you are sure that there is nobody reading from an obsolete registry. The general idea is to set a traffic light, so that you are sure during the update of the records that you are not reading an obsolete data.

score 0 · Answer 2

I have a question about the quantity, if the total of sold is equal to the quantity, then what happens ?, can not be sold at that price, then what is sold ?, I guess then that Id_Tarifa is not only the PK, if not also the order to "use" the prices.

Having the Field Vendidos and the process that each time a product was sold updated that field, seems to me the best option.

The SQL query considered it more optimized than Calculating the "Active" Price in each change or query, and it would be something like that (based on a given article):

Select Top 1 Precio 
From [TabladePrecios] 
Where GETDATE() Between Fecha_Inicio and Fecha_Termino 
And GETDATE() <= Fecha_Limite
And Cantidad > Vendidos
And Id_Producto = [IdProducto]
Order By Id_Tarifa

I also recommend maintenance tasks, which move (copy and delete) records that take the role of historical (will not be used again in daily practice), in order to have fewer records in the tables that They are used continuously.

In parallel, if my assumption of Id_Tarifa is correct, and you would like a table / view with all active prices at the same time, the query (which can not use the Top 1 next to the Order by ) occurs is not at all optimized, using the clause Exists , and it would be like this:

Select Id_Tarifa, Id_Producto, Precio, Fecha_Inicio, Fecha_Termino, Fecha_Limite
From [TabladePrecios] TP
Where GETDATE() Between Fecha_Inicio and Fecha_Termino 
And GETDATE() <= Fecha_Limite
And Cantidad > Vendidos
And Not Exists 
    (Select *
    From [TabladePrecios]
    Where GETDATE() Between Fecha_Inicio and Fecha_Termino 
    And GETDATE() <= Fecha_Limite
    And Cantidad > Vendidos
    And Id_Producto = TP.Id_Producto
    And Id_Tarifa < TP.Id_Tarifa
    )

On the other hand, the same query but using the subquery completely to reach the Id_Tarifa currently used, it may be slower, and it would be like this:

Select Id_Tarifa, Id_Producto, Precio, Fecha_Inicio, Fecha_Termino, Fecha_Limite
From [TabladePrecios] TP
Where GETDATE() Between Fecha_Inicio and Fecha_Termino 
And GETDATE() <= Fecha_Limite
And Cantidad > Vendidos
And Id_Tarifa =
    (Select Min(Id_Tarifa)
    From [TabladePrecios]
    Where GETDATE() Between Fecha_Inicio and Fecha_Termino 
    And GETDATE() <= Fecha_Limite
    And Cantidad > Vendidos
    And Id_Producto = TP.Id_Producto
    )

If the number of queries is considerably greater than the number of Updates, it is considerably more optimized to use a " Persisted View " based on the queries of subsequent "Assets" instead of the "Article to Article" query that you raise first. Theoretically this table will be reconstructed every time one of the fields related to the query are modified, but it will be a "physical" table, so the queries to it do not depend on the delay in building it, if it can delay the first query after a Update of TablaDePrecios until its reconstruction.

This does not mean that the maintenance tasks of the price table are one of the greatest points to optimize, however, if for some reason it could not be carried out, use compression in the tables.

Otherwise I ask you to clarify the behavior to be performed between Cantidad and Vendidos .