RE: Timeout Expired

Dave,

Try creating the query you are running via ODBC as a stored procedure in the
DB. This will allow SQL Server to create and store an optimized execution
plan. That way the next time the stored procedure is run, the execution plan
will aready exist.

When you pass a query to SQL via ODBC the SQL server first creates an
execution plan - for large queries this can add signifigant time.

After creating the stored procedure - try running the stored procedure from
the SQL Query Analyzer and see if does not execute faster that the query.

You also mentioned that the query returns 21K rows? Do you need them all?
You could also try returning only the top 100, 1000, 10000.... you get the
idea.

As for bumping up the timeout period... what are you using the ODBC
connection on? There is deffinitely a way to do what you are asking in .asp.
I am sure the same sort of thing exists for other platfoms. In .asp you
need to change a setting in the connection object. Do a google search for
connection ado timeout... and see if that doesn't help out.

Hope this helps,
Simon



"Dave Pylatuk" wrote:

> Hello all.
>
> I have a table in SQL Server 2000 with 3.5 million rows in it, approximately
> 20 columns. I am querying this table via ODBC using the microsoft driver.
> Certain
> queries fail and return a 'Timeout Expired' message. I have a non-unique index
> on the column I am referencing in my WHERE clause. I have determined that
> the query takes approximately 1.5 minutes to complete when run through Query
> Analyzer and returns 21,000 rows.
>
> Is there a way to improve the efficiency of this index ? Is there a way to
> bump
> up the timeout period on the server or on the ODBC connection ?
>
> Any help would be appreciated,
> Thanks
.

Relevant Pages

  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server - Filter
    ... Happy to use a stored procedure but prefer to use a View that can be linked ... >> the sql server tables from access you are pretty much defeating the idea ... > many fewer bugs if they'd just used Access queries for the most part. ... > to query data from the back-end. ...
    (comp.databases.ms-access)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: ADP Help
    ... And the stored procedure would be something like: ... > this, if all the SQL statements are on the SQL Server, how would the SQL ... I use that query as a base ... >>> query for almost all of my reports. ...
    (microsoft.public.access.adp.sqlserver)