In self servicing, is it correct that only datatables and collections can be returned from different gets that exist? Or is there a way to return a datareader?

Thanks,

tvoss wrote:

In self servicing, is it correct that only datatables and collections can be returned from different gets that exist? Or is there a way to return a datareader?

No, there are no datareaders exposed as that would expose a database connection to the outside of the layer.

Would you like a datareader exposed from the ORM support classes?

It would be nice to have a datareader for large data pulls.

tvoss wrote:

It would be nice to have a datareader for large data pulls.

I'll add it to the todo, that is: the ability to get the requested data as a datareader.

I know this thread is almost a year old, but I was wondering if there is any new news on datareaders for llblgen pro?

It's still on the todo list, haven't decided when to implement it, it has a low priority

I guess I shouldn't get in the habit of commenting on other users' requests, but...what would be the point of this? If you have to get a lot of data wouldn't you use a typed list which is just a datatable derived object populated by a datareader already?

wouldn't the data reader let you write the records to disk (for example) as you read them, where as the typed list would have to fit all in memory?

The point can be that you can call a procedure, get a datareader from that and use the datareader to fill entity objects. At least that's the only purpose I can see where this can be a bit helpful. On the other hand, while this looks great, it is a real pain to maintain.

Otis wrote:

The point can be that you can call a procedure, get a datareader from that and use the datareader to fill entity objects. At least that's the only purpose I can see where this can be a bit helpful. On the other hand, while this looks great, it is a real pain to maintain.

I just wanted to place another vote for being able to get a DataReader from LLBLGen.

The specific case where I think it would be quite useful is for TypedLists and TypedViews, especially when I'm using a custom ResultsetFields. In these cases, I'm basically getting back a single table of data. So if I were going to pass that data off to some other module (e.g. a report or some outbound XML stream), I'd like to improve performance by not storing it in the intermediary DataTable. But I get to keep using all the query generation code and field/relation constants that LLBLGen has generated for me.

And as another forum member said, since a DataTable is loaded using a DataReader, it should be relatively simple to just return the DataReader directly.

Although it could be implemented with entities as well, I don't see as big an advantage there because you would have to disallow things like prefetch paths, making it more complicated for end users.

I'll add it to the todo to expose a fetched query as a datareader.

That means I'll reconsider it with every upgrade. The main point I have against exposing datareaders is that it allows users to grab a datareader and pass it on to another tier.

Thanks for adding it to your todo list.

Otis wrote:

The main point I have against exposing datareaders is that it allows users to grab a datareader and pass it on to another tier.

I think every framework has ways in which it can be misused. I agree that you want to encourage the "the pit of success", meaning it doesn't make sense to include a feature in a framework if it has no good applicability. But in the case of a data reader, it can be the optimal approach for dealing with large result sets within a tier. So I think it makes sense to include it.

Anyways, thanks again.

Otis wrote:

tvoss wrote:

It would be nice to have a datareader for large data pulls.

I'll add it to the todo, that is: the ability to get the requested data as a datareader.

Frans, Did this ever get implemented?

UPDATE: ahhh... from: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=1029

(ID: 96) [10] Adapter: make it possible to supply the entity fetch routine with a datareader filled somewhere else (for example with data originated from a stored proc Dropped

Any reason why this was dropped? It would be advantageous to be able to get the fastest possible access to data when necessary.

I need to get forward only read only access to data using the fastest possible means... Using what's available today, is using a Stored Proceeder which returns a datatable the fastest way to get the result of a query?

Marcus

Marcus wrote:

Otis wrote:

tvoss wrote:

It would be nice to have a datareader for large data pulls.

I'll add it to the todo, that is: the ability to get the requested data as a datareader.

Frans, Did this ever get implemented?

UPDATE: ahhh... from: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=1029

(ID: 96) [10] Adapter: make it possible to supply the entity fetch routine with a datareader filled somewhere else (for example with data originated from a stored proc Dropped

Any reason why this was dropped? It would be advantageous to be able to get the fastest possible access to data when necessary.

It made the API messy.

You don't get a datareader without using ADO.NET, which thus would imply that the developer needed some sort of ADO.NET interface with which a datareader could be filled/created. Another thing was that the datareader's format (the columns) could be different than the columns required for the entity factory, which would result in "It doesn't work..." kind of questions and because they would be originated by the user, it's likely the user will ask us to tell them what's wrong.

I need to get forward only read only access to data using the fastest possible means... Using what's available today, is using a Stored Proceeder which returns a datatable the fastest way to get the result of a query? Marcus

What do you want to do with the data as you need the fastest way possible?

Otis wrote:

What do you want to do with the data as you need the fastest way possible?

This particular routine performs access control on resources within a web application. I have an optimised query which pulls ACL information from 2 tables. I need to process this info quickly and throw it away. Also it can't be cached since it's a web farm and this routing gets hit more than any other...

Marcus wrote:

Otis wrote:

What do you want to do with the data as you need the fastest way possible?

This particular routine performs access control on resources within a web application. I have an optimised query which pulls ACL information from 2 tables. I need to process this info quickly and throw it away. Also it can't be cached since it's a web farm and this routing gets hit more than any other...

The processing is thus required outside the RDBMS? I asked this because if the processing can be done INSIDE the RDBMS, the routine would be perhaps even more efficient and a stored procedure would be, in this case, a better choice.

How did you set this up, as I think caching is perfectly possible: the ACL's for the resources are static and the ACL's for the user can be loaded when the user hits the webserver for the first time. You then combine the two to get the access level of the user for a given resource. Or are both changing a lot?

Otis wrote:

The processing is thus required outside the RDBMS? I asked this because if the processing can be done INSIDE the RDBMS, the routine would be perhaps even more efficient and a stored procedure would be, in this case, a better choice.

Well actually my question regarding the fastest way was more about minimising the .NET overhead on post processing the query results in LLBLGen. Ideally I'd like to have the results in the most "raw" state. The routine in question exists in a mutlitple instance Service (different machines) consumed by both the web application and external clients and has a potential to be hit more than 200+ times per second at peak times. To this end, in LLBLGen, which method of fetching does the least work?

Otis wrote:

How did you set this up, as I think caching is perfectly possible: the ACL's for the resources are static and the ACL's for the user can be loaded when the user hits the webserver for the first time. You then combine the two to get the access level of the user for a given resource. Or are both changing a lot?

Yes, I can see where you're coming from... However, how do you deal with stale data in one of the web farm's server's cache? Roll on SQL 2005 and database cache invalidation (that is of course... if it actually works in real applications, not just demos )

When a user authenticates, the list of groups (groups support inheritance) that the user is a member of is enunerated and cached for the lifetime of the request. A list of resources that will be required to process the request is determined and a single fetch is performed on the ACL table getting all required permission rows. Resources also support partial permission inheritance, so this query is dynamically created to ensure a single fetch is perfomed as an optimisation. The resulting rows are processed, merged and assigned to their appropriate resource. These are then also cached for the duration of the request.

ACLs are not static, they do change... albeit infrequently. The issue with caching at higher levels is that unless the cache invalidation is synchronised (and I have a plan for that too, below) then you will have instances where one server's cache has fetched fresh data and others still have stale data. The result is that the user has differing permissions on each page request until the caches get back in sync.

I did have the idea to invalidate the cache at regular well know intervals, say every 5 minutes. Therefore new permissions would only become "live" on a five minute boundary 09:05, 09:10 etc... But then the load on the server is going to shoot through the roof on these intervals which is not ideal. So then I thought about calculating a hashcode for the ID of the User which would act as an offset for the invalidation timeout. So for userID= 1234 the hashcode would be for example "34" so I simply invalidate the cache for this user on the 5 minute boundary + the hashcode value of 34, resulting in 09:05:34, 09:10:34 etc... Using this method all servers can calculate when to invalidate a particular user's cache entries and everyone keeps in sync...

I obviously need to think this through more, but it's what I've been planning in lieu of SQL 2005's cache invalidation...

Marcus wrote:

Otis wrote:

The processing is thus required outside the RDBMS? I asked this because if the processing can be done INSIDE the RDBMS, the routine would be perhaps even more efficient and a stored procedure would be, in this case, a better choice.

Well actually my question regarding the fastest way was more about minimising the .NET overhead on post processing the query results in LLBLGen. Ideally I'd like to have the results in the most "raw" state. The routine in question exists in a mutlitple instance Service (different machines) consumed by both the web application and external clients and has a potential to be hit more than 200+ times per second at peak times. To this end, in LLBLGen, which method of fetching does the least work?

a dynamic list fetch will be a good candidate, or a proc call. Both will fill a datatable with raw data.

You can though also try to formulate the things you want with scalar queries, which effectively will offer you the processing on the server (don't know if they're useful in your situation)

Otis wrote:

How did you set this up, as I think caching is perfectly possible: the ACL's for the resources are static and the ACL's for the user can be loaded when the user hits the webserver for the first time. You then combine the two to get the access level of the user for a given resource. Or are both changing a lot?

Yes, I can see where you're coming from... However, how do you deal with stale data in one of the web farm's server's cache? Roll on SQL 2005 and database cache invalidation (that is of course... if it actually works in real applications, not just demos )

Well, sqlserver 2005 is so far away that I wouldn't consider it for a live app now

How's the load balancer set up btw? One server per user, or does it switch to a random server for every request? If it's one server per user, you can utilize that in your app of course.

When a user authenticates, the list of groups (groups support inheritance) that the user is a member of is enunerated and cached for the lifetime of the request. A list of resources that will be required to process the request is determined and a single fetch is performed on the ACL table getting all required permission rows. Resources also support partial permission inheritance, so this query is dynamically created to ensure a single fetch is perfomed as an optimisation. The resulting rows are processed, merged and assigned to their appropriate resource. These are then also cached for the duration of the request.

ACLs are not static, they do change... albeit infrequently. The issue with caching at higher levels is that unless the cache invalidation is synchronised (and I have a plan for that too, below) then you will have instances where one server's cache has fetched fresh data and others still have stale data. The result is that the user has differing permissions on each page request until the caches get back in sync.

How about this: you create a table for cache control. Whenever ACL's are changed, you set a flag in there and have a timestamp in there as well (flag setting, can be something else, as long as the timestamp changes when ACLs are updated). For every request, in the global.asax code behind, you fire a scalar query on that system table. If the timestamp is different from the timestamp of the cached version of the table, the ACL's also cached on that server, are out-of-sync, and you refetch them. This thus takes a very simple query which is fired for every request, and a bigger query which is fired when ACL's change.

You can then use the cached ACL's in the app, the global.asax code takes care of the maintenance, and if you add a trigger to the ACL table, you have no maintenance and no extra code in the ACL maintenance code.

I did have the idea to invalidate the cache at regular well know intervals, say every 5 minutes. Therefore new permissions would only become "live" on a five minute boundary 09:05, 09:10 etc... But then the load on the server is going to shoot through the roof on these intervals which is not ideal. So then I thought about calculating a hashcode for the ID of the User which would act as an offset for the invalidation timeout. So for userID= 1234 the hashcode would be for example "34" so I simply invalidate the cache for this user on the 5 minute boundary + the hashcode value of 34, resulting in 09:05:34, 09:10:34 etc... Using this method all servers can calculate when to invalidate a particular user's cache entries and everyone keeps in sync...

Also a possibility, though can still be slower. That is: if the main ACL read queries are slow. The best optimization starts with measuring what's the real bottleneck (like: if in-core processing of sets of ACL's is slower than fetching the data, it's better to optimize the ACL processing than to try to optimize the query reads.)

Otis wrote:

You can though also try to formulate the things you want with scalar queries, which effectively will offer you the processing on the server (don't know if they're useful in your situation)

Instintively I tend to shy away from getting the DB to do anything that can be done in the BL layers since the DB is ultimately going to be the bottleneck and the most expensive in terms of MS License costs when scaling...

Otis wrote:

How's the load balancer set up btw? One server per user, or does it switch to a random server for every request? If it's one server per user, you can utilize that in your app of course.

Random at the moment... I thought of hashing the userID for load balancing, but there were too many unknowns and still are ( ) regarding features which means I'd prefer not to retrict myself at this point to taking advantage of this optimisation. It is definately on the consideration list though... and a very good point

Otis wrote:

How about this: you create a table for cache control. Whenever ACL's are changed, you set a flag in there and have a timestamp in there as well (flag setting, can be something else, as long as the timestamp changes when ACLs are updated). For every request, in the global.asax code behind, you fire a scalar query on that system table. If the timestamp is different from the timestamp of the cached version of the table, the ACL's also cached on that server, are out-of-sync, and you refetch them. This thus takes a very simple query which is fired for every request, and a bigger query which is fired when ACL's change.

You can then use the cached ACL's in the app, the global.asax code takes care of the maintenance, and if you add a trigger to the ACL table, you have no maintenance and no extra code in the ACL maintenance code.

Great suggestion! I will definately look into this one as it gives me the best of both worlds. Well done!

Otis wrote:

Also a possibility, though can still be slower. That is: if the main ACL read queries are slow. The best optimization starts with measuring what's the real bottleneck (like: if in-core processing of sets of ACL's is slower than fetching the data, it's better to optimize the ACL processing than to try to optimize the query reads.)

Ahhh the beauty of profiling... Don't you just love it! I remember getting a 24% improvement by changing 2 lines of code!! (http://www.styledesign.biz/weblogs/macinnesm/archive/2004/06/27/174.aspx).

Thanks for your suggestions - Really helpful as usual!

Marcus wrote:

Otis wrote:

You can though also try to formulate the things you want with scalar queries, which effectively will offer you the processing on the server (don't know if they're useful in your situation)

Instintively I tend to shy away from getting the DB to do anything that can be done in the BL layers since the DB is ultimately going to be the bottleneck and the most expensive in terms of MS License costs when scaling...

True, but if you have to process a lot of data with simple statements, inside the DB can be faster because it's optimized for set processing and you don't have to transport the data to/from the db.

Otis wrote:

How about this: you create a table for cache control. Whenever ACL's are changed, you set a flag in there and have a timestamp in there as well (flag setting, can be something else, as long as the timestamp changes when ACLs are updated). For every request, in the global.asax code behind, you fire a scalar query on that system table. If the timestamp is different from the timestamp of the cached version of the table, the ACL's also cached on that server, are out-of-sync, and you refetch them. This thus takes a very simple query which is fired for every request, and a bigger query which is fired when ACL's change.

You can then use the cached ACL's in the app, the global.asax code takes care of the maintenance, and if you add a trigger to the ACL table, you have no maintenance and no extra code in the ACL maintenance code.

Great suggestion! I will definately look into this one as it gives me the best of both worlds. Well done!

Otis wrote:

Also a possibility, though can still be slower. That is: if the main ACL read queries are slow. The best optimization starts with measuring what's the real bottleneck (like: if in-core processing of sets of ACL's is slower than fetching the data, it's better to optimize the ACL processing than to try to optimize the query reads.)

Ahhh the beauty of profiling... Don't you just love it! I remember getting a 24% improvement by changing 2 lines of code!! (http://www.styledesign.biz/weblogs/macinnesm/archive/2004/06/27/174.aspx).

Whoa! Yes, it's often overlooked how slow some loops can be if you keep on calling into what seem like simple properties or simple methods.

NProf is great, with it I found that datareader.IsDBNull() is extremely slow. If you do datareader.GetValue(ordinal)==System.DBNull.Value, gains 20% or so. Very odd.

Otis wrote:

Whoa! Yes, it's often overlooked how slow some loops can be if you keep on calling into what seem like simple properties or simple methods.

NProf is great, with it I found that datareader.IsDBNull() is extremely slow. If you do datareader.GetValue(ordinal)==System.DBNull.Value, gains 20% or so. Very odd.

Yes very odd indeed! At least they had the good conscience to make it a method rather than a property…

Marcus wrote:

Otis wrote:

Whoa! Yes, it's often overlooked how slow some loops can be if you keep on calling into what seem like simple properties or simple methods.

NProf is great, with it I found that datareader.IsDBNull() is extremely slow. If you do datareader.GetValue(ordinal)==System.DBNull.Value, gains 20% or so. Very odd.

Yes very odd indeed! At least they had the good conscience to make it a method rather than a property…

Heh, unlike the drones hammering out the code for bitmap.Height/width