"(CLOB) SELECT t1."Code", t1."Name", t1."Client", t1."InvestAIClosing", t1."DataSource", t1."FirstAnnualReportDate", t1."ComparativePeriodRule", t1."ComparativePeriodRuleCode", t1."TypeStructure", t1."TypeStructureCode", t1."CountryOfRegistration", t1."CommencementofOperationsDate", t1."TerminationDate", t1."DateRepresentation", t1."DateRepresentationCode", t1."IsMasterFund", t1."FeederOfMasterFundCode", t1."FeederOfMasterFund", t1."Currency", t1."CurrencyName", t1."InvestorDescription", t1."SharesUnits", t1."Condensed", t1."ReportingStandard", t1."Multiplier", t1."IsUmbrella", t1."UmbrellaOfFund", t1."ConsolidatedCombined", t1."InceptionDate", t1."InvestorPortal", t1."DynamicCaptioning", t1."Code2", t1."Code3", t1."Code4", t1."InvestmentsHeld", t1."LegalStructureDes", t1."ConsolidatedTo", t1."InvestmentManager", t1."AgreementDate", t1."ClosedEndDate", t1."CommodityPoolOpExemp", t1."FundName2", t1."CurrencySymbol", t1."FYStart", t1.IDFUND, t1.ISMASTER, t1.ISSLEVE, t1.ISLOOKTHROUGH
FROM (
    SELECT t2."Code", t2."Name", t2."Client", t2."InvestAIClosing", t2."DataSource", t2."FirstAnnualReportDate", t2."ComparativePeriodRule", t2."ComparativePeriodRuleCode", t2."TypeStructure", t2."TypeStructureCode", t2."CountryOfRegistration", t2."CommencementofOperationsDate", t2."TerminationDate", t2."DateRepresentation", t2."DateRepresentationCode", t2."IsMasterFund", t2."FeederOfMasterFundCode", t2."FeederOfMasterFund", t2."Currency", t2."CurrencyName", t2."InvestorDescription", t2."SharesUnits", t2."Condensed", t2."ReportingStandard", t2."Multiplier", t2."IsUmbrella", t2."UmbrellaOfFund", t2."ConsolidatedCombined", t2."InceptionDate", t2."InvestorPortal", t2."DynamicCaptioning", t2."Code2", t2."Code3", t2."Code4", t2."InvestmentsHeld", t2."LegalStructureDes", t2."ConsolidatedTo", t2."InvestmentManager", t2."AgreementDate", t2."ClosedEndDate", t2."CommodityPoolOpExemp", t2."FundName2", t2."CurrencySymbol", t2."FYStart", t2.IDFUND, t2.ISMASTER, t2.ISSLEVE, t2.ISLOOKTHROUGH, ROWNUM AS "rnum"
    FROM (
        Select * from vw_ais_fundmaster
        ) t2
    WHERE ROWNUM <= :p0
    ) t1
WHERE t1."rnum" > :p1"

My sql looks like this. and vw_ais_fundmaster is not a table, but a very complex view.

It's imporssible to reduce the sql excuting time.

The only way is find out a solution which do not need many times query.

Thanks for the code and the info, Spark!
Working with complex views can be a pain sometimes :(
(I know - I've written even bigger ones by hand :D)

I can give you a suggestion which you can try (I'll inspect it with SQL profiler later when I have the time).
The idea is to cache the results from your query to the database into a list and then convert that list to an IQueryable.
That way the grid's data processing will work on the list and not on the original IQueryable (which results in more SQL queries).

Here's a very quick sample of a controller action that illustrates the idea:

[GridDataSourceAction]
public ActionResult GetData()
{
    var gridSourceData = GetVirtualMachineData();
    gridSourceData.ToList();

    return View(gridSourceData.AsQueryable());
}

Hope you can give it a go and fingers crossed that it works for you!

Cheers,
Borislav

 

I find out a work around way: We new a GridModel object in conctroler, and set AutoGerenateColumns, AutoGerenateLayouts to false, then put it into TempData. And write our own MyPfsGridDataSourceActionAttribute. The code like this:

    public class MyGridDataSourceActionAttribute : FilterAttribute, IActionFilter
    {
        /// <summary>
        /// intercepts the controller call, and transforms the action result into a json result.
        /// </summary>
        /// <param name="filterContext"></param>
        public void OnActionExecuted(ActionExecutedContext filterContext)
        {
            if (filterContext.HttpContext.Request.IsAjaxRequest())
            {
                IQueryable dataSource = filterContext.Controller.ViewData.Model as IQueryable;
                     GridModel internalGridModel = filterContext.Controller.TempData["GridModel"] as GridModel;
                NameValueCollection queryString = filterContext.HttpContext.Request.QueryString;
                if (queryString["layout"] == null)
                {
                    internalGridModel.Features = GridDataSourceActionAttribute.InferFeatures(queryString);
                }
                internalGridModel.DataSource = dataSource;
                if (filterContext.Controller.ViewData["GenerateCompactJSONResponse"] != null && !(bool)filterContext.Controller.ViewData["GenerateCompactJSONResponse"])
                {
                    internalGridModel.GenerateCompactJSONResponse = new bool?(false);
                }
                filterContext.Result = internalGridModel.GetData();
            }
        }

In OnActionExecuted, instead new a InternalGridModel, we pull out the object from tempData.

By this way, we can avoid 2 times data load. But GridModle.CheckNullable() still can not be avoided.

I have done a profiling. GridModle.CheckNullable() takes 11 seconds. and the RenderFlatQueryable function takes another 11 seconds.

Do you have any suggestion for GridModle.CheckNullable()?

Thanks for you suggestion. But this solution takes another issue. If the amount of data is very big. I need to load them into memory, it's not what I want.

Can I handle the request and send back data by myself? I mean with out GridDataSourceAction. If I can, How can I do that?