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
|
Hi, we are doing a pretty complex SQL select (which generates a series of selects over multiple entities) through lightspeed and were wondering if we have any control over the SQL that is generated by lightspeed to improve performance? Is it possible to execute a custom sql script though lightspeed and still have entities populated? Thanks for your time. |
|
|
Use the IUnitOfWork.FindBySql method. |
|
|
Hi. We are having a lot of trouble populating a collection of data from the database within an acceptable time using the LightSpeed query object. This query is our primary query and must perform at the speed of light! The LightSpeed generated select takes 1600ms while our hand cut sql takes less than 300ms. The LS generated sql is made up of 9 queries. The entity collection we are populating can be a collection of 400 records and each record has some linked entities that are populated from the LS generated sql query (Uow.Find(query).) Can we use FindBySql(sqlTxt) or Find(storedProc) and utilise our own sql script to populate our collection? Our script also has the 9 queries returning the exact results of the LS generated query. We have tried but we always get exceptions when we include the results for more than one entity object. I also copied the LS generated sql and thought that I should be able to execute this from FindBySql(.) but it also throw an exception. Sp error is “No Id returned from stored procedure .....”. txt error is ‘NullReferenceException – Object reference not set to an instance of an object’ Please help? Do you need more info such as the sql scripts or would you like to know about our model and the relationships between the entities?
|
|
|
As mentioned before, you can use FindBySql or a stored procedure to populate entities from your own SQL, but at the moment we have a limitation that these can only return populate lists of entities, not entire eager load graphs. The best approach will depend on how the main query is used. For example, if it is used primarily to obtain a list of appointments and key associated data, then rather than loading an entire object graph, you could consider creating a special (read-only) 'AppointmentDisplayInfo' entity that backs onto a view or stored procedure that denormalises the data into a flat list. (Or you could use FindBySql.) Then, when you need to drill into associated entities or modify the data, you can load individual "real" Appointment entities by ID as required. |
|
|
Thanks for the prompt reply. This is quite important to us and it would be fantastic if we could populate the entity collection and its child objects with FindBySql and Find(sp). Bearing in mind that our hand cut sql brings back the exact same record sets and columns, how difficult would it be for you to make a change to LS so that we could build a query object (as in supplied code example in above post) and supply it with our hand cut optimised sql query or a stored proc and param objects? Could it be as simple as creating a property on the query object that we could set and the uow.Find would use that query or named stored proc instead of the LS generated one? Regards |
|
|
Unfortunately, no, it's not that simple. When LightSpeed generates its own SQL, it can control the structure to make it easy to read back into entities. When it has to handle arbitrary user-supplied SQL, we have to perform additional mapping work to match your result set to the entity definition. Now as you know we already have mechanisms for this: FindBySql and Find(ProcedureQuery). The limitation is that they don't yet support eager loading of associations, because this requires additional work on our part to map not only a row to an entity, but also a result set to an entity class. This is actually harder than column mappings. When we get a result set, we at least know the names of the columns from the IDataReader, and we can map those to the fields of an entity type. But if your query returns multiple result sets from multiple tables, we don't know which table each one comes from! So we have no idea which entity type to map it into! So at the very minimum we need to extend the API so that you can tell us "read the first result set into Appointments, the second into Rooms, the third into Customers, etc." [Edited to add: In fact it is rather trickier than this, as in general there could be two associations to the same type, and they would be populated by two separate SELECTs. This may not apply in your case but it's still something we'd need to handle. So the API would need to list not types but associations.] Just 'creating a SQL property on the query object' would get us no further than what we already have in FindBySql and Find(ProcedureQuery). The issue is not being able to pass a SQL string or sproc name: it is being able to map multiple result sets over multiple entity types. I will investigate this but I can't make any promises and I'd therefore encourage you to look at alternative approaches such as the viewmodel idea I suggested in my previous post. |
|