Hi,

I was wondering what would lead to the best performance for the next situation. I have a product table and a product variants table. So T-shirt with sizes X, XL, M, S. I would like to show all these products including their sizes in a grid so visitors can see what products and sizes are in stock. I am wondering what is the best way of collecting this data from the database? I assume I have 100 products and every product 4 variants. So 400 dataitems in total.

I have a productcontroller() and a productvariantscontroller()

1. Using DAL of DNN. So I call productcontroller() to get products and then on the databind I do a get to collect with the productvariantscontroller() for each product the variants. So I do: 1 fill with 100 returning products and 400 calls to fill these 100 returning products.: 1 + 400 = 401 database calls.

2. I am using dataset: I call 2 times the database: 1 to fill products and 1 to fill productvariants: so 2 database calls.

3. I can also create a class with has product + variants information in it and with a table join on the database I collect this information. However, the product information id redundant.. but that would be option 3.

In my opinion 2 is fastest, or does it not matter? Or is there a way to bind relationships with the DAL of DNN?

Any help from some application architects would be great!

J.

J.

In general a datareader should be faster than a dataset, regardless of how you implement it.  Yes, the datareader can contain only one result set but since I don't know how you present the data I don't know if it can be accommodated.  Maybe you can create a view in the database with the consolidated query you need and then populate your grid from the view instead of the tables directly.  In this way the relationship business is done at the database but again, I don't know if it applies to the way you are doing things.

Also, if you are doing paging with the grid, you should consider optimizing the paging so that you don't retrieve all the records every time.  There are many papers on how to do this.

And by the way, I am not too fond of the DNN DAL, too much work, and it may make some of the ideas above harder to implement.

Carlos

Hi,

Can I conclude that the DNN DAL is faster with datareader then the dataset of Microsoft?

I am interest to know how the relationship of Charles' approach is being created? The disadvantage of this JOIN query is that the product data is added to the data output of the database towards the web server. The amount of data is quite a bit.

What about collecting with a datareader first the product data and then in a cached datagrid collect on the databinding the variants one by one? Or is this a database killer? I am afraid too many database calls care much heavier than a single call. But how to combine with a relationship the products and variants of 2 data readers and the DNN dal. I normally love to connect every with JOINS but that makes you have to create custom dals since I fully generate the DAL of DNN with code smith template from a single table.

Any ideas if a relationship can be make on DNN dal objects?

J.