[ic] interchange and SQL DBMSes: /scan/ fetches anything?!

[Jonathan Clark]

> 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
> items.
>
> In the log the most interesting line for me was:
>
> dbd_st_prepare: statement = >select * from products <
> and:
> <- 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
>   11447
>
> ("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;
>  count
> -------
>  10944
> (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
search:

se=foo

then where is Interchange going to look for 'foo'? Answer is all fields in
all records of the products table.

Jonathan.



--
Jonathan Clark
Webmaint.com - Building Clever Websites   http://www.webmaint.com/
Webmaint.net - Business Web Hosting       http://www.webmaint.net/