Prev: search for a string or part of a string in another string
Next: set ARITHABORT on
From: fniles on 17 Apr 2010 17:55
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


From: Cor Ligthert[MVP] on 18 Apr 2010 06:41
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" <fniles(a)pfmail.com> wrote in message
news:uWhBjin3KHA.4752(a)TK2MSFTNGP02.phx.gbl...
> 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
>
>
>
From: Plamen Ratchev on 18 Apr 2010 15:22
ARITHABORT is one of the options that affects plan reuse, see more here:
http://technet.microsoft.com/en-us/library/cc966425.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 18 Apr 2010 18:09
fniles (fniles(a)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, esquel(a)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

From: fniles on 18 Apr 2010 22:32
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" <esquel(a)sommarskog.se> wrote in message
news:Xns9D5F1923C770Yazorman(a)127.0.0.1...
> fniles (fniles(a)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, esquel(a)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
>


 |  Next  |  Last
Pages: 1 2
Prev: search for a string or part of a string in another string
Next: set ARITHABORT on