Queries - sometimes fast, sometimes slow

dave.bevan · Post by **dave.bevan** » Thu Jul 08, 2004 2:37 pm

texis -h:
Texis Web Script (Vortex) Copyright (c) 1996-1998 Thunderstone - EPI, Inc.
Commercial Version 2.5.908387314 of Oct 14, 1998

Hi,

I've been running a busy internal site for around 7 years (yes, my texis installation is really that old!).

More recently, we've notices big performance dips on some queries. Sometimes the query will execute fast (<300ms), other times it can take up to 90 seconds to complete the SAME query.

We don't have any apparant lock contention issues (as far as I can see anyhow - I can still use TSQL to get into the database while the query is running.

Also, we're seeing that on these long-executing queries, the memory used by the [CGI, our own, not Vortex] app shoots up to 200MB, where as when running fast, you hardly see any memory impact.

We're started more frequent inserts into the table concerned recently.

My question is this::

Does a co-incidental insert followed closely by a query, against the same table, make the query run without the advantage of the various indexes being made available (as they may be locked for update)?

Regards,

-- Dave.

Dave Bevan. Systems Specialist - Support Development
BBC News Production Facilities.
BBC TV Centre,
LONDON W12 7RJ
UK.

dave.bevan · Post by **dave.bevan** » Thu Jul 08, 2004 4:20 pm

Hi John,

I'm using (been for many years) ltest, and see no unusual behavoir (at the moment anyhow).

Re index updates pending, the largest I have at present is around 6Kbytes - the 'T' files only appear on metamorph indexes.

I have SYSMETAINDEX configured to check the relevent MM indexes with a WAIT of 3600 and a THRESH of 1000, so I think that's why I've not got much indexing data awaiting flush.

What I also can't get working reliably is texisd. That's to say that the 'texisd' won't 'stay' running once it's been launched. I've reverted to running a backgrounded 'ltest' instance, which seems to be the only stable way of getting consistent lock management.

The CGI apps are compiled using NCGDIRECT and ncgargv/ncgargc so the connection to the database is direct, rather than managed by texisd.

As I'm in brain-dump mode, I'd also like to check how best to query a boolean condition against a particular column, as part of a larger query.

For example, my benchmarking has determined that:

"SELECT ....... WHERE ... AND ... AND cid IN (1,2,13,14,......) AND ... ORDER BY ..." (up to 20 numbers in the cid 'option list')

is FAR SLOWER (5x or x10 slower) than:

"SELECT ....... WHERE ... AND ... AND (cid=1 OR cid=2 OR cid=13 OR cid=14 OR cid=......) AND ... ORDER BY ..."

Identical query, other than "IN (.....)" vs "(... OR ...)"

Any ideas - the "cid" column is an INT type?

Thanks,

-- Dave.