Silverlight RIA DomainService with 2 million rows in a table

I'm doing a project that reads from an oracle database. I have used Silverlight RIA, and autogenerated DomainService since I'm not too concerned about structuring as I only worry about displaying the data.

My question is, that when I use the domaindatasource from the XAML, and use fiddler for debugging the WCF service and its calls, the first set of data from the table of useraccounts contains 2 million rows, and the DomainService times out.

Now I have tried increasing the timeout of the se开发者_StackOverflow中文版rvice to 20 mins, but still no avail, I get the error:

Load operation failed for query "GETUA_USERACCOUNTS". The http request to has exceeded the alloted timeout

Also out of the total 9 tables that I use, 3 tables have around 2 million rows, what would be the best method to approach this problem?

---

Use the ToTraceString method...

http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring.aspx

...or an Oracle profiling tool to determine the SQL statement that is being used and confirm that it takes a long time to execute.

Use query optimization techniques such as adding indexes to speed it up.

Alternatively, write a stored procedure that returns the required result in a more efficient manner.

---

To continue where TomTom left off, and Red asked, do your data filtering / processing on the server, before returning the results (Psuedocode)

public IQueriable<UserDTO> GetUserAccountDetails(string UserID)
{
    DataSet oneBazillionRows = SQLServer.GetAllUserRows();

    // LINQ to the rescue
    return from user in oneBillionRows
           where user.ID = UserID;
}

and your comsumer:

public void GetUserInfo()
{
    ServiceContext context = new ServiceContext();
    context.Load<UserDTO>(ServiceContext.GetUserAccountDetailsQuery(), load =>
    {
        // do some work here
        //like signalling the call is complete
        // or storing the data in your View Model
    }, null);
}

You consumer will then only recieve the one data row. The basic form is like so:

public IQueriable<ReturnType> WebService(Parameter parameter, ...)
{
    // Do all your work here, return minimal results
}

Consider: Invariably, the sever is going to be a lot beefier than your client machine. Let it do all the work of filtering / sorting / preprocessing results, and have it hand over minimal data. You will find your RIA implementations become much more snappy.

---

You should use a DataPager, see here: http://www.silverlightshow.net/items/Creating-applications-with-.NET-RIA-Services-Part-4-Adding-a-DomainDataSource.aspx

<navigation:Page x:Class="WebAdministrationTool.ManageUsers" 
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:ria="clr-namespace:System.Windows.Controls;assembly=System.Windows.Ria.Controls"
    xmlns:local="..."
    xmlns:validation="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data.DataForm">

    <Grid x:Name="LayoutRoot">

        <data:DataGrid x:Name="UsersGrid" 
                       IsReadOnly="True" 
                       AutoGenerateColumns="True"
                       ItemsSource="{Binding Data, ElementName=MyDataSource}" />

        <validation:DataPager x:Name="UsersPager"
                              PageSize="10" 
                              Source="{Binding Data, ElementName=MyDataSource}" />

        <ria:DomainDataSource x:Name="MyDataSource" LoadMethodName="LoadHugeAmountOfRows">
            <ria:DomainDataSource.DomainContext>
                <local:MyDomainContext />
            </ria:DomainDataSource.DomainContext>
        </ria:DomainDataSource>

    </Grid>

</navigation:Page>

---

I had similar issue and to handle this i created stored procedures on my database to do the work and only spit back the information i needed. There is not a lot of info on adding stored procedures to RIA but here is a shot on what i know works.

1. create your stored procedure
2. update your Database Model in your project to include the stored procedure
3. in Model Browser right click and add function import. here you get to name and decide how the data is returned (make it easy on yourself and try to return an object already in your DomainService.metadata.cs file)
4. if creating new return object add it to your DomainService.metadata.cs file

5. in your domain service add a public method that returns the result list.

   public IQueryable<FWCUser> UpdateFWCUserWithUserProfileID(int userProfileID, int fwcUserID)
   {
      return this.ObjectContext.UpdateFWCUserWithUserProfileID(userProfileID, fwcUserID).AsQueryable();
   }
   

6. build your project and call the method as needed from code behind

   FWCDomainContext context = new FWCDomainContext();
   context.Load(context.UpdateFWCUserWithUserProfileIDQuery(num1, num2), Completed, null);