Re: queries on smi tables

Mickm, Your sample query ran in under 30 seconds on my system Sequent/ Informix 7.10.UC3. I have noticed that some queries do "seem" to take a long time. Have you run set explain against your queries. I have on my system and it has provided some insight to why the queries seem to run slow. The following query: Select dbsname[1,10], tabname[1,14], type, rowidlk, sid, username from syslocks, outer syssessions where syslocks.owner = syssessions.sid 1. creates a view for syslocks, seq scans SIX tables to load the view into a temp table 2. executes the above query, seq scans the temp table, does an index lookup into sysscblst and an autoindex lookup into sysrstcb. I think? When we use the smi tables we are not *just* reading shared memory. My next step is to read the create script for the sysmaster database to gain some further insight. Note: I have to "outer syssessions" because of a hash join bug on my version. Regards, Steve Berg - spb@masada.nb.com PS. Here's a query to list your temp tables in DBSPACETEMP select hex(i.ti_partnum) partition, trim(n.dbsname) || ":" || trim(n.owner), trim(n.tabname) table, i.ti_nptotal allocated_pgs from systabnames n, systabinfo i where bitval(i.ti_flags, "0x0020")=1 and i.ti_partnum = n.partnum On Mon, 27 May 1996, Mickey Mestel wrote: > hi, > has anyone else noticed that in 7.X queries on the smi tables are > incredibly slow? no matter what the load on the system, the following > query: > select dbsname from systabnames > where dbsname = "bls" and tabname = "abi"> > takes a consistent 5 minutes or so to run. considering that this pseudo-table > exists in memory, and only has a couple thousand rows, if that, *and* is > indexed, this seems really out of hand. > > i'm using this stuff to write all kinds of utilities for dba info, > and have to preface each of my scripts with an echo or a print stating > that this will take some time, and to have patience. > > a straight select * from tabname runs like you would expect it to, > but any type of relational query runs incredibly slow. > > anyone else seen this? > > mickm >