Fast SQL Server Stored Procedure runs slow in VB.NET
in [Programming SQL]
|
Prev: running total column help
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 > |