Retrieving and handling Multiple recordsets at same time

Topics: Administration, Core, Customizing Orchard, General, Writing modules
Jun 25, 2013 at 12:43 PM
Hi,
I am creating a widget which requires a lot of data from different tables to be displayed. also there is a lot of logic involved before the data could be displayed in UI. If I am not wrong in Orchard the Nhiberanate retrieves only one record set at a time.

So I have decided to not to use the default but create Stored Procedure which will return me multiple record sets at a time. This will improve my performance a lot.

I have some questions on this?
? How to hide the default database call and how to call my custom database routines inside Orchard.
? How and where I can map the multiple record sets returned by my Stored Procedure with respective Content parts.

If someone have any code sample for this will be of great help.


Thanks.
Coordinator
Jun 26, 2013 at 1:42 AM
It seems like you are making a lot of assumptions, some if not all of which are unwarranted. In particular when talking about performance, you need first to demonstrate that you do have a bottleneck before you go against every Orchard choice and convention in order to fix it. There are so many reasons not to use stored procedures that you should be extremely sure that you do have a problem and that this problem can't be dealt with in a more elegant way.
This has a very strong smell of premature optimization.
Jun 26, 2013 at 4:17 AM
Hi Roy,
My intention is not going against the Orchard convention, but my requirement requires data from many tables for the same widget. There are more than 30 tables which are used to display data on the screen. If I do not use stored procedure I may have to do more than 10 calls to DB which is too much of network traffic for a full blown site. This is how more than 95% of pages will be in my site. So I decided to use Stored procedure so that I can group all the above 10+ calls in a single Stored procedure. If you still feel this is a wrong approach can you please guide me through a more realistic way. Else if you feel that my decision based on requirement is correct can you please guide me through my previous queries.
Coordinator
Jun 26, 2013 at 4:26 AM
My recommendation is to apply good programming practices: first make it work, then profile and optimize sensibly.

Are those 10 calls to the DB completely separate and unrelated queries? Why would it represent so much traffic that letting the standard logic in nHibernate do its job would be so much slower? How far is the DB from your web server? Is there any caching you can do that would be more efficient than rewriting the whole data access logic in Orchard?
Jun 26, 2013 at 9:19 AM
Hi Roy,
All these call are separate and unrelated queries getting different data. I cannot cache data because it depends upon many factor such as page id, language etc. Too many factors come in picture. Do you think the customization which i am trying to achieve above is not possible in Orchard or it will break the core of the Orchard.
Coordinator
Jun 27, 2013 at 12:19 AM
What I think is that you're doing premature optimization and are choosing the most difficult path for yourself, with uncertain results. As I said before, I would first develop the feature, then profile it and apply perf improvements as appropriate. Before you attempt to fix a problem, you need to know that there is a problem and to know what it is exactly.
Jul 15, 2013 at 4:28 PM
Hi Bertarnd,
Considering my scenario where I want to develop a widget with following features. Can you please guide me (if possible with example) how to build this widget?
  1. The widget have a tab control kind of structure.
  2. Each of these tab display the data from different table in different format.
  3. When the page load with this widget in it the first tab data will be pulled from displayed by default.
  4. When user clicks on any other tab a Ajax call should be made to pull the latest data for that tab.
Can you please guide me how to build such control in Orchard.
Jul 15, 2013 at 6:23 PM
If I may make a suggestion, I would start with writing a simple widget first to see how that works. Once you are confident with that, you can write any widget you want. There are a lot of good references on how to make AJAX calls using jQuery. When you have specific questions, please do let us know.
Coordinator
Jul 15, 2013 at 7:54 PM
Edited Jul 15, 2013 at 7:55 PM
You'll need a simple MVC or WebAPI controller with actions for each of the queries, returning a JSON result.
Jul 16, 2013 at 5:37 PM
Thank you both sfmskywalker and BertrandLeRoy. I have started working on my widget will ask questions if get stuck somewhere.