"Eric Renken" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
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
|
Similar Threads
