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,

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



[quoted text, click to view]