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
|
I want to have a Select procedure that selects a page of results as entities, but from it I also want to return from the query the total row count of results using COUNT(*) OVER() An output parameter from the stored procedure seems like the obvious solution but I think would need to select the result into a temporary table/table variable and then select the result from there to assign it to the output parameter. Would that effect performance much? Another option I thought of would be to create a new entity that inherits from the one I want to use with the stored procedure and have the additional count field on the new entity. Would that work? What do you guys advise to go about doing this? |
|
|
|
|
[quote user="James Newton-King"]Would that affect performance much?[/quote] That will depend on your database, size of table, how the query optimiser decides to run your query, phase of the moon, etc. If you are using SQL Server, there is a program called SQL Profiler (under Performance Tools) which can help you measure performance; some other databases have similar tools or you can just compare the queries using ADO.NET and a System.Diagnostics.Stopwatch. From a LightSpeed point of view, I don't think there would be significant overhead because LightSpeed only works with the result set (there is obviously the extra cost of copying an integer into an out-parameter but that will be insignificant!). [quote user="James Newton-King"]create a new entity that inherits from the one I want to use with the stored procedure and have the additional count field on the new entity. Would that work?[/quote] Technically, it could work, but it would be very wrong. The row count is an attribute of a query, not an attribute of an individual entity. You could also run into problems with caching -- if you run the sproc twice in the same unit of work, the second run might return an ID that was already in the identity map, in which case we would return the existing entity which would have the count from the first run. |
|
|
|
