Fast SQL Server Stored Procedure runs slow in VB.NET

[fniles]

Our application uses VB.NET 2008 and SQL Server 2000.

I can run the SP fast in the SQL Server Management Studio or SQL Query
Analyzer window.
But, when running it in the VB.NET program, it is very slow.
Why is it slow in the program ?
Is there anything else that I could do ?

Thank you

This is the VB6.NET code:
Sql = "exec GetData1Min @Symbol='EUR/USD',@SeqNumLow='20100324-0000'"
connectionString = "Data Source=" & _settings.DataSource & ";Initial
Catalog=" & _settings.Database & ";User ID=" & _settings.UserID &
";Password=" & _settings.Password

Using connection = New SqlConnection(connectionString)
connection.Open()

Using command = New SqlCommand(sql, connection)
Dim reader As SqlDataReader

command.CommandType = CommandType.Text
command.CommandTimeout = 300
reader = command.ExecuteReader() --> this is very slow in the program, but
very fast in SQL Server Management Studio or SQL Query Analyzer window

These are the table and SP:
CREATE TABLE [dbo].[TickData1Min] (
[SequenceNumber] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CommodityCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MonthYear] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Symbol] AS (rtrim([CommodityCode]) + [MonthYear]) ,
[OpenPrice] [decimal](16, 4) NULL ,
[HighPrice] [decimal](16, 4) NULL ,
[LowPrice] [decimal](16, 4) NULL ,
[ClosePrice] [decimal](16, 4) NULL ,
[Volume] [numeric](18, 0) NULL ,
[Date] [datetime] NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_TickData1Min] ON
[dbo].[TickData1Min]([SequenceNumber]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TickData1Min] ADD
CONSTRAINT [DF_TickData1Min_Volume] DEFAULT (0) FOR [Volume]
GO



CREATE Procedure [dbo].[GetData1Min]
(
@Symbol VarChar(10),
@SeqNumLow VarChar(13)
) with recompile
as Begin

Select sequencenumber, openprice,highprice,lowprice,closeprice,volume
From TickData1Min
Where [Symbol] = @Symbol
AND SequenceNumber >= @SeqNumLow
Order By SequenceNumber

End
GO

 

[Cor Ligthert[MVP]]

The method to execute a non query is the executenonquery, not a datareader,
I see you never use that executenonquery, the datareader is meant in
combination with a Select SP

 

[fniles]

Thank you.

The query is faster after I added "SET ARITHABORT ON" like below:
Using Command = New SqlCommand("SET ARITHABORT ON", connection)
Command.CommandType = CommandType.Text
Command.ExecuteNonQuery()
End Using

This only happened after I add a column to a clustered index in the table.
Before I did that, the query runs fast without having to "SET ARITHABORT ON"
Do you know why I need to "SET ARITHABORT ON" after I re-index the table ?

 

[Erland Sommarskog]

The query is faster after I added "SET ARITHABORT ON" like below:
Using Command = New SqlCommand("SET ARITHABORT ON", connection)
Command.CommandType = CommandType.Text
Command.ExecuteNonQuery()
End Using

This only happened after I add a column to a clustered index in the
table. Before I did that, the query runs fast without having to "SET
ARITHABORT ON" Do you know why I need to "SET ARITHABORT ON" after I
re-index the table ?

Apparently that is a computed column. In SQL 2000, ARITHABORT has to be ON,
for indexes on computed columns to be used. This condition has been lifted
in SQL 2005.


--
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

 

[fniles]

Aha, you got it.
I added column Symbol to the index, and column Symbol is a computed column
with the following specification: (rtrim([CommodityCode]) + [MonthYear])

So, if the database is in SQL 2005, I will not need to "SET ARITHABORT ON"
?
I probably should move the database to SQL 2005 if that's the case, because
otherwise I will have to change a few programs.