DataView Rowfilter Speed

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joseph (_at_discussions.microsoft.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 14:13:07 -0800

My application allows the end user to enter search criteria to limit the
information displayed in a datagrid. The code executes a stored procedure
and populates a dataset with a single table.

A dataView is created from that table which is bound to the data grid. For
example:
'Run the stored procedure and return data set.
DVEds = objDVE.GetPrimaryDataView(StoredProcedure)
'Create a data view from the data set
DVEdv = New DataView(DVEds.Tables(0))
DVEdv.AllowDelete = False
DVEdv.AllowEdit = False
DVEdv.AllowNew = False
'Bind the grid to the data view
grdMainView.DataSource = DVEdv

So far - so good. A user can then select a field from a list and enter some
search criteria. The criteria is transformed into an SQL statement and the
rowfilter is updated:

Dim intFieldType As Integer
intFieldType = Me.DVEds.Tables(0).Rows(0)(Me.cmbSearchField.Text).gettypecode
strRowFilter = BuildSQL(cmbSearchField.Text, intFieldType, txtSearch.Text)
Me.DVEdv.RowFilter = strRowFilter

No problem - this all works fine. It takes some time to populate the
initial dataset, but then the row filter works well enough.

I'm looking about 70,000 records. The problem occurs after entering several
searches. For example,
FirstName="Mike" (takes 2 seconds)
FirstName="Fred" (takes 2 seconds)
Firstname="Mike" (takes 2 m-i-n-u-t-e-s)

I've simplified this for this request but the basic problem is that some
rowfilter searches are resolved quickly, and some take minutes and there is
no pattern with regard to whether the search returns a large or small number
of records, or whether the search is higher or lower or how the data is
sorted. Sometimes, I can execute 10 searches with no problems, and then, a 2
minute delay.

I realize the rowfilter is rebuilding the index - and that filtering would
be faster through the stored procedure - but I'd like to know the dymamics of
what make the rowfilter work - and what consideration I can give to making it
perform consistantly.

Thanks!

Relevant Pages

  • Access ADP Form and Stored Procedure as RecordSource
    ... Me.RecordSource property, to a second Stored Procedure, in code when ... the Search button was clicked and include all the Input Parameters ... from the search criteria the user would input. ... What I realized is you can't change the RecordSource from one Stored ...
    (microsoft.public.access.queries)
  • Access ADP Form and Stored Procedure as RecordSource Options
    ... Me.RecordSource property, to a second Stored Procedure, in code when ... the Search button was clicked and include all the Input Parameters ... from the search criteria the user would input. ... What I realized is you can't change the RecordSource from one Stored ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Stored procedure and query optimization
    ... > users should be able to lookup data by any of the columns. ... Write a generic stored procedure that will fit any search criteria: ... > and we should choose the second option because of its significant ...
    (microsoft.public.sqlserver.programming)
  • Re: TADOQuery, OnFilterRecord and Locate
    ... How much work is being done in the stored procedure? ... skills, plus up to 20 other search criteria. ... design pattern like the Decorator pattern)? ...
    (borland.public.delphi.database.ado)
  • Re: TADOQuery, OnFilterRecord and Locate
    ... How much work is being done in the stored procedure? ... skills, plus up to 20 other search criteria. ... design pattern like the Decorator pattern)? ...
    (borland.public.delphi.database.ado)