I often read in this forum that people have concerns about performance where database access is concerned.
I just added some query timing code to my production system.
To display the classified ad page shown here:
http://ocotillonews.com/class.htm
I utilize 134 database queries. Total elapsed time for database access: 0.0035 seconds.
This on a 2ghz pentium linux apache mysql server.
The following query -- an invoice query report -- executes in 0.0002 seconds. Note that it is a 5 table join, has multiple conditionals as part of the select and where clauses, group functions, etc.
All this in 0.0002 seconds. This query isn't brilliant or highly optimized: with today's fast servers you can afford some ragged SQL and still do OK.
SELECT concat('<a class=new href=index.php?action=adadjust&invoiceid=',invoice.id,'&adid=',adpayment.adid,'>',invoice.id,'</a>') 'Invoice Id', concat('<a class=new href=index.php?adview=',adpayment.adid,'>',adpayment.adid,'</a>') 'Ad id',
IF( invoice.accountid =1||invoice.accountid=17,
CONCAT( ' <a class=new href=javascript:receipt(', ledger.invoiceid,
')> Print </a> ' ), CONCAT( ' <a class=new href=javascript:extendreceipt(', ledger.invoiceid,
')> Print </a> ' )) 'Receipt'
,CONCAT( '<!',if(d.bizname<>'',d.bizname, CONCAT(d.lastname,
', ',
d.firstname )) ,'><a class=new href=index.php?action=showperson&adminperson=true&recordid=',d.id,'>',if(d.bizname<>'',d.bizname, CONCAT(d.lastname,
', ',
d.firstname )) ,'</a>' ) 'Customer ',
concat(' <a class=new href=mailto:',d.email,'>',d.email,'</a>') Email,
if(email<>'',concat('<input type=checkbox checked=true name=emailarray[] value=',email,'>'),'<BR>') 'Send',
invoicedate 'Date',
invoice.charge 'Charges',
invoice.payment 'Payments',
IF( invoicedate
BETWEEN now( )-INTERVAL 30 day
AND now( ),
charge-payment,
' ' ) '0-30' ,
IF( invoicedate
BETWEEN now( )-INTERVAL 60 day
AND now( )-interval 30 day,
charge-payment,
' ' ) '30-60' ,
IF( invoicedate
BETWEEN now( )-INTERVAL 90 day
AND now( )-interval 60 day,
charge-payment,
' ' ) '60-90' ,
IF( invoicedate < now( )-INTERVAL 90 day ,
charge-payment,
' ' ) '91+',
if(invoice.charge-invoice.payment>0,concat('<a class=new href=index.php?action=pay&adid=',adpayment.adid,'&invoiceid=',invoice.id,'>',invoice.id,'</a>'),' ') 'Enter Payment'
FROM invoice,
ledger,
person d,
adpayment,
ledger a,
account
WHERE account.id = ledger.accountid
and invoice.accountid=ledger.accountid
and invoice.id=ledger.invoiceid
AND d.id=adpayment.personid
AND a.invoiceid = ledger.invoiceid
AND adpayment.invoiceid = ledger.invoiceid
AND ledger.accountid in( 1,
13,14,27,28,
17,
18 )
AND ledger.publicationid=86
AND invoice.charge-invoice.payment>0
GROUP BY invoice.id
ORDER BY ledger.timestamp desc
You'll note that I use the query to process the aging information, rather than returning values and using PHP to do the processing.
IF( invoicedate
BETWEEN now( )-INTERVAL 60 day
AND now( )-interval 30 day,
charge-payment,
' ' ) '30-60'
You'll note that I don't create a separate column called 'balance due' for invoices, but use the database to calculate balance due (charge-payment) on the fly. This is because I'm secure that on the fly database calculations are faster than the programming required to keep a 'balance due' column updated and reconciled whenever payments or adjustments are posted.
I even include table formatting , checkboxes, and link formatting code directly in the query, rather than processing this information through php with raw returned values.
Based on my experience in this environment, I make it a practice to hand as much processing to the database as I can.
I particularly use the database for ALL my date processing, and will present a case for that approach at another time.
