Performance problems

[Gerben Vos]

Hello everyone,

we have been using Neo for some time now (you may remember Arjen Poutsma, who worked with us a year or two ago and who made several contributions to the Neo project). Until recently, we used Neo 1.3.0, now 1.4.2, and SQL Server.

Only recently we implemented our Neo-based application with a real-life database, which we converted to the format expected by our application. However, we are having some performance problems which we don't seem to be able to solve.

The database contains, amongst others, a table 'Records' with about 80,000 rows, and the user can define several 'fields' to go with these records; their values are stored in a table 'FieldValues' with about 2.3 million rows.

We have added a lot of indexes to the database, which helped considerably, and upgraded to 1.4.2, which also helped a bit. Using spans also seems to help in some cases, although we are still investigating which exactly.

The main problem that is still left seems to be that when doing a query with a large number of results, Neo spends a huge amount of time in the routines that create objects from the result list. For example, when the results contain 5000 rows, this can take seven minutes, although the SQL query itself returns relatively quickly. Larger result lists can take more than an hour, which is almost all spent inside Neo code (we didn't wait until it was finished). The amount of time spent on processing of the result by Neo seems to be seriously out of proportion to that spent executing the SQL query.

Here are some extracts from our schema XML. We use 32-character GUIDs as primary keys.

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE database SYSTEM "norque.dtd">
<database name="Product" package="ZyLAB.Product.BusinessLayer" defaultIdMethod="guid" defaultJavaNamingMethod="nochange">
        <table name="Records" javaName="Record" subPackage="Records">
                <column name="RecordId" primaryKey="true" required="true" type="CHAR" size="32" />
                ....
                <iforeign-key foreignTable="FieldValues" name="FieldValues" onDelete="cascade">
                        <ireference local="RecordId" foreign="ForeignId" />
                </iforeign-key>
        </table>
        <table name="FieldValues" javaName="FieldValue" subPackage="Fields">
                <column name="ValueId" primaryKey="true" required="true" type="CHAR" size="32" />
                <column name="ForeignId" required="true" type="CHAR" size="32" />
                ...
        </table>
        ...
</database>

Note that ForeignId itself isn't defined as a foreign-key. This is because it can also refer to the primary key id of other tables that define entities that can have fields (like 'Categories' and 'Folders').

When implementing spans, we found that we had to add the following code, called from to FieldValueMap.GetRelationInfos() (we had to make a small change to NeoSupport.vtl to call a static member in the FieldValue class):

            infos.Add("Record", new RelationInfo(factory, typeof(ZyLAB.RMA.BusinessLayer.Records.Record), typeof(FieldValue), "RecordId", "ForeignId"));

This addition in itself sped things up considerably, even when not using spans, but not enough for some queries, as described above.

We're at a loss how to fix this. We suspect there's some inefficient code with Neo itself that causes this. Or maybe it would be better to implement some type of lazy object creation somehow?

                                        Gerben Vos,
                                        ZyLAB Technologies B.V.

[Artem-5]

Dear Gerben,

You can use log4net to generate Neo-specific output, then you'll see
more details and timings. You can also post the output here, so that
we could see what's happening in your project. Just put the line
log4net.Config.BasicConfigurator.Configure();


I've discovered that spans can make it quick but also can slow it down
considerably (up to 10-20 times), because of the post-datastore stage,
the HitSpans method, which is called recursively.

In addition, a lot of work is done when we do then in-memory search.
Neo is smart since we might have some objects added/changed and not
committed to the database, so we don't trust the query and do the
whole search again, now in the memory. However, it would be great to
be able to turn this off (like you can turn off the db query stage).

Sadly, it seems that the project is abandoned (is it?). There was a
2005 version by Paul Gielens, but it hasn't made it to the repository.
It is very sad since Neo has some unique features, and is very simple
to use.

Monday, June 11, 2007, 9:06:14 PM, you wrote:

> Hello everyone,

> we have been using Neo for some time now (you may remember Arjen
> Poutsma, who worked with us a year or two ago and who made several
> contributions to the Neo project). Until recently, we used Neo 1.3.0, now 1.4.2, and SQL Server.

> Only recently we implemented our Neo-based application with a
> real-life database, which we converted to the format expected by our
> application. However, we are having some performance problems which
> we don't seem to be able to solve.

> The database contains, amongst others, a table 'Records' with about
> 80,000 rows, and the user can define several 'fields' to go with
> these records; their values are stored in a table 'FieldValues' with about 2.3 million rows.

> We have added a lot of indexes to the database, which helped
> considerably, and upgraded to 1.4.2, which also helped a bit. Using
> spans also seems to help in some cases, although we are still investigating which exactly.

> The main problem that is still left seems to be that when doing a
> query with a large number of results, Neo spends a huge amount of
> time in the routines that create objects from the result list. For
> example, when the results contain 5000 rows, this can take seven
> minutes, although the SQL query itself returns relatively quickly.
> Larger result lists can take more than an hour, which is almost all
> spent inside Neo code (we didn't wait until it was finished). The
> amount of time spent on processing of the result by Neo seems to be
> seriously out of proportion to that spent executing the SQL query.

> Here are some extracts from our schema XML. We use 32-character GUIDs as primary keys.

> <?xml version="1.0" encoding="utf-8" ?>
> <!DOCTYPE database SYSTEM "norque.dtd">
> <database name="Product" package="ZyLAB.Product.BusinessLayer"
> defaultIdMethod="guid" defaultJavaNamingMethod="nochange">
>         <table name="Records" javaName="Record" subPackage="Records">
>                 <column name="RecordId" primaryKey="true"
> required="true" type="CHAR" size="32" />
>                 ....
>                 <iforeign-key foreignTable="FieldValues"
> name="FieldValues" onDelete="cascade">
>                         <ireference local="RecordId" foreign="ForeignId" />
>                 </iforeign-key>
>         </table>
>         <table name="FieldValues" javaName="FieldValue" subPackage="Fields">
>                 <column name="ValueId" primaryKey="true"
> required="true" type="CHAR" size="32" />
>                 <column name="ForeignId" required="true" type="CHAR" size="32" />
>                 ...
>         </table>
>         ...
> </database>

> Note that ForeignId itself isn't defined as a foreign-key. This is
> because it can also refer to the primary key id of other tables that
> define entities that can have fields (like 'Categories' and 'Folders').

> When implementing spans, we found that we had to add the following
> code, called from to FieldValueMap.GetRelationInfos() (we had to
> make a small change to NeoSupport.vtl to call a static member in the FieldValue class):

>             infos.Add("Record", new RelationInfo(factory,
> typeof(ZyLAB.RMA.BusinessLayer.Records.Record), typeof(FieldValue), "RecordId", "ForeignId"));

> This addition in itself sped things up considerably, even when not
> using spans, but not enough for some queries, as described above.

> We're at a loss how to fix this. We suspect there's some
> inefficient code with Neo itself that causes this. Or maybe it would
> be better to implement some type of lazy object creation somehow?

>                                         Gerben Vos,
>                                         ZyLAB Technologies B.V.




---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email

[Jim Arnold]

Hi Gerben,

Sorry I don't have any specific solutions for you, but I have spent quite some time working on performance tuning with Neo, so maybe with more information I can help.  Have you run a profiler over your code?  It would be useful to see which methods Neo is spending most time in.  Some of the killer areas I've seen before have been to do with events, both inside ADO.Net and with Neo itself.  If you have a lot of dependent records, the number of events created can be very high.  5000 records should not take 7 minutes to materialise under normal conditions.

The GUID you are using looks interesting also.  Creating and matching string-based GUIDs will be quite a lot slower than, say, integers or the System.Guid type.  Given that Neo does a lot of indexing and sorting by key, this might be another performance hot spot.

Unfortunately I haven't had much experience with spans so I can't say much about them.

Finally, is this table read/write from your application, or read-only?

Jim

"Gerben Vos" <Gerben@...>

11/06/2007 18:06

Please respond to
user@...

To	<user@...>
cc	"Siebe Bloembergen" <Siebe@...>, "Lieuwe Visser" <Lieuwe@...>
Subject	[neo-user] Performance problems

Hello everyone,

we have been using Neo for some time now (you may remember Arjen Poutsma, who worked with us a year or two ago and who made several contributions to the Neo project). Until recently, we used Neo 1.3.0, now 1.4.2, and SQL Server.

Only recently we implemented our Neo-based application with a real-life database, which we converted to the format expected by our application. However, we are having some performance problems which we don't seem to be able to solve.

The database contains, amongst others, a table 'Records' with about 80,000 rows, and the user can define several 'fields' to go with these records; their values are stored in a table 'FieldValues' with about 2.3 million rows.

We have added a lot of indexes to the database, which helped considerably, and upgraded to 1.4.2, which also helped a bit. Using spans also seems to help in some cases, although we are still investigating which exactly.

The main problem that is still left seems to be that when doing a query with a large number of results, Neo spends a huge amount of time in the routines that create objects from the result list. For example, when the results contain 5000 rows, this can take seven minutes, although the SQL query itself returns relatively quickly. Larger result lists can take more than an hour, which is almost all spent inside Neo code (we didn't wait until it was finished). The amount of time spent on processing of the result by Neo seems to be seriously out of proportion to that spent executing the SQL query.

Here are some extracts from our schema XML. We use 32-character GUIDs as primary keys.

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE database SYSTEM "norque.dtd">
<database name="Product" package="ZyLAB.Product.BusinessLayer" defaultIdMethod="guid" defaultJavaNamingMethod="nochange">
                <table name="Records" javaName="Record" subPackage="Records">
                                 <column name="RecordId" primaryKey="true" required="true" type="CHAR" size="32" />
                                 ....
                                 <iforeign-key foreignTable="FieldValues" name="FieldValues" onDelete="cascade">
                                                  <ireference local="RecordId" foreign="ForeignId" />
                                 </iforeign-key>
                </table>
                <table name="FieldValues" javaName="FieldValue" subPackage="Fields">
                                 <column name="ValueId" primaryKey="true" required="true" type="CHAR" size="32" />
                                 <column name="ForeignId" required="true" type="CHAR" size="32" />
                                 ...
                </table>
                ...
</database>

Note that ForeignId itself isn't defined as a foreign-key. This is because it can also refer to the primary key id of other tables that define entities that can have fields (like 'Categories' and 'Folders').

When implementing spans, we found that we had to add the following code, called from to FieldValueMap.GetRelationInfos() (we had to make a small change to NeoSupport.vtl to call a static member in the FieldValue class):

           infos.Add("Record", new RelationInfo(factory, typeof(ZyLAB.RMA.BusinessLayer.Records.Record), typeof(FieldValue), "RecordId", "ForeignId"));

This addition in itself sped things up considerably, even when not using spans, but not enough for some queries, as described above.

We're at a loss how to fix this. We suspect there's some inefficient code with Neo itself that causes this. Or maybe it would be better to implement some type of lazy object creation somehow?

                                                                                    Gerben Vos,
                                                                                    ZyLAB Technologies B.V.