Very Slow ServerFilter

[Fubear]

I have a subform that needs to display a subset of recods depending on what values are entered in the parent form.

The system uses an Ms SQL backend, and the query is a stored view on the server of ~ 13,000 records

When i display an unsorted view, the subform loads all 13'000 records in about 2 seconds, but when I apply a serverfilter and refresh, then i get about 200 records per second. This means that a filtered form of 7000 records takes about 30 secs to fully load the data.

I thought the SQL server was designed to perform these sorts of queries quickly. I'm not quite sure how to find out how to speed it up as the filter im applying is external to the view.

An example of what a filter would look like:
((Code<>'A')AND(Date BETWEEN '10/01/2002' AND '12/31/2003'))

That filter would return 7500 records, that largest amount possible to return with one of the filters.

 

[cmmrfrds]

Is this done in the same subform as the slow filter?
&quot;When i display an unsorted view, the subform loads all 13'000 records in about 2 seconds&quot;

Or is it done straight from the view?

 

[Fubear]

From the subform

Without a ServerFilter set - all records are loaded in just a few seconds. When I apply a serverfilter then it slows right down (The form should have a serverfilter applied at all times).

 

[Fubear]

After a lot of testing differant combinations of filters - I have determined that the &quot;slowness&quot; was caused by the date filtering.

It is really slow when calculating BETWEEN Date AND Date, or >Date and <Date.

What I have done is written some quite confusing SQL that uses the datepart function to extract the DayOfYear and Years of the two dates to compare. Its a little harder to code, but its a lot faster.

 

[cmmrfrds]

Are all the fields used in your criteria indexed on sql server?

I would be suspicious of the <> which will probably force a full table scan and negate the value of any indexes.

There should be away to get around the <> , can you show the basic where criteria and the tables involved?

Does your date in the database include time? That would effect how sparse the index is on the date.

 

[Fubear]

The <> works fine - its the date bit which was slow. Yes it does include time, but i need the time part as well.

Using the datepart function to extract the relevant components to compare works fine, its just some VERY complex (from my point of view) VB to get it to do so.


I cant belive im still thinking of work on a saturday

 

[cmmrfrds]

When you said &quot;stored view&quot; is this a stored procedure residing on sql server?

Paste in the sql query and we can take a look.

 

[Fubear]

Yes - I meant view on the SQL server combining the tables I need to display the data