|
|
ITFRED, have you found a solution to your problem at all??
I have a similar problem, I also get the *occasional timeout*.
Mine is an ASP web application connected to MSDE, and the application
gets a timeout error occasionally when the ASP code tries to update
many records in a table, but smaller tables work fine.
The error message is:
> Error Number -214xxxxxx, ODBC SQL Server Driver timeout expired.
In a testing environment on a dev server, the same ASP application
connects to a SQL2K database and it has NOT encountered any problems.
My conclusion is (thanks to all your posts I have read) that it is in
ODBC connections where the MSDE engine times out.
Can anyone please come up with a solution!??
Is using a different database an option, perhaps mysql or postgreSQL?
Thanks.
huge
ITFred wrote:
> *I have been struggling with a similar problem for a couple of weeks
> and
> came
> to this group in search of a solution. Perhaps a pooling of
> information
> will help.
>
> In my situation, I have a VB interactive front-end on multiple
> machines
> accessing a SQL2K database through ODBC over TCP/IP. It has been in
> service
> for over 2 years and the master table now has over 750K records in
> it.
> Recently we started receiving occasional "Timeout Expired" errors in
> pretty
> much the same manner as you describe --- a query that would normally
> take
> 1-2 seconds would suddenly take over 90 and crap out. I ran
> database
> integrity checks, reconstructed the indexes, created additional
> indexes,
> ran
> numerous original and reconfigured queries through the Index
> Analyzer. And
> of course spent hours trying various searches in the MS KB. I have
> reached
> the following conclusions:
>
> The problem (in my case, at least) originates with the Query
> Optimizer. A
> very specific query run through the Query Analyzer will *always*
> select an
> appropriate index or mix of indexes. The *identical* query
> submitted
> through ODBC, however, will sometimes select a completely
> inappropriate
> index mix, or no index at all, wind up executing full-table scans or
> other
> time-wasting substitutes, and time out. This effect, I suspect, is
> a
> permanent feature of MSSQL, but does not manifest on smaller tables
> because
> the malfunction does not cause a timeout on smaller tables, just an
> inexcusable waste of time.
>
> I proved this theory by adding index hints to my ODBC-originated
> queries
> (which Books Online says should never be necessary), and observing
> the
> performance stabilize. Problem is, I consider this a completely
> hokey
> solution since if I ever decide to reconfigure my indexes, I will
> have
> dozens of coordinating code changes to perform. Also, there are
> several
> queries in which I cannot use index hints because the query requires
> column-level 'OR'-ing with which hints are incompatible.
>
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment from MS that this is a behavioral issue
> with
> SQL2K that needs to be addressed.
>
> - ITFred *
--
huge
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message397556.html
|
|