RE: Database config for large table

Based on previous messages in the mckoidb mailing list, we were making
sure to use one connection for the life of the application.

I know it is pretty sad that we hadn't tried it already, but the
increase in the data_cache_size does seem to have helped for many of the
queries. Is there any way to know the point of diminishing return for
giving it more memory? Are there any other settings we should also try
changing?

Some queries are still performing strangly. All of the test below were
performed using the Mckoi JDBC Query Tool.

In it's original form:

SELECT MIN(ed.SampleIndex) 
FROM EventDetail ed, EventHeader eh 
WHERE EventType=2 
  AND ed.SampleIndex>200000 
  AND eh.LocatesPriority 
  AND ed.SampleIndex=eh.SampleIndex

This query took around 2 second to execute against a particular
database.

We modified it to:

SELECT MIN(ed.SampleIndex) 
FROM 
  (SELECT * FROM EventDetail 
  WHERE EventType=2) ed 
  JOIN EventHeader eh 
  ON ed.SampleIndex=eh.SampleIndex 
WHERE ed.SampleIndex>200000 
  AND eh.LocatesPriority

In this form the query took ~0.02 seconds when it is run using version
1.0.2 of Mckoi. Against version 1.0.3 this form still takes ~2 seconds.
Removing the "AND eh.LocatesPriority" under 1.0.3 drops the time down to
~0.02 seconds.

We have discovered in situations where a filtering where condition (such
as the "EventType=2" in the example above) is going to cut down the
number of records significantly, applying it before the join conditions
can significantly up the performance. Does this sound like a sensible
technique?

Thanks for your help.

Joel


-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Tobias
Downer
Sent: Monday, April 03, 2006 8:04 PM
To: [email protected]
Subject: Re: Database config for large table

Hi Joel,

Could you give an example of the type of queries that are performing 
badly?  Also, are you keeping at least one connection open to the 
embedded database at all times?  If your application is running Mckoi in

embedded mode and it creates only a single connection, performs a query,

and then closes the connection, you are incurring the cost of starting 
and stopping the database together with the time it takes to perform the

query.  In addition, each time you close the last connection to an 
embedded database, the cache is wiped so you lose the benefit of 
collecting cached data over a long session.

So I suggest you make sure to keep at least one connection open to the 
embedded database over the entire session of your application.  To 
increase the size of the cache, modify the 'data_cache_size' property in

your .conf file.  For example, to set the cache size to 32MB;

data_cache_size=33554432

Hope this information help,
Toby.


Joel Garringer wrote:
> We have a table with a few hundred thousand records with a couple of 
> dozen columns. Most of the columns are type FLOAT or INT.
> 
> We have been having a really tough time with performance against this 
> table. Queries against the table can take several minutes to run, and
I 
> really think that we are just missing something.
> 
> The table can easily grow to around 100 meg, and I wanted to make sure

> there weren't any easy changes we could make to the ".conf" file that 
> would help us. The database was very peppy when this table had less
than 
> 50,000 records, so the performance problems have kind of taken us by 
> surprise.
> 
> We run the database in embedded mode and we could give the database 
> hundreds of meg of memory if we needed to, but I not sure if/how Mckoi

> can use the extra memory.
> 
> I think that I can send a copy of the table if you want to look at it.
> 
> Thanks.
> Joel



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]