.nettiers datasource does not apply filter on query

Question

I have a table with 13K records and a sql machine a little old.

In the problematic page I have a netTiers datasource, that goes in timeout. My problem is that at page_load i set the filter: vwImmobileCommessaAttivoDataSource.Filter = "ImmobileId = '" + Request.QueryString["ImmobileId"] + "'"; that change the result number from 13K to 10. But I see the generate query is:

                BEGIN

                SELECT * FROM [dbo].[vwImmobileCommessaAttivo] 
                ORDER BY [CommessaId]

                -- get total count
                SELECT @@ROWCOUNT AS TotalRowCount;

                END

without the filter, and goes in timeout.

There is a way to accelerate the query? including my filter?

Answer 1

I personally use DevExpress grids and bind directly to them using something like:

grid.DataSource = new vwImmobileCommessaAttivoService().Find(string.format("ImmobileId='{0}'", Request.QueryString["ImmobileId"]));
grid.DataBind();

If your filter is only going to drop you from 13k to 10k and you are timing out at 13k records, then I think you are going to need to look into some form of paging option to return fewer records. If you enable SQL2005 features there is some paging built in, but I have never personally tried it, so I do not know how well it works. The default Nettiers paging still gets all records from sql and does the paging internally.

In terms of your existing code actually doing what you expect. .Filter only works if both EnablePaging and EnableSorting are set to false. Have you instead tried:

vwImmobileCommessaAttivoDataSource.SelectMethod = vwImmobileCommessaAttivoSelectMethod.Find;
vwImmobileCommessaAttivoDataSource.WhereClause = "ImmobileId = '" + Request.QueryString["ImmobileId"] + "'";