Command Timeout Error

  • Thread starter Thread starter Eric Renken
  • Start date Start date
E

Eric Renken

Yes, you are all thinking another person who needs to set the CommandTimeout
property. Well this time I don't think so; however setting it longer does
fix the problem, but it isn't the answer, because in a large user web site I
can tie up SQL that long.

I have a stored procedure that when called from Query Analyzer returns
within a second. When that same EXACT query is called from C# ADO.NET using
the SqlClient or OleSbClient it takes about 40 seconds to return. I think
it has something to do with a "indexed view". We ran the Index Tuning
wizard on the stored procedure and its recommendation was this "indexed
view". This improved the speed and took it down to 1 second. Before that
it took over a minute to execute. Is there a problem with ADO.NET, and ADO
2.7 as we tried that as well , calling stored procedures that could use an
indexed view. It seems that it seems that the query optimizer isn't
allowing for the use of the view.

This problem happens when you call it from C# Windows app, C# ASP.NET app,
and a VB 6 app. I think it is a problem with how ADO is calling SQL. It
could be a configuration problem and that would be great as that would be an
easy fix.

We are running VS 2003 .NET 1.1 and our SQL Server is SQL 2000 with SP 3.

Any help on this would be appreciated, as I need to get this fixed SOON.

Eric Renken
 
Eric Renken said:
Yes, you are all thinking another person who needs to set the CommandTimeout
property. Well this time I don't think so; however setting it longer does
fix the problem, but it isn't the answer, because in a large user web site I
can tie up SQL that long.

I have a stored procedure that when called from Query Analyzer returns
within a second. When that same EXACT query is called from C# ADO.NET using
the SqlClient or OleSbClient it takes about 40 seconds to return. I think
it has something to do with a "indexed view". We ran the Index Tuning
wizard on the stored procedure and its recommendation was this "indexed
view". This improved the speed and took it down to 1 second. Before that
it took over a minute to execute. Is there a problem with ADO.NET, and ADO
2.7 as we tried that as well , calling stored procedures that could use an
indexed view. It seems that it seems that the query optimizer isn't
allowing for the use of the view.

This problem happens when you call it from C# Windows app, C# ASP.NET app,
and a VB 6 app. I think it is a problem with how ADO is calling SQL. It
could be a configuration problem and that would be great as that would be an
easy fix.

We are running VS 2003 .NET 1.1 and our SQL Server is SQL 2000 with SP 3.

Any help on this would be appreciated, as I need to get this fixed SOON.
Click to expand...

Ahh, the ugly side of indexed views. From BOL:


<quote>

Indexed Views
Indexed views store the result set returned by a view by creating a
clustered index on the view. For complex views, the stored result set
greatly speeds data retrieval. An indexed view is useful only as long as all
operations referencing the view use exactly the same algorithms when
building their results.

.. . .

SET Option Settings
Any SET options that affect the results generated by Transact-SQL statements
must have the same settings for all operations referencing the index. There
are seven SET options that affect the results stored in computed columns and
returned by views. All connections using indexes on computed columns or
indexed views must have the same settings for these seven options:
These six SET options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.

These SET options must be set correctly for any connection that creates an
index on a view or computed column. Any connection executing INSERT, UPDATE
or DELETE statements that change data values stored in the indexes must have
the correct settings. This includes bulk copy, Data Transformation Services
(DTS), and replication operations. Microsoft® SQL Server™ 2000 generates an
error and rolls back any insert, update, or delete operation attempted by a
connection that does not have the proper option settings. The optimizer does
not consider using an index on a computed column or view in the execution
plan of any Transact-SQL statement if the connection does not have the
correct option settings.

</quote>

But the SQLClient uses these settings (according to SQLProfiler)
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7


So after connecting each time you must issue

set ANSI_NULLS on
set ANSI_PADDING on
set ANSI_WARNINGS on
set ARITHABORT on
set CONCAT_NULL_YIELDS_NULL on
set QUOTED_IDENTIFIER on
set NUMERIC_ROUNDABORT off

Which you can do like this

.. . .
con.Open()
SQLCommand cmdSetOptions = new SQLCommand(" set ANSI_NULLS on set
ANSI_PADDING on set ANSI_WARNINGS on set ARITHABORT on set
CONCAT_NULL_YIELDS_NULL on set QUOTED_IDENTIFIER on set NUMERIC_ROUNDABORT
off",con);
cmdSetOptions.executenonquery();

David
 
Back
Top