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

[Plamen Ratchev]

This is most likely due to different execution plans. The plan produces for the VB execution is probably not efficient
for the set of parameters provided. Read here more about parameter sniffing:
http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

Since you are on SQL Server 2000, one possible solution can be to assign the stored procedure parameters to local
variables and use the local variables in the query. That in effect will turn off parameter sniffing and use statistics
to generate query plan.

--
Plamen Ratchev
http://www.SQLStudio.com

[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" <fni...@pfmail.com> wrote in message
news:uWhBjin3...@TK2MSFTNGP02.phx.gbl...

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

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:A5CdndrRNaOR-1fW...@speakeasy.net...

[Plamen Ratchev]

ARITHABORT is one of the options that affects plan reuse, see more here:
http://technet.microsoft.com/en-us/library/cc966425.aspx

[Erland Sommarskog]

fniles (fni...@pfmail.com) writes:
> 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, esq...@sommarskog.se

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.

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D5F1923...@127.0.0.1...

[Erland Sommarskog]

fniles (fni...@pfmail.com) writes:
> 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"
> ?

Correct.

(There still some situations in SQL 2005 where you need to have ARITHABORT
ON, to wit when you work with the xml data type. But for indexed views and
indexed computed columns ARITHABORT does not matter.)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx