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.

Post by **John** » Thu Jul 08, 2004 2:55 pm

The first thing I would suspect is that with the more frequent inserts you may not have adjusted the rate at which the index is refreshed. You could check that by looking at the indexname_T.tbl file. A large one indicates an index that needs refreshing.

An insert does not make the index unavailable, however it will add to the list of records to be searched linearly. If that list gets large it can take a while.

Ltest should be able to show the actual lock contention if it does exist.

If you need to go in to more detail you can open a tech support ticket via the Tech Support link.

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.

Post by **John** » Mon Jul 12, 2004 9:40 am

You might try with tsql -V to see what it is doing. It may be using an index for the IN, but not the OR, and if one or more of the cid's have a lot of results it maybe reading and processing a lot from the index, whereas if the other clauses have reduced the result set sufficiently it may not need to look at a large number of records. How many records match without the cid clause?