This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
We have a performance problem when using lightspeed model with a procedure that returns app. 35 columns and 20.000 records. It takes the procedure 20 sec. to execute and this is really worrying since we are still in development and working with a test database (which always contain less records than the production DB). We are using the model against Oracle DB. Any ideas? If we make the prcedure return less columns on the same number of records, the procedure is a lot faster. So we assume the problem lays in the mapping of all the columns which takes time. |
|
|
Hello Delfino, Loading via a stored procedure is slower than loading from a table. When loading from a table, we are able to control the order of the columns to be the same as the order of the fields in the CLR object, so we can copy data by index without needing to do a by-name lookup. When loading from a sproc, because the order of the columns is determined by the SP and does not correspond to the field order, we need to do a by-name mapping which is more expensive. We have applied some optimisations to this but I will take a look and see if we can improve it to be closer to what you would get from a table load. Thanks for letting us know about this! |
|
|
Hello Delfino, I have made some changes to the way we load objects from stored procedure results which should improve the performance for large data sets. These improvements will be included in nightly builds dated 30 Apr 2009 and above, available from about 1430 GMT. In informal testing, loading 30000 instances of an object with 22 columns from a local SQL Server database via a stored procedure now takes approximately 2-3 seconds. Obviously the actual performance characteristics will depend on the size of the data, the complexity of the SP, the connectivity to the database, etc. Please let us know whether you still see performance issues with the new build. If so it would be useful to know whether you see the same performance issues if you bypass the SP and query the table directly, and also to see the Oracle CREATE TABLE statement for the table. Thanks! |
|
|
Thanx, Ivan! I'll let you know. |
|