webupper function and UDTs -- solution!

Here's a solution to a problem I wrote to the list about in early March: that the Web blade retrieves dynamic tags from the WebTags table using the webupper function, causing a table scan because the primary-key index on ID can't be used and causing an unnecessary cast to the html type. Apparently with the introduction of Data Director for Web, a new Web blade configuration parameter was created. The docs say: > MI_WEBTAGSSQL > > Set this variable to define the table in which the dynamic tags are stored. > To define the wbTags table as the default dynamic tag storage, add the > following line to the web.cnf file: > MI_WEBTAGSSQL SELECT parameters, object FROM wbTags WHERE > webupper(ID)=webupper(MI_WEBTAGSID); Rather than the definition given here, set the parameter MI_WEBTAGSSQL to SELECT parameters, object FROM webTags WHERE ID='$MI_WEBTAGSID';This change also means that tag IDs can be case sensitive. Seth ----- Forwarded message from Seth Grimes ----- I noticed something weird while tuning performance: explain output (SET EXPLAIN ON) for dynamic tags shows QUERY: ------ select parameters,content from webTags where webupper(ID) = webupper('ihsetglobal'); Estimated Cost: 6 Estimated # of Rows Returned: 22 1) informix.webtags: SEQUENTIAL SCAN Filters: informix.equal(informix.webupper(informix.webtags.id ),UDT ) 1) I was unaware that the names of dynamic tags are case insensitive. I don't see this mentioned in the docs. 2) The webupper function is defined on the Web blade's HTML type... but webTags.ID is of type varchar. While there's a cast from varchar to HTML, it must cost something to use it. The designers should have created a version that works directly on non-HTML types given how often it's going to be called. 3) The primary constraint is on webTags.ID but there's not functional index on webupper(ID) hence the table scan reported above. No big deal unless you have more than a small number of UDTs. Comments? Seth -- Seth Grimes Alta Plana Internet & database design & development grimes@altaplana.com http://altaplana.com 301-891-2581 grimes@access.digex.net http://www.access.digex.net/~grimes ----- End of forwarded message from Seth Grimes ----- -- Seth Grimes Alta Plana Internet & database design & development grimes@altaplana.com http://altaplana.com 301-891-2581 grimes@access.digex.net http://www.access.digex.net/~grimes