Server timeout expired

[Mahen]

A query (containing 4 left outer joins) takes above 6 minutes to
return a recordset of 212 rows.
I am passing a sql string from MS Excel 2003 to MS Sql Server 2000 to
run the query and return the recordset to a spreadsheet. I get the
message "Timeout expired" from Excel.

The database is from a legacy application which keeps transaction data
in separate tables for each financial year. Hence, to use stored
procedures would mean having one for each financial year, whereas I
can conveniently change the table name in a cell in the spreadsheet
based on the year required.

Please help. Thanks a lot

 

[Erland Sommarskog]

A query (containing 4 left outer joins) takes above 6 minutes to
return a recordset of 212 rows.
I am passing a sql string from MS Excel 2003 to MS Sql Server 2000 to
run the query and return the recordset to a spreadsheet. I get the
message "Timeout expired" from Excel.

The database is from a legacy application which keeps transaction data
in separate tables for each financial year. Hence, to use stored
procedures would mean having one for each financial year, whereas I
can conveniently change the table name in a cell in the spreadsheet
based on the year required.

The timeout is defined by Excel, or more exactly by ADO which it presumably
uses. I don't know if there is a way to change it. (But I don't know much
about connecting to SQL Server from Excelt at all.)

Changes are good that it's possible to speed up your query so that it
completes with the 30 seconds which is the default timeout. But in order
to give suggestions, I would need to see the table definitions, including
the indexes. And of course, I would need to see the query as well. It would
also help to see the current query plan.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mahen]

The timeout is defined by Excel, or more exactly by ADO which it presumably
uses. I don't know if there is a way to change it. (But I don't know much
about connecting to SQL Server from Excelt at all.)

Changes are good that it's possible to speed up your query so that it
completes with the 30 seconds which is the default timeout. But in order
to give suggestions, I would need to see the table definitions, including
the indexes. And of course, I would need to see the query as well. It would
also help to see the current query plan.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for your response. I have solved the problem on two levels:
1) While configuring the ODBC data source connection to SQL server, I
have set the "Save long running queries . . .(Long query time)"
parameter to zero.
2) I have increased the RAM on my machine, I was using a notebook
(with a personal edition of SQL server) with only 540 MB of ram. It
now has 2GB.
With both these solutions, the query now takes only 2 seconds!

 

[Erland Sommarskog]

Thanks for your response. I have solved the problem on two levels:
1) While configuring the ODBC data source connection to SQL server, I
have set the "Save long running queries . . .(Long query time)"
parameter to zero.

Ah, you found the switch. That's great. The default timeout of 30 seconds
is even better.

2) I have increased the RAM on my machine, I was using a notebook
(with a personal edition of SQL server) with only 540 MB of ram. It
now has 2GB.
With both these solutions, the query now takes only 2 seconds!

Sometimes it does help to throw hardware at the problem. Yes, 540 MB of
RAM for both SQL Server and Excel is a tad low.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx