I'm running into the age old problem with the battle between optimizing your code and still keeping it readable. I read somewhere that using whitespace in SQL queries is really bad as it take a lot more bandwidth and puts more stress on SQL Server parsing the SQL it is sent. Right now I have a query like so:- So the question I have is, does it really matter how the SQL query is sent? I mean, if I put it all on one line (which would kinda suck as it is harder for me to read then), would it speed up transactions significally?<?php
$selQ
= 'SELECT
n.pknewsID AS newsID,
n.title,
n.full_text,
n.publish_up AS datePublished,
CONCAT(u.fname," ",u.lname) AS author,
i.loc AS img_file,
i.descr AS img_caption
FROM tblnews n
LEFT JOIN tblusers u
ON n.fkcreated_by = u.pkuserID
LEFT JOIN tblnews_images i
ON i.fknewsID = n.pknewsID
WHERE
n.pknewsID = '.$articleID.' AND
n.published = 1
LIMIT 1';
$
selQ
=
$DB->setQuery($selQ);
/**
* $DB->setQuery basically is a preg_replace function that
* removes all the tabs in the query string, and replaces them
* with a single space.
*
*/echo
$selQ;
/**
* Printed out it looks like this :-
* SELECT
* n.pknewsID AS newsID
* n.title
* n.full_text
* n.publish_up AS datePublished
* CONCAT(u.fname," ",u.lname AS author,
* i.loc AS img_file,
* i.descr AS img_caption,
* FROM tblnews n
* LEFT JOIN tblusers u
* ON n.fkcreated_by = u.pkuserID
* LEFT JOIN tblnews_images i
* ON i.fknewsID = n.pknewsID
* WHERE
* n.pknewsID = 6 AND
* n.published = 1
* LIMIT 1
*
*/
Whitespace does take up more space and increase the network packet size, but I would prefer readability over trying to minimize whitespace. It does not adversely affect the parser.
?>
If you are worried about packet sizes, use stored procedures since a proc name is shorter then a query or batch. Also, use parameterized queries in general since the server can efficiently cache them and avoid compiling the query. This would be a bigger performance savings in your case above.sql
No comments:
Post a Comment