Gossamer Forum
Home : Products : Gossamer Links : Discussions :

performance question

Quote Reply
performance question
Hi,

I have a Plugin where I get Prices for products. I store the price history so there are always multiple prices for every product. There are two ways and I am a little worried about performance. I would love to hear opinions and comments.
I have a .cfg file which is generated with arrays where the newest array is the newest price. The file is loaded during the update of the pricelist for every product.
So it is only:

Code:
my $config = GT::Config->load($dir . "/$data->{SupplierID}/$data->{'GTIN'}/price.cfg",
{create_ok => 1});
my $Price = $config->{NetPrice}[0]->{NetAmount}

And I have a table with all prices which I acces like this at the moment.
Code:
my $KIP = $DB->table('KIP');
$KIP->select_options ("ORDER BY Date DESC LIMIT 0,1"); #order by date DESC
my $kip_cond = GT::SQL::Condition->new();
$kip_cond->add('GTIN' => '=' => $data->{GTIN}); # item key = primary key with SupplierID
$kip_cond->add('SupplierID' => '=' => $data->{SupplierID}); # supplier key= primary key with GTIN

my $sth = $KIP->select('*', $kip_cond) || die $GT::SQL::error;
my $Price;
while (my $local_kip = $sth->fetchrow_hashref) {
$Price = $local_Kip->{NetAmount}
}
The .cfg file is much quicker and I wonder how I could improve the mysql way.

Regards

n||i||k||o

Last edited by:

el noe: Feb 11, 2015, 3:29 AM
Quote Reply
Re: [el noe] performance question In reply to
Performance wise, try not to use GT::SQL::Condition unless you really have to (as it just makes more work for the code). So try:

Code:
my $KIP = $DB->table('KIP');
$KIP->select_options ("ORDER BY Date DESC LIMIT 0,1"); #order by date DESC
my $sth = $KIP->select( { GTIN => $data->{GTIN}, SupplierID => $data->{SupplierID} }) || die $GT::SQL::error;
my $Price;
while (my $local_kip = $sth->fetchrow_hashref) {
$Price = $local_Kip->{NetAmount}
}

Alternatively, if you trust the values being passed in via $data->{GTIN} and $data->{SupplierID} , then I would just go for a do_query, as again, thats quicker (especially with larger data);

Code:
my $Price = $DB->table('KIP')->do_query(qq|SELECT NetAmount FROM glinks_KIP WHERE GTIN = '$data->{GTIN}' AND SupplierID = '$data->{SupplierID}' ORDER BY Date DESC LIMIT|)->fetchrow || '';

Angelic

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] performance question In reply to
Hi Andy,

Thanks a lot, makes sense. I did not look at it that way yet.
Tried to move my queries to GT::SQL syntax wherever I could but there are a lot of queries I do trust and using do_query seems smart for these cases.

Cheers

n||i||k||o
Quote Reply
Re: [el noe] performance question In reply to
Hi,

Yeah. When I was working on a large forum, doing searches on hundreds of thousands of records, using do_query worked out about 1000% times quicker (not an issue with GT::SQL, but a placeholder performance issue in mySQL itself)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!