performance limit

Hi, can someone share expirience with performance limits of ObjectDB especialy when quering tables with large records sets.

Our issue is with tables with 100.000+ records. Everything was OK when we had few thousand records.

We have a simple SELECT statement which selects from a table with 100.000+ records by UserID atribute, tables have from 5 to 20 atributes. Usualy the results range from few hundred to few thousand records. This query takes up to 12 seconds.

Is this normal, what when we will have few milion records, how to handle this.

Everything is set to lazy loading, we tried indexing the table and it doesnt help much.

In this case ObjectDB is used similar to a relational database. I have seen a similar query on a relation database with few milion records working in a sub second range.

Regards.

With proper indexes defined, queries on millions of objects should take only milliseconds.

There could be several reasons for slow query execution, including indexes that are not in use. Particularly, if a new index is defined for an existing entity that already has instances in the database (see this solution).

HI, 

we have indexes in place on createdBy atribute. We folowed the instructions you proposed on http://www.objectdb.com/database/issue/18.

This is the query we use 

select e from AktImpl e where e.createdBy like 'username'

Runing this query on 150.000 records gives 126 results in 15 ms for user A. Using this query for username B gives 6500 results in 1700 ms. We will hit 1 milion records wery soon and aplication would be nonresponsive then.

This is linear increase of time, we asume that indexes are not used. Runing the query in ObjectDB explorer clearly marks tha index on atribute createdBy is used.

Can you please give some explanation on this or point us where to go. Is it possible that indexes are not used, what kind of indexes does ObjectDB uses, is there some default type, can we change it.

Last thing we can do is to create a new Database and Aplication with these 6-7 critical classes, fill it with milion of records. Could we send it to you for testing.

Thanks for help in advance, this is a blocker issue for us.

Kind regards 

This is a very simple query, and ObjectDB can process such queries very quickly. If a proper index is defined and in use the effect of the number of candidate objects is minimal. There is probably a good reason for this behaviour in your application.

According to your description it seems that a proper index exists and in use. The next guess is that maybe the query is executed fast, but loading the results takes a lot of time. If this is the case, you may notice that the same query runs much faster in the Explorer (because in the Explorer result data is always loaded lazily). You may execute queries in the application with lazy result loading, using the objectdb.result-fetch query's hint in order to separate query execution from result loading.

Loading the query results could be very slow if eager fetch is used extensively, and then your 6500 result objects may require loading of many other referenced objects.

If this answer doesn't solve the issue, please provide a test case (as you suggested) to demonstrate the issue.

Thanks, we will give it a try, altough everything is set to layzy loading, Query is executed from a java console app.

Regards.