Need a fast way to save a snapshot of MySQL table using PHP

[DeX]

I know of all the ways to dump it to a text file but I want to save a database table into another match table. I have web software which allows users to enter some inputs and save a create a quote for their client. At the time of the quote, all of the building products in the database have an associated price and this pricing can change at any time. When they save the quote, I want to save all 300 products and prices into another table so I can go back later and see what the prices were when that quote was done. I don't want to load a quote from a month ago and have it price based on current pricing, I want it to price using the prices from a month ago.

 

Right now I'm simply selecting all 300 rows and dumping them into a match table with the quote ID. That's causing it to take about 30-50 seconds to save the quote, along with a couple of other tables I'm saving as well. I'd like to make the quote saving much faster, is there a faster way to get this snapshot of the database table when the quote is saved?

[fastsol]

How about a quote items reference table. Just store the quote_id, product_id and quoted_price. All of which you should have the data for when submitting the quote. Then you don't have duplicated table data with the products and you just display the quoted price later if you need to view it.

[DeX]

How about a quote items reference table. Just store the quote_id, product_id and quoted_price. All of which you should have the data for when submitting the quote. Then you don't have duplicated table data with the products and you just display the quoted price later if you need to view it.

 

Yes, that's what I'm doing, it's just 4 columns:

- id

- quote_id

- product_id

- price

 

Then I also have to save the sales person's commission rates:

- operations commission

- sales commission

- sales management commission

- 4 or 5 other commissions

 

Then I save the rates:

- winter build rate (used if building in winter)

- remote labour rate (used if building far enough away from head office)

 

And then I save minimums

- minimum labour (used if labour is less than the minimum)

- minimum materials (used if materials are less than the specified minimum)

- minimum truss price (used if total truss price isn't at least the specified minimum)

 

These are all things required for looking back at older quotes and they all come from separate tables and get stored into separate match tables.

[mac_gyver]

didn't see this thread before.

 

the 'correct' way of doing this is to store the pricing information in a table using the productId, the start date (or datetime) and the end (or a NULL) date (or datetime.) each time the price changes for an item, you would set the end date (or datetime) for the previous price and insert a new row with the start date (or datetime) and the new price. any query to calculate the price would use the date (or datetime) of the quote to match the correct pricing information.

 

---

 

any chance that your thread about the switch/case statement taking a long time is doing a ton of database queries (in a loop) that could account for the bad performance?

Edited June 30, 2015 by mac_gyver

[Barand]

the 'correct' way of doing this is to store the pricing information in a table using the productId, the start date (or datetime) and the end (or a NULL) date (or datetime.)

 

Just my 0.02 worth, but if you store a date of 9999-12-31 instead of NULL in the end date of the current price then it makes joins easier when you want the price that was  applicable at the time of the transaction EG

SELECT .... 
FROM transaction
    INNER JOIN price ON transaction.date BETWEEN price.startdate AND price.enddate

[mac_gyver]

i was going with - ... BETWEEN startdate AND IFNULL(enddate,'9999-12-31')

[Barand]

Will that use the indexes?

[mac_gyver]

no.

[gizmola]

I agree with both mac and Barand, but more so in practice I've used Barand's method.  I like to call that 'max end date' the 'PERPETUITY_DATE'.  Makes for a nice constant.

 

Other benefit of that, is that you have a complete price history for any item, and there's often some nice analysis you can do with that data and visualizations like charting the price variation of items over time.

[DeX]

didn't see this thread before.

 

the 'correct' way of doing this is to store the pricing information in a table using the productId, the start date (or datetime) and the end (or a NULL) date (or datetime.) each time the price changes for an item, you would set the end date (or datetime) for the previous price and insert a new row with the start date (or datetime) and the new price. any query to calculate the price would use the date (or datetime) of the quote to match the correct pricing information.

 

---

 

any chance that your thread about the switch/case statement taking a long time is doing a ton of database queries (in a loop) that could account for the bad performance?

Wow, that is genius! I actually laughed when I read it because of how smart that is.

 

Because the system gets the overall price for the quote, it goes to the database 300 times to get the prices for all the different products with 300 individual database calls to the same table. I think it might only make the calls for products with a positive quantity though, now that I think about it.

 

EDIT - These calls are only done when the quote is saved, not on edits. I'm not concerned right now about speed of saving quotes, currently I'm focused on edits.

Edited July 1, 2015 by DeX

[mac_gyver]

if your bom/cart is memory/session based, to display it with pricing, you would extract all the productId's (since these are array keys now, you can use array_keys() to do this all at once.) you would then implode the array of productId's to make a comma separated list that will get used in ONE database query, using an IN(...) term in the WHERE clause, to retrieve all the prices for those productId's, and store the result in an array, using the productId as the array key. as you loop over the contents of the bom/cart to display it, you would use the productId to get the price from the array you just made from the single query.

 

if your bom/cart is database table based, you would just join the bom/cart with the pricing table, then just retrieve and display the results.