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 tha
n
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 th
an
>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 i
s
>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 & #91;DF_E_Initialization_Total_Moles_Valu
e] DEFAULT (0) FOR [
Value],
CONSTRAINT & #91;DF_E_Initialization_Total_Moles_Mode
] DEFAULT (1) FOR [M
ode]
GO
CREATE INDEX [IX_E_Initialization_Total_Moles] ON
[dbo].[t_Init]([ProjectID], [SetID], [Element], [Poi
nt], [StoreTable],
[VariableID], [Mode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] ADD
CONSTRAINT [DF_E_Solver_Options_Values_ValueID] DEFAULT (0) FOR [Val
ueID],
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|||On Thu, 4 Jan 2007 06:42:01 -0800, Anahi wrote:

>Thanks for your reply.
>I'm sending two options (it is simpler situation than mine, but it is
>possible to see the behaviour):
(snip)
Hi Anahi,
Based on the code you posted, I would be VERY surprised if the cursor
based solution would outperform the setbased version. However, I noticed
that there were some differences between the two, so I'm not even sure
if they both produce the same results (and I can't spend any more time
on it at the moment). You'll have to fix that before any performance
comparison starts to make sense.

>Also, I've been testing it using Tables Variables and Views, but neither
>were better than CURSORS.
If you were using table variables to mimic a cursor (i.e. insert the
data into a table variable, then process row by row), then this is not
surprising. What makes cursor performance slow is not the cursor by
itself, but the fact that you process a single row at a time on a
database that is optimized towards handling all rows at once.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis