My question is connected with tip "Using Views to Control User Access to
Data":
http://www.microsoft.com/sql/techinfo/tips/administration/controlledaccess.asp
I need to update (using Access 2000/2002) table defended in such way,
but it cause some additional problems.
1. I can't update this view when user haven't SELECT permissions
on the base table (user have only permissions to the view, because I try to
defend base table).
As I understand ADO tries directly update base table and fails.
2. When I add "WITH VIEW_METADATA" to enforce ADO to update
view, not base tables I have another problem - I need to add
prymary key columns from "authtable" to make view updateble
for ADO. In such way I also had to add some additional procedures
to dataform in Access to fill this additional columns automatically (for
insert operation).
It helps but it cause additional procedures at client side.
3. The best way to resolve this problem only on server side as I thought
was to create view like this (using "WITH VIEW_METADATA" and "IN" clauses):
CREATE VIEW v_data
WITH VIEW_METADATA
AS
SELECT <column_list>
FROM dbo.mytable AS a
WHERE a.Pkey
IN
(
SELECT b.DataKey
FROM dbo.authtable AS b
WHERE b.userid = suser_sname()
)
But this view is not updatable in Access 2000/2002
because ADO DOES NOT SEE PRIMARY KEY INFORMATION
when there is combination "WITH VIEW_METADATA" and "IN" clauses.
I cann't use this view without "WITH VIEW_METADATA", in this case
view is "updatable" for ADO, but updates fails as described in p.1.
I send this bug year ago to MS when was mdac 2.7.
Now we have MDAC 2.8, SQL SP 3, Yukon Beta - ADO has the same problem with
updates
when there is combination "WITH VIEW_METADATA" and "IN" clauses
I think, it helps to make simple customised rowbased security system not
only for SELECT
but also for INSERT, UPDATE and DELETE in combination "SQL Server - MS
Access" or other ADO based clients.Hello Max:
You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
ii> 1. I can't update this view when user haven't SELECT permissions
ii> on the base table (user have only permissions to the view, because I
ii> try to defend base table).
ii> As I understand ADO tries directly update base table and fails.
In Access 2002, open the view in design mode, open properties, select
"Update using view rules".
ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com|||"Vadim Rapp" <vrapp@.nospam.polyscience.com> ñîîáùèë/ñîîáùèëà â íîâîñòÿõ
ñëåäóþùåå: news:e1IXvQULEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello Max:
> You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
> ii> 1. I can't update this view when user haven't SELECT permissions
> ii> on the base table (user have only permissions to the view, because I
> ii> try to defend base table).
> ii> As I understand ADO tries directly update base table and fails.
> In Access 2002, open the view in design mode, open properties, select
> "Update using view rules".
> ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
> Vadim
> ---
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>
Hello Vadim,
Thanks for reply,
Chekbox "Update using view rules" exactly adds "WITH VIEW_METADATA" clause
to view definition and this situation adds aditional problems as described
in p.2. But when I want add more sophisticated rules for user selection
permissions and make it updatable in Access I get very processor time
cosuming solutions. Execution plans for view scheme discribed in p.3 for my
tasks and needed rules some times more than 10 time quick than the same that
Access (ADO) understands as updatable - this the main problem...
And root of problem is ADO uncorrect understanding of combination of clauses
"WITH VIEW_METADATA" and "IN" (also "EXISTS").
Ó Ëüâîâ³ êëàñíî, ïðàâäà ùå òðîõè çèìíî áóâàº, ³ ÷àñ â³ä ÷àñó äîùèòü. Â
íåä³ëþ 2 òðàâíÿ áóäåìî ñâÿòêóâàòè äåíü ì³ñòà.
MAX|||Hello Max:
You wrote in conference microsoft.public.sqlserver.server on Thu, 29 Apr
2004 14:05:18 +0300:
II> And root of problem is ADO uncorrect understanding of combination of
II> clauses "WITH VIEW_METADATA" and "IN" (also "EXISTS").
It looks like it's not ADO but Access. I see that Access indeed does not
allow to add records to such a view; however, I successfully executed the
following pure ado code in VB:
rs.CursorLocation = adUseClient
rs.Open "view1", conn, adOpenKeyset, adLockBatchOptimistic
rs.AddNew
rs!c1 = "a"
rs!c2 = "b"
rs!id = 14
rs.UpdateBatch
rs.Close
where view1 was
ALTER VIEW dbo.View1
WITH VIEW_METADATA
AS
SELECT dbo.t1.c1, dbo.t1.c2, dbo.t1.id, dbo.t1.auth
FROM dbo.t1 INNER JOIN
dbo.authtable ON dbo.t1.auth >= dbo.authtable.minvalue
WHERE (dbo.authtable.userid = USER_NAME())
Note that I did not include in the view anything from authtable.
Another interesting possibility is described in BOL in "Create View"
article, which says: "INSTEAD OF triggers can be created on a view in order
to make a view updatable".
As a side note: you might receive more advise if you asked the question in
more relevant newsgroups. This one is mainly read by server administrators;
you might try .access.adp.sqlserver and .data.ado (though, as I said, it
looks like ADO is innocent here).
Êðiì òîãî, êîëè òè ïèøåø â àíãëiéñüêó ãðóïïó, òî ïèøè ñâîº iì'ÿ
ïî-àíãë³éñüêè.
regards,
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com
Showing posts with label views. Show all posts
Showing posts with label views. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
Custom Date Format
I need to change the date format from mmddyyyy to yyyymmdd and I'm not
quite sure how to accomplish it. Obviously there are default date
views, but yyyymmdd is not one of them. I'm guessing I need to create
an expression. Can someone help?
Thanks,
tpTry putting this into a textbox.
= Format(<Database Field>, "yyyyMMdd")
This should help with all formatting inquries:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
Regards,
Dan|||Thanks Dan. I figured it was an easy one...|||I went though the MSDN docs, and put together a small document which include
all of the formatting special characters which can be used...It might be
worth downloading and printing out if you do a lot of reporting services..
It is at www.msbicentral.com. It is in the articles section, titled
'Formatting Expressions in Reporting Services"
Hope this helps.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"newsgroups@.bendcycling.org" wrote:
> Thanks Dan. I figured it was an easy one...
>
quite sure how to accomplish it. Obviously there are default date
views, but yyyymmdd is not one of them. I'm guessing I need to create
an expression. Can someone help?
Thanks,
tpTry putting this into a textbox.
= Format(<Database Field>, "yyyyMMdd")
This should help with all formatting inquries:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
Regards,
Dan|||Thanks Dan. I figured it was an easy one...|||I went though the MSDN docs, and put together a small document which include
all of the formatting special characters which can be used...It might be
worth downloading and printing out if you do a lot of reporting services..
It is at www.msbicentral.com. It is in the articles section, titled
'Formatting Expressions in Reporting Services"
Hope this helps.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"newsgroups@.bendcycling.org" wrote:
> Thanks Dan. I figured it was an easy one...
>
Tuesday, February 14, 2012
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
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
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
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
Subscribe to:
Posts (Atom)