[thelist] Dynamic vs. static SQL in large search screens

[david.landy at somerfield.co.uk]

John,

I'm no expert on this, but here's my 2c worth :-)

If you don't have to rip your app apart to do it, I'd recommend adding a
layer to your design, so that you have a 3-tier approach:

- Presentation layer, eg ASP/JSP/PHP/whatever
- Logic and data abstraction layer, eg Java classes/COM objects ("the middle
tier")
- Database layer

The middle tier interfaces between the database layer and the presentation
layer and takes all db code/logic out of your presentation layer. The middle
tier is the ideal place for your dynamic search query logic.

However, if you've already got a 2-tier site (sounds like you do) then I
wouldn't bother ripping it apart just for this ;-), too much hassle for too
little benefit. If this is the case then I'd back up Josh's advice to put
the logic in the presentation layer. I've written plenty of 2-tier apps and
they work just fine; it's taken me years to see the value of the 3-tier
approach, but now I've cut my first one I won't look back!

In terms of optimization, can you have a combination of a simple search and
an advanced search on your page? If so, you could possibly get away with a
pre-compiled query for the (say) 90% of queries that are simple, and only
have the overhead of an on-the-fly query for advanced queries. This would
make a significant processing saving if querying is a frequently used
operation in your app.

HTH

David

-- 
 
If you are not the intended recipient of this e-mail, please preserve the
confidentiality of it and advise the sender immediately of any error in
transmission. Any disclosure, copying, distribution or action taken, or
omitted to be taken, by an unauthorised recipient in reliance upon the
contents of this e-mail is prohibited. Somerfield cannot accept liability
for any damage which you may sustain as a result of software viruses so
please carry out your own virus checks before opening an attachment. In
replying to this e-mail you are granting the right for that reply to be
forwarded to any other individual within the business and also to be read by
others. Any views expressed by an individual within this message do not
necessarily reflect the views of Somerfield.  Somerfield reserves the right
to intercept, monitor and record communications for lawful business
purposes.