[thelist] Too many queries?

Rudy_Limeback at maritimelife.ca Rudy_Limeback at maritimelife.ca
Fri Aug 25 11:40:25 CDT 2000


hi minh lee

> I will be writing a CF application and I'm afraid the 
> database may have to be quite large with a lot of tables. 

yeah, i hate it when that happens   ;o)

best strategy is to have a database with as few tables and rows as 
possible


> I'm also going to have to query a lot of tables at once for 
> some of those pages. 

there are strategies for this situation as well... i can probably help if 
you have questions about the sql, i'm real good with outer joins...

<tip>
in web/database applications, always look for opportunities to take 
advantage of server options like query results caching, i.e. don't 
re-query the database for stuff like product descriptions if the product 
hasn't changed since the last time somebody visited the page
   * if the last time somebody visited the page was maybe the day before 
yesterday, go ahead and run multi-table queries that retrieve product 
descriptions along with product codes --  your visitors aren't going to 
mind waiting, eh
   * on a high-volume site, the last time somebody visited the page is 
like a tenth of a second ago, so you want those product descriptions 
handy, and not have to go get them from the database every time
</tip>

this process, of deciding where, when, and how you do your database 
access, is part of data architecture 


> Is there a recommended limit before server performance gets compromised? 


this depends so much on individual installations that i can't offer 
anything but the vaguest generalities

if you are *experiencing* performance problems, you would normally address 
them in the following order

  1. does the box have enough horsepower
  2. is the o/s software configured properly
  3. did the developers know what they were doing
  4. how good is the database design

if you *wish to avoid* performance problems, you would normally address 
them in the reverse order

<tip>
to build a new application, first design your database -- all else comes 
after, and woe betide anyone who skips this step and starts coding 
</tip>


back when there were only mainframes, and ibm had the computer monopoly, 
there was a joke that "ibm" meant "install bigger machine" because, when 
called in to fix a performance problem, that was always their first 
comeback... which makes sense, because they sold hardware...

in today's world, installing a bigger machine is the easiest part of the 
equation -- servers are a commodity, as microsoft will happily tell you 
(not to pick on microsoft, it being only a coincidence that you need 
additional server software licenses for the additional servers...)

configuring the software is almost a no-brainer, too -- provided you have 
sysadmin staff you can trust who understand both o/s tuning and internet 
security

then there's the question of whether your developers knew what they were 
doing 

i have seen some really butt-ugly application logic over the years, and 
even a well designed database won't perform well if the application does 
stupid things 

this is not to insinuate that all applications are stupid, it's just that 
i've seen some doozies when it comes to database logic written by 
application developers

<aside for="aardvark">
   i hate to say this adrian, but i just don't get as warm a 
   feeling from former vb programmers using a low-level language 
   like asp against a database as i do from html developers 
   using a language like cf -- when it comes to designing clean 
   application logic in web pages that access a database, the 
   html side is "closer to" the data model and therefore
   more likely to know what they're doing than programmers who 
   see things in terms of "very simple" dll's or "VB code you 
   may have sitting around"
</aside>


just my opinion

... and i could be wrong, eh



rudy
r937.com





More information about the thelist mailing list