Timeout expired error executing a simple View in SQL Serve..

Hi

We are building a SQL Express database for embedded use in our application
and hitting problems executing simple Views executed from SSQL Server
Management Studio Express SP2 (SSMSE) and from Access XP via ODBC.

The MDF has a single table with 20 or so fields that are mainly text (100
char), plus a couple of datetime fields and an integer field as the primary
key that auto-increments. The table has over 7m (million) rows and the MDF
is close to capacity at 3.9Gb. Aside from the PK field, only the datetime
and integer fields are indexed.

Next we started to create Views with various filter criteria to assess
performance and immediately hit problems with query timeouts with simple
criteria such as LogDate >= 1/1/2008 AND <= 1/2/2009 (dates shown as entered
into the grid rather how the appear in the Where clause)

The error we get is:

SQL Execution Error. Timeout Expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.

Simplifying the criteria to [LogDate] > 1/1/2008 quite happily returns all
7m rows but [LogDate] = 1/1/2008 gives a timeout error. But [LogDate] <>
1/1/2008 returns everything EXCEPT 1/1 as you'd expect.

Ok, we thought maybe this is date field specific but criteria against an
integer field generates the same error: [IntegerField]=22 fails whilst
[IntegerField]<>22 works as does [IntegerField]>22.

Althought the error message is consistent the results returned do vary from
View to View (each View having different criteria applied); in some cases no
records are retrieved and the error is quick, in other cases the View
appears to be nearly complete before failing on the last hundred or so rows.
With some criteria this might be 5,000 rows and with other it might be
500,000. Saving and re-running any given View many times gives consistent
results every time for that View, i.e. the results and the error are the
same every time whilst the View is unchanged.

Any amount of criteria against text fields seems to produce the results we
would expect but simple criteria against datetime and numeric fields mostly
fail.

We've increased the various timeouts available in SSMSE (up 600 seconds
mainly) but we get timeout errors occuring after anything from 18 seconds up
to 2.5 minutes, depending on how many rows get returned before each View
fails. We've added, removed and rebuilt the indexes but nothing has any
impact.

In an attempt to isolate SSMSE we ran same tests from MS Access XP via an
ODBC "linked table" and get same errors there. Access quite happily pulls
across all 7m rows from the table but ttrying to connect to the Views that
timeout in SSMSE also cause a timeout when opened from Access.

BTW, I should add that taking the SQL from any of the failing Views and
executing as a Query in SSMSE works jsut fine without any timeouts and
returns the exprected number of rows... this suggests it's not a basic
syntax issue.

After trawling the web and following every lead we're at a dead end. Common
sense tells me that SQL Expr would be of very limited utility without Views
to filter the data and that we must be doing something wrong... but I sure
can't see what after many days of looking at this issue and any pointers
from a seasoned user would be *extremely* welcome.

Thanks in advance.

Mark

Having similiar issue. Simple query in SQL 2005 express works fine and times
out in a view.

Changed all time out settings (connection and command) by not working. Do
not believe this is the problem.

Just wondering if you learned anything that may help.

Kent

url:http://www.ureader.com/msg/11431407.aspx