[ic] interchange and SQL DBMSes: /scan/ fetches anything?!
jonc at webmaint.com
Sat Aug 23 17:34:30 EDT 2003
> this week I upgraded on a test system to current stable IC
> version. The old server took 10 seconds to render a HTML page
> with two products. The new server (four times CPU power) bought
> down the performance even more; it took 30 seconds. Unusable of
> course. I wondered why my full text search returning some hunderd
> matches just takes 1 second...
> I enabled "DataTrace 1" in "interchange.cfg" and got 134.000
> lines logfile for a single request returning 2 (two) product
> items build via "/scan/" like foundation does. It queried for a
> particular product category (as foundation does) containing two
> In the log the most interesting line for me was:
> dbd_st_prepare: statement = >select * from products <
> <- execute= 10944 at DBI.pm line 1468
> Why the heck this?! The rest shows up heaps of addtionaly queries:
> $ grep "dbd_st_execute" /tmp/icdebug |wc -l
> ("select shop_id from products where sku = 'xx:xxxxx'")
> hum. Each SKU gets fetched, I guess because I use a field more
> ("shop_id"). But anyway, a select * from products returns that
> field already, and there is a WHERE clause missing.
> But the nice thing I learned: my DBMS takes 400 queries per
> second :-) (PostgreSQL)
> If I'd queried *all* products for all ("virutal") shops, this
> would be reasonable:
> xxxxxxxxx-shop=> select count(*) from products;
> (1 row)
> but I just wanted two. I replaced the /scan/ with a simple
> "[query]" using a WHERE clause asking for the category and the
> shop identifier. This reduced render time from 30 secs to
> somewhat around half a second.
> Is this a bug in /scan/? Did I something wrong? Or does /scan/ is
> implemented to verify the condition by itself after fetching
> *any* possibly needed date from the DBMS?
My guess is that you are not performing a coordinated search. If I do a
then where is Interchange going to look for 'foo'? Answer is all fields in
all records of the products table.
Webmaint.com - Building Clever Websites http://www.webmaint.com/
Webmaint.net - Business Web Hosting http://www.webmaint.net/
More information about the interchange-users