Cursors vs Table Variables vs Views

Hi people,
I'm checking about which option is the most efficient: Cursors, Table
Variables or Views.
The theory says that cursors are not so good to use, if it is possible to
avoid them, just do it. But, in my case I have better results using them than
using Table Variables or Views.
The case is I need to insert in a table some rows which are not there yet
(so I use the NOT EXISTS clause). If I make it by cursors the speed as well
as the cost are better. But the other two options are slower (using Views is
faster than Table Variables).
In conclusion, I'm confused: is it good to use cursors or not? why is this
option better than the others?
Thanks,
Without some examples of what exactly you are doing I don't know that
anyone will be able to respond constructively. It is true that
cursors are generally frowned upon for performance reasons, but there
is no automatic way the they substitute for views or table variables.
Perhaps if you post DDL, sample data and SQL commands someone will be
able to comment.
Roy Harvey
Beacon Falls, CT
On Wed, 3 Jan 2007 10:10:01 -0800, Anahi
<Anahi@.discussions.microsoft.com> wrote:

>Hi people,
>I'm checking about which option is the most efficient: Cursors, Table
>Variables or Views.
>The theory says that cursors are not so good to use, if it is possible to
>avoid them, just do it. But, in my case I have better results using them than
>using Table Variables or Views.
>The case is I need to insert in a table some rows which are not there yet
>(so I use the NOT EXISTS clause). If I make it by cursors the speed as well
>as the cost are better. But the other two options are slower (using Views is
>faster than Table Variables).
>In conclusion, I'm confused: is it good to use cursors or not? why is this
>option better than the others?
>Thanks,
|||Thanks for your reply.
I'm sending two options (it is simpler situation than mine, but it is
possible to see the behaviour):
Option1: using CURSORS
DECLARE @.ProjectID int, @.SetID INT, @.VarID INT, @.Table int, @.Mode INT,
@.PhaseID int, @.ValueOpt int, @.Element int, @.ValueEst int
SELECT @.ValueEst = v.ValueID FROM t_Options v
WHERE v.ProjectID = @.ProjectID AND v.OptionID = 7 AND v.Mode = 1
SELECT @.ValueOpt = v.ValueID FROM t_Options v
WHERE v.ProjectID = @.ProjectID AND v.OptionID = 7 AND v.Mode = 3
DECLARE set_cursor CURSOR LOCAL READ_ONLY FOR
SELECT setID, Element, Mode FROM t_Values WHERE ProjectID = @.ProjectID
OPEN set_cursor
FETCH NEXT FROM set_cursor INTO @.SetID, @.Element, @.Mode
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE var_cursor CURSOR LOCAL FOR
SELECT c.VariableID, 2
FROM t_Variables2 c
WHERE c.ProjectID = @.ProjectID
OPEN var_cursor
FETCH NEXT FROM var_cursor INTO @.VarID, @.Table
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO t_Init (ProjectID, SetID, Element, Point, VariableID,
StoreTable, Mode)
SELECT @.ProjectID, @.SetID, @.Element, P.P, @.VarID, @.Table, @.Mode
FROM (SELECT 1 AS P
UNION ALL
SELECT 2
UNION ALL
SELECT 0
UNION ALL SELECT
3) P
WHERE (P.P <> 3 or (p.p = 3 AND ((@.Mode = 1 AND @.ValueEst = 18) or (@.Mode
= 3 and @.ValueOpt = 18))))
AND not exists (SELECT 1
FROM t_Init
WHERE ProjectID = @.ProjectID AND Mode = @.Mode
AND SetID= @.SetID AND Element = @.Element
AND Point = p.p AND VariableID = @.VarID
AND StoreTable = @.Table)
FETCH NEXT FROM var_cursor INTO @.VarID, @.Table
END
CLOSE var_cursor
DEALLOCATE var_cursor
FETCH NEXT FROM set_cursor INTO @.SetID, @.Element, @.Mode
END
CLOSE set_cursor
DEALLOCATE set_cursor
go
----
Option2: without CURSORS
DECLARE @.ProjectID int
INSERT INTO t_Init (ProjectID, SetID, Element, Point, VariableID,
StoreTable, Mode)
SELECT d.ProjectID, d.SetID, d.Element, P.P, c.VarID, c.[Table], d.Mode
FROM t_Values d
INNER JOIN t_Options v ON v.ProjectID = d.ProjectID AND v.Mode = d.mode AND
v.OptionID = 7
INNER JOIN (SELECT 0 AS P UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) P
ON (p.p <> 3 OR (p.p = 3 AND v.ValueID = 18))
INNER JOIN (SELECT c.VariableID AS VarID, 2 as [Table], 0 as Mode
FROM t_Variables2 c
WHERE c.ProjectID = @.ProjectID) c ON (c.[Table] = 2 OR (c.[Table] <> 2
AND c.Mode = d.Mode))
WHERE d.ProjectID = @.ProjectID
AND NOT EXISTS (SELECT ProjectID FROM t_Init WHERE ProjectID = d.ProjectID
AND SetID = d.SetID AND Element = d.Element AND Point = p.p AND VariableID =
c.VarID AND StoreTable = c.[Table] AND Mode = d.Mode)
----
It is needed you know that the variables, where I'm using t_Variables2,
could be extracted from 4 different tables: t_Variables1, t_Variables2,
t_Variables4 and t_Variables5. But, in order to simplify the situation, I
only used t_Variables2.
Also, I've been testing it using Tables Variables and Views, but neither
were better than CURSORS.
Thanks very much,
Anahi
----
The structure of my database is (it is a part):
CREATE TABLE [dbo].[t_Init] (
[ProjectID] [int] NOT NULL ,
[SetID] [int] NOT NULL ,
[Element] [int] NOT NULL ,
[Point] [int] NOT NULL ,
[StoreTable] [int] NOT NULL ,
[VariableID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[Mode] [int] NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Options] (
[ProjectID] [int] NOT NULL ,
[OptionID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
[Mode] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Projects] (
[ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
[Project] [nvarchar] (100) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Variables2] (
[ProjectID] [int] NOT NULL ,
[VariableID] [int] NOT NULL ,
[Variable] [nvarchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Abbreviation] [nvarchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Values] (
[ProjectID] [int] NOT NULL ,
[SetID] [int] NOT NULL ,
[Element] [int] NOT NULL ,
[Size] [float] NOT NULL ,
[Mode] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Init] WITH NOCHECK ADD
CONSTRAINT [PK_E_Initialization_Total_Moles] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] WITH NOCHECK ADD
CONSTRAINT [PK_E_Solver_Options_Values] PRIMARY KEY CLUSTERED
(
[ProjectID],
[OptionID],
[Mode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Variables2] WITH NOCHECK ADD
CONSTRAINT [PK_C_Compounds] PRIMARY KEY CLUSTERED
(
[ProjectID],
[VariableID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Values] WITH NOCHECK ADD
CONSTRAINT [PK_E_Discretization_Values] PRIMARY KEY CLUSTERED
(
[ProjectID],
[SetID],
[Element],
[Mode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Init] ADD
CONSTRAINT [DF_E_Initialization_Total_Moles_Value] DEFAULT (0) FOR [Value],
CONSTRAINT [DF_E_Initialization_Total_Moles_Mode] DEFAULT (1) FOR [Mode]
GO
CREATE INDEX [IX_E_Initialization_Total_Moles] ON
[dbo].[t_Init]([ProjectID], [SetID], [Element], [Point], [StoreTable],
[VariableID], [Mode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] ADD
CONSTRAINT [DF_E_Solver_Options_Values_ValueID] DEFAULT (0) FOR [ValueID],
CONSTRAINT [DF_E_Solver_Options_Values_Mode] DEFAULT (0) FOR [Mode]
GO
ALTER TABLE [dbo].[t_Values] ADD
CONSTRAINT [DF_E_Discretization_Values_Mode] DEFAULT (0) FOR [Mode]
GO