Page 1 of 1

SQL Optimization

Posted: Fri Aug 08, 2003 10:59 pm
by fractalvibes
Kind of open-ended question, but a thought I had - aside from the obvious, I wonder if there are any such things as 'universal' ideas as to database sequel query optimization? Thinking the answer is 'it depends',
as a lot depends upon the underlying database engine, and how it determines/implements the plan by which it will access the database. Most of my dealings with the issue are work-related with ASP/ADO/DB2, don't know how the info we are given applies to the PHP/MySQL or PHP/Whatever DB worlds. I think it an interesting and important topic, since many of our apps depend greatly on fast, accurate access to a data store of some kind. If certain techniques can reduce download/processing time for the app by some magnitude - great!!!

Any thoughts?

Phil J.

Posted: Sat Aug 09, 2003 10:05 am
by nielsene
No universal as everything is a tradeoff, but good things to look at are:
indexices for commonly restricted,sorted, or joined clauses -- especially in a read heavy environment
profiling alternate formulations of queries (for instance I got a factor of 10,000 speed up when I changed an OR'd join into a union).

Combining similiar queries into larger queries if possible. The DB is faster than you, the DB is faster than PHP, so making it do more of the work is good.

Posted: Sat Aug 09, 2003 11:12 pm
by fractalvibes
Yes, agreed 100%. One of our developers went to a conference featuring Susan (Sharon?) Larson, a DB2 expert. He came back with some handouts from the conference. For example, in your `where` clause, using the most restrictive predicates first is supposed to provide a performance boost. Cool! This is true for DB2. Would the same hold true for MySql or MS Sql Server or PostGres? That may be a database engine-specific issue, as I would think that each has their own proprietory method of determining the access plan to get to the data most efficiently. Just don't know. Can one run `explains` on an sqlstring from a PHP script from PHP? That would be interesting to see.

Phil J.

Posted: Sun Aug 10, 2003 10:26 am
by nielsene
Typically speaking that sounds like it would work. DBMS's are free to rearrange the order of the where clauses, and probably will in simple queries to intermediate queries. As the queries become more complex, (many table joins, many restrictions) the search space becomes too large to quickly plan and I imagine many systems fall back to the in order evaluation of the where clauses.

Posted: Sun Aug 10, 2003 11:55 pm
by fractalvibes
I suspect you are correct here, and that is why there is much more emphasise on education and SQL effeciency these days. Until a few years ago, our DB2 DBAs only had to deal with static SQL. I.e. cobol programs accessing the database went through a precompile, compile and DB2 bind process. In the bind process, the DB2 Optimizer figured out the access plan it would use - what indices, if any, tablespace scan, index scan, etc. etc. and stored this in a package. Thinking that DB2 can do query rewrites, as you say, to figure a better way.

In this day of webdev and dynamic SQL, the database has to determine this on the fly, so we really need to do a better job as developers in developing our SQL as efficiently as possible. Adding the proper indices is important, but if the developers insist upon doing select * from mytable....

Guessing the many flavors of SQL are treated/optimized differently depending upon the DB engine, while there might be a subset of `truths`that hold true across the board. The material I read from the Susan Larson seminar was awesome - she can write sequel to replace entire applications...just awesome!

Phil J.