Gossamer Forum
Home : Products : Gossamer Links : Discussions :

performance difference do_query prepare + execute

Quote Reply
performance difference do_query prepare + execute
Hi there - probably hi Andy, Robert and Vishal,

I am working with the optimization of queries and wonder if there is an effect if I use
Code:
sub {
my $brand_id = shift;
my $query = qq|SELECT
Contact_Email
FROM glinks_Links
WHERE
BrandID = ? LIMIT 0,1|;
my $sth = $DB->table('Links')->prepare ($query) || undef;
$sth->execute ($brand_id);
return $sth->fetchrow_hashref;
}
over
Code:
sub {
my $brand_id = shift;
my $query = qq|SELECT
Contact_Email
FROM glinks_Links
WHERE
BrandID = ? LIMIT 0,1|;
return $DB->table('Links')->do_query($query) || undef;
}

While the second is shorter as far as I know it is better and probably faster with mySQL to use placeholders. I yet not figurered out if LinksSQL does call mySQL with placeholders or if both of the above lead to the same request and placeholder micht be more secure.

Thanks for your input

n||i||k||o
Quote Reply
Re: [el noe] performance difference do_query prepare + execute In reply to
... where the second is:

Code:
sub {
my $brand_id = shift;
my $query = q|SELECT
Contact_Email
FROM glinks_Links
WHERE
BrandID = | . $brand_id . q| LIMIT 0,1|;
return $DB->table('Links')->do_query($query)->fetchrow_hashref || undef;
}
forgot to remove the placeholder and fetchrow_hashref Angelic

Last edited by:

el noe: May 6, 2021, 3:38 AM
Quote Reply
Re: [el noe] performance difference do_query prepare + execute In reply to
Hi,

Are you asking if your 1st or 2nd query is faster?

BTW I would personally do:

Code:
sub {
my $Links = $DB->table('Links');
$Links->select_options("LIMIT 0,1");
return $Links->select( ['Contact_Email'], { BrandID => $_[0] } )->fetchrow || undef
}

That gets around placeholders, and any potential SQL injection issues as well then

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!

Last edited by:

Andy: May 6, 2021, 8:06 AM
Quote Reply
Re: [Andy] performance difference do_query prepare + execute In reply to
Hi Andy,

Thanks for your reply. The question was indeed which way would probably be the fastest.
The request is just an example of many amd which in this case probably could be done nice the LinksSQL way in your example.
Regarding this the question is which of the three ways could be the fastest/best.
As far as I have understood doing the same queries with placholders leads to a one cached query in mysql whereas my example without placholder would be a unique query for every case.
Not sure if I got that right.
If that is correct the question is if the now three examples do have different outcomes in LinksSQL and in the way they are handed over to mySQL.

Regards

n||i||k||o
Quote Reply
Re: [el noe] performance difference do_query prepare + execute In reply to
Placeholders don't add speed as far as I'm aware. They just stop someone doing a SQL injection into your query (or breaking the query).

For example, if "Brand" was: 'the "future" starts now', then it would create the query:

Code:
SELECT * FROM glinks_Links WHERE Brand = "the "future" starts now";

Which would obviously break the query. Someone more malicious could use that to hijack your query and do stuff in the DB

If you are happy the query is sanitized, I would just stick with something like:

Code:
my $brand_id = shift;
my $query = qq|SELECT
Contact_Email
FROM glinks_Links
WHERE
BrandID = "$brand_id" LIMIT 1|;
return $DB->table('Links')->do_query($query)->fetchrow || undef;

Unless you are doing some serious amount of queries (hundreds or thousands per page), then I don't think you will find it makes a difference. Obviously make sure you have an index on BrandID as well though!

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 difference do_query prepare + execute In reply to
Hi Andy,

Thanks for your feedback. The queries are called with INT results from previous queries so it should be safe enough.
Nevertheless no risk is better than as good as no risk.
For the speed I am quite happy with the performance of the webpage which is hosted on a Carbon60 previously GT Server.
I was more into some finetunig and there are diskussions that queries with placeholders are better for caching than unique queries for every request.
Then again I read that there are no more queries cached by mySQL.
I am a little lost with the whole thing but in the end it works as it is and I will do my cleanup anyway.

Regards

n||i||k||o