homepage Welcome to WebmasterWorld Guest from 54.161.192.130
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Large database to database querying and storing estimated values
tec4



 
Msg#: 4398442 posted 10:34 pm on Dec 15, 2011 (gmt 0)

Hi everyone,

Trying to figure out exactly how to implement this current project to derive estimates from 1 db to another and stores the estimated value in one of the databases. I currently have, what I think is a pretty hacked version of it and works but does not seemed optimized at all and is pretty darn slow...what i have currently is below:

I have 2 tables in my database: one for previously sold products (approx. 600,000 rows) and one for items not yet for sale (approx. 50,000 rows and growing).

What I would ideally like to do is present the site user with an estimated value/sale price of the items that are not currently for sale based on parameters matching/querying against the database for the previously sold items.

I currently have this working on the fly but since it has to query the database on every page load/item they visit it is quite time consuming in regards to page load time (though I’m sure my query could be optimized quite a bit…)

So, I was playing with the idea of possibly, weekly or bi-monthly, looping through the data and creating estimates for each item and storing the estimated value with in a column with a corresponding “last modified estimate” date to query against.

My problem is that I’m really not sure how to set something up w/ this size to make it as optimized as possible…

Example of how I am currently querying:

-search.php stores my class variables and search parameters and initiates the query and stores the "$count"
-calculate_item_value.php calculates, in php, the results and averages an estimated value base on query results


 $previous_start_time = file_get_contents("last-run");

$items = Items::getItems($previous_start_time);

foreach ($items as $item) {

//stores beginning search parameters to be inserted
//and used in queries
$percentSearch = 0.20;
$radius = 0.25;
$yearbuiltrange = 10;
$soldtime = '-4 months';
require("search.php");

//checks if query results is 3 or more....
//if so use these items to calculate estimate
if ($count >= 3) {

//simple calculation in php from the 3 or
//more query items to derive value and
//also Updates Mysql row for estimate value
require("calculate_item_value.php");

}
else {

//increase search parameter ranges
$percentSearch = 0.25;
$radius = 0.5;
$yearbuiltrange = 12;
$soldtime = '-8 months';

require("search.php");


if ($count >= 3) {

require("calculate_item_value.php");

}
else {

$percentSearch = 0.30;
$radius = 0.75;
$yearbuiltrange = 15;
$soldtime = '-2 years';

require("search.php");

if ($count >= 3) {

require("calculate_item_value.php");

}
else echo "Estimate Failed";
}

}

}



How do really large sites handle creating estimates or similar things on their sites and databases? This just seems very VERY slow and un-optimized. Have been thinking that I might be able to use 1 query w/ a broader set of criteria and just use PHP to calculate using the search parameter variables but haven't tried that yet...that is probably next on my list.

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved