[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