Optimizing query speed.

[tony.malandain]

Hi,

I have a table with 3 fields:
Id: GUID stored in a char(40) column.
Index: stored in a integer column
PlainText: stored in a varchar(1000) column.

On this table I have the following index:
A metamorph inverted index on PlainText, Id, Index
and a unique index on Id, Index

We've build a C++ program that uses the Texis API to query the database.
Typical queries look like:
select PlainText from MyTable where Id in ( some Ids );
select PlainText from MyTable where Id = xxx and Index=xxx;

When we run searches on this table, the time it takes to respond varies greatly. At best it takes about 50ms, but at worst it can take several seconds!!!

Looking at the resources used, the program never grows bigger than a few MB where the table has above 400,000 rows and the index (.tok file) is about 30MB.
I have the feeling that it is not fully load the index in memory and instead, it keeps accessing the disk to get the information it needs. Do I understand it correctly?
If so, would loading the complete index (.tok file) in memory improve the performances? I have about 2GB of memory on my server so I could store some data there.
How could I do that?

Thanks,
/Tony.

[John]

Typically the token file is mapped into memory, rather than being read, which may not show up in the process size. The full index (which includes all the index files (.tok/.btr/.dat etc) is not read in, as that would slow down a single query.

The operating system should cache the index in memory so that when Texis does access the file again it is already in memory.

[tony.malandain]

We are running Texis on Windows 2003 and I'm not sure windows will cache the 30MB of the file in memory.
It may cache a portion of the file, but I have strong doubt it caches everything. As I mentionned, some of the queries takes several seconds where other take only 50ms.
How can I reduce that difference. Several seconds is way too much. I have plenty of memory available on the server but Texis doesn't seem to take advantage of it.
Do you have any guidelines on configuring Windows 2003 to optimize Texis performances?