This is a migrated thread and some comments may be shown as answers.
How to display the table data by joining 3 tables as taking too long to retrive and list the data
0 Answers 12 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
supriya
Top achievements
Rank 1
supriya asked on 25 Mar 2011, 02:15 PM
 

  Hi..
I have a MVC application in which I need to display the data from 3 tables. I am using entity model for it. Out of these in 2 i have made the association:users and payment table

And 3rd table month_<monthid> is created every month to store the users to whom the magzine is sent. The table name month_<monthid> is generated dynamically by selecting the month so in order to fetch the data i have used ExeuteStoreQuery.  For small amount of data the listing is fast but for large amount it is very slow.

Now i have created a class to bind to grid which will include all the fields from the 3 tables to display.
But here when i am getting the large volume of data  about 12000 then it is taking about 30 min to go through the loop and assigning the data to the class object and then adding to the list of the result which is finally binded to telerik grid.


I am here by attaching the sample code using a link .Is there any direct way to bind the query result of joined tables to grid instead of going through the loop and preparing the list for the model class i think that will save time
The code block of preparing the list using the Executestorequery is under the function GetuserList().
foreach (var r in result)
                {

                    Result  objresult = new Result();
                    var paymentresult = from sub in dtpayment.AsEnumerable() where sub.Field<int>("user_id") == r.user_id select sub;
                    if (paymentresult.Count() > 0)
                    {
                       
                        objresult.amount_paid = paymentresult.FirstOrDefault().Field<decimal>("amount_paid");
                        objresult.magzine_id = paymentresult.FirstOrDefault().Field<int>("magzine_id");
                    }
                    objresult.address=r.address;
                    objresult.email=r.email;
                    objresult.name=r.name;
                    objresult.user_id=r.user_id;
                    objresult.month= smonth;
                    lstresult.Add(objresult);
                }

This code block of for loop is taking very time where i am using ExceuteStoreQuery.
But i have observed that simply by joining the users and payment table using LINQ query to get all the 12000 records i.e no involvement of month table the result is appearing faster.
So,can you suggest any way to improve the performance of my application.

Also include the databse structure with the sample
Below is the link to sample

http://sampletestone.s3.amazonaws.com/magzine.7z?AWSAccessKeyId=AKIAINHDRCMKC5GUSNFA&Expires=1303583399&Signature=8o8Wn6UNjbEl3dIyipAX9xH29Hg%3D

Thanks in advance
supriya
Tags
Grid
Asked by
supriya
Top achievements
Rank 1
Share this question
or