Pass Thru Times out

[Guest]

Criteria Section:
WHERE (((RESC.func_biz_num)='41322')
AND ((SCH.TIME_FY_DATE) >= '1/7/2007'
AND (SCH.TIME_FY_DATE) <= '1/13/2007'))


I have a passthru query to SQL Server which is the "backend" (MS Project is
the front end). The PassThru works well with many func_biz_num. However,
41322 (above) has a larger numbers of records per month.

1. If I keep the date range under 15 Days, the query runs pretty well (5-10
secs) and returns about 15,000 records (30,000 records for 30 days).

2. If I increase the date range above 15 days, the query will time out
after about 60 secs.

3. If I remove the <= part of the filter or if I remove both the <= and > =
part of the criteria, the query runs pretty well and returns massive numbers
of records.

4. If I remove the date filters from the passthru and put the date filters
in an Access query (for 30 days of activity) attached to the passthru query,
the Access query will run (and not time out) but is takes about 60 seconds
to run (this is approximately what Access queries will do without the
Passthru.

Questions:

1. Why does this query work for < 15 Days and then immediately, say going
from 15 days to 16 days, time out (like a critical mass thing)?

2. How can I get this query to work? Is there a workaround.

Thank you in advance

Ross

 

[Sylvain Lafontaine]

You can solve the time-out by increasing the maximum time allowed. I don't
know MS Project, so I cannot tell you how to increase this time-out.

However, in your case, maybe you have a problem on the server side because
of a missing index. If SQL-Server is doing a full table scan because of a
missing index on either RESC.func_biz_num or SCH.TIME_FY_DATE; this could
explains a lot.

Using these two indexes as the clustering index for the tables could also
change a lot of thing in term of performance. Not using a clustering index
or using it exclusively for the primary key is not necessarily the best
thing to do.

You should absolutely run these queries in the Query Analyser or the
SQL-Server Management Studio to take a look at the query plans generated by
SQL-Server for each of these cases. Make sure also that the statistics have
been updated and that the query plan cache has been purged.

 

[Guest]

Excellent plan!

Thank you!

You can solve the time-out by increasing the maximum time allowed. I don't
know MS Project, so I cannot tell you how to increase this time-out.

However, in your case, maybe you have a problem on the server side because
of a missing index. If SQL-Server is doing a full table scan because of a
missing index on either RESC.func_biz_num or SCH.TIME_FY_DATE; this could
explains a lot.

Using these two indexes as the clustering index for the tables could also
change a lot of thing in term of performance. Not using a clustering index
or using it exclusively for the primary key is not necessarily the best
thing to do.

You should absolutely run these queries in the Query Analyser or the
SQL-Server Management Studio to take a look at the query plans generated by
SQL-Server for each of these cases. Make sure also that the statistics have
been updated and that the query plan cache has been purged.