Best practice...

Hi,
I was an oracle programmer recently moved to MSSql. So I'm trying to learn
the right way. Oracle was very cursor oriented & MSSQl seems to seriously
discourage using them at all wherever possible.
In this situation here's what I roughly written, with cursors - it'll give
you the direction I am going at least. I want to re-write properly without
cursors.
Declare cur_DeleteChild Cursor Scroll For
Select TableID
from tbl_TableMaster
Where ParentID in
(Select TableID from tbl_TableMaster where
supplierID = @.v_FromSupplierID)
Open cur_DeleteChild
Fetch First FROM cur_DeleteChild into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_DeleteChild @.ChildTableID
END
Fetch Next FROM cur_DeleteChild into @.ChildTableID
Close cur_DeleteChild
Deallocate cur_DeleteChild
These are not large tables, nor will this sp be run often.
Rather than the subquery, I've read I should use temp table if necessary,
but in most cases I can avoid using them at all.
(I know temp tables do not perform the same purpose as cursors)
Thanks for any suggestions.I always use cursors and have not been sacked yet
depends on what you want to have happen in the cursor and the volumes of
data, they can be just as useful as in oracle.
Having said that I also use temp tables too.
"Lesley" wrote:

> Hi,
> I was an oracle programmer recently moved to MSSql. So I'm trying to lear
n
> the right way. Oracle was very cursor oriented & MSSQl seems to seriously
> discourage using them at all wherever possible.
> In this situation here's what I roughly written, with cursors - it'll give
> you the direction I am going at least. I want to re-write properly witho
ut
> cursors.
> Declare cur_DeleteChild Cursor Scroll For
> Select TableID
> from tbl_TableMaster
> Where ParentID in
> (Select TableID from tbl_TableMaster where
> supplierID = @.v_FromSupplierID)
> Open cur_DeleteChild
> Fetch First FROM cur_DeleteChild into @.ChildTableID
> While (@.@.Fetch_Status <> -1)
> Begin
> exec sp_DeleteChild @.ChildTableID
> END
> Fetch Next FROM cur_DeleteChild into @.ChildTableID
> Close cur_DeleteChild
> Deallocate cur_DeleteChild
> These are not large tables, nor will this sp be run often.
> Rather than the subquery, I've read I should use temp table if necessary,
> but in most cases I can avoid using them at all.
> (I know temp tables do not perform the same purpose as cursors)
> Thanks for any suggestions.|||Looks like a cascaded DELETE to me. Have you considered using the ON DELETE
CASCADE option? Otherwise you can use a DELETE trigger.
If your table has a self-referencing foreign key and represents a hierarchy
of some unknown depth then you'll possibly want to use a Recursive trigger.
However, there are other data models for this kind of hierarchy that may
serve you better. Google for "Materialized Path" or "Nested Sets" if you
aren't familiar with them.
David Portas
SQL Server MVP
--
"Lesley" wrote:

> Hi,
> I was an oracle programmer recently moved to MSSql. So I'm trying to lear
n
> the right way. Oracle was very cursor oriented & MSSQl seems to seriously
> discourage using them at all wherever possible.
> In this situation here's what I roughly written, with cursors - it'll give
> you the direction I am going at least. I want to re-write properly witho
ut
> cursors.
> Declare cur_DeleteChild Cursor Scroll For
> Select TableID
> from tbl_TableMaster
> Where ParentID in
> (Select TableID from tbl_TableMaster where
> supplierID = @.v_FromSupplierID)
> Open cur_DeleteChild
> Fetch First FROM cur_DeleteChild into @.ChildTableID
> While (@.@.Fetch_Status <> -1)
> Begin
> exec sp_DeleteChild @.ChildTableID
> END
> Fetch Next FROM cur_DeleteChild into @.ChildTableID
> Close cur_DeleteChild
> Deallocate cur_DeleteChild
> These are not large tables, nor will this sp be run often.
> Rather than the subquery, I've read I should use temp table if necessary,
> but in most cases I can avoid using them at all.
> (I know temp tables do not perform the same purpose as cursors)
> Thanks for any suggestions.|||> I always use cursors
Probably that explains why you can't cope with a 25 million row table. Set
based code is smarter, more scalable and easier to maintain than cursors.
Cursors are useful - if you don't know SQL.
David Portas
SQL Server MVP
--
"marcmc" wrote:
> I always use cursors and have not been sacked yet
> depends on what you want to have happen in the cursor and the volumes of
> data, they can be just as useful as in oracle.
> Having said that I also use temp tables too.
> "Lesley" wrote:
>|||Thanks David,
The wording used to create these tables has made it confusing.
Each row in the tbl_TableMaster contains the identity of a logical or
physical table - some self referencing, yes. The logical tables are childre
n
to the physical tables. The logical tables are the one's I'm deleting. The
rows that make up the logical child tables are held within a separate
phsyical table. That physical table will, in most situations, never be
deleted. I'm deleting the rows from the physical table that contains the
child rows & the row from tbl_TableMaster that identifies the logical child
table. That may have made it more confusing, but if you think there is
anything else that may apply to that situation please advise.
I will look into the recursive trigger, I have used a recursive call for
something similar and it worked well. I think that may serve my purpose.
Though I used a physical table to store my data - can I use a temp table whe
n
making a call on itself? I will also look into "Materialized Path" & "Neste
d
Sets" as I'm not familiar with them & they may serve my purpose.
Thanks again.
"David Portas" wrote:
> Looks like a cascaded DELETE to me. Have you considered using the ON DELET
E
> CASCADE option? Otherwise you can use a DELETE trigger.
> If your table has a self-referencing foreign key and represents a hierarch
y
> of some unknown depth then you'll possibly want to use a Recursive trigger
.
> However, there are other data models for this kind of hierarchy that may
> serve you better. Google for "Materialized Path" or "Nested Sets" if you
> aren't familiar with them.
> --
> David Portas
> SQL Server MVP
> --
>
> "Lesley" wrote:
>|||I'm not certain what you mean by "logical tables" in this context but it
sounds very much like a better design would solve your problems. You seem to
be confusing data with metadata.
David Portas
SQL Server MVP
--
"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F0ADAD7B-7D6D-44FA-B76F-665317A51BAB@.microsoft.com...
> Thanks David,
> The wording used to create these tables has made it confusing.
> Each row in the tbl_TableMaster contains the identity of a logical or
> physical table - some self referencing, yes. The logical tables are
> children
> to the physical tables. The logical tables are the one's I'm deleting.
> The
> rows that make up the logical child tables are held within a separate
> phsyical table. That physical table will, in most situations, never be
> deleted. I'm deleting the rows from the physical table that contains the
> child rows & the row from tbl_TableMaster that identifies the logical
> child
> table. That may have made it more confusing, but if you think there is
> anything else that may apply to that situation please advise.
> I will look into the recursive trigger, I have used a recursive call for
> something similar and it worked well. I think that may serve my purpose.
> Though I used a physical table to store my data - can I use a temp table
> when
> making a call on itself? I will also look into "Materialized Path" &
> "Nested
> Sets" as I'm not familiar with them & they may serve my purpose.
> Thanks again.
>
> "David Portas" wrote:
>|||Thanks,
I can't change the design at this point. I have to work with what has
already been developed & in production for a while.
By logical tables - it's just a bunch of rows of data held in one table that
have something in common - indicated by one row in the master table. You're
right, logical table isn't the right term - that's what everyone calls them
here in that particular situation. Metadata would be a better way to
describe it I think.
I think I can work without the cursors from here on in.
Thanks for your help.
Lesley.
"David Portas" wrote:

> I'm not certain what you mean by "logical tables" in this context but it
> sounds very much like a better design would solve your problems. You seem
to
> be confusing data with metadata.
> --
> David Portas
> SQL Server MVP
> --
> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F0ADAD7B-7D6D-44FA-B76F-665317A51BAB@.microsoft.com...
>
>|||Try something like this...
DECLARE @.ChildTableID INT
SELECT @.ChildTableID = 0x80000000
WHILE (1=1)
BEGIN
SELECT @.ChildTableID = MIN( TableID ) FROM tbl_TableMaster WHERE TableID >
@.ChildTableID
AND ParentID IN( SELECT TableID FROM tbl_TableMaster WHERE supplierID =
@.v_FromSupplierID )
IF @.ChildTableID IS NULL BREAK
EXEC sp_DeleteChild @.ChildTableID
END
It should run faster than the cursor and use less resources.
It also assumes that you never use "-2147483648" as a TableID.
"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:B8354BE6-3FDD-4917-BC11-A54302FBCDEB@.microsoft.com...
> Hi,
> I was an oracle programmer recently moved to MSSql. So I'm trying to
learn
> the right way. Oracle was very cursor oriented & MSSQl seems to seriously
> discourage using them at all wherever possible.
> In this situation here's what I roughly written, with cursors - it'll give
> you the direction I am going at least. I want to re-write properly
without
> cursors.
> Declare cur_DeleteChild Cursor Scroll For
> Select TableID
> from tbl_TableMaster
> Where ParentID in
> (Select TableID from tbl_TableMaster where
> supplierID = @.v_FromSupplierID)
> Open cur_DeleteChild
> Fetch First FROM cur_DeleteChild into @.ChildTableID
> While (@.@.Fetch_Status <> -1)
> Begin
> exec sp_DeleteChild @.ChildTableID
> END
> Fetch Next FROM cur_DeleteChild into @.ChildTableID
> Close cur_DeleteChild
> Deallocate cur_DeleteChild
> These are not large tables, nor will this sp be run often.
> Rather than the subquery, I've read I should use temp table if necessary,
> but in most cases I can avoid using them at all.
> (I know temp tables do not perform the same purpose as cursors)
> Thanks for any suggestions.|||Thank you very much. I've just returned to this project after a few ws &
this code will do the trick.
Thanks again.
"Rebecca York" wrote:

> Try something like this...
> DECLARE @.ChildTableID INT
> SELECT @.ChildTableID = 0x80000000
> WHILE (1=1)
> BEGIN
> SELECT @.ChildTableID = MIN( TableID ) FROM tbl_TableMaster WHERE TableID
>
> @.ChildTableID
> AND ParentID IN( SELECT TableID FROM tbl_TableMaster WHERE supplierID =
> @.v_FromSupplierID )
> IF @.ChildTableID IS NULL BREAK
> EXEC sp_DeleteChild @.ChildTableID
> END
> It should run faster than the cursor and use less resources.
> It also assumes that you never use "-2147483648" as a TableID.
>
> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:B8354BE6-3FDD-4917-BC11-A54302FBCDEB@.microsoft.com...
> learn
> without
>
>

Best Practice Analyzer doesnt like my cursor

I'm running the Best Practice Analyzer on my sql 2k server and one of many
issues is about a bunch of cursors. The issue is for this cursor in an SP.
Actually there are the same 2 issues listed for a lot of SPs they are:
This cursor is declared as updatable (either explicitly or implicitly),
however it doesn't seem to be updated.
and
This cursor does not specify explicit updatability information (FOR UPDATE
clause or READ_ONLY clause).
However, I declared the cursor as read only so I don't understand why I
still get this Non-Compliance issue. Here's the cursor:
DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
SELECT VAL_ID
FROM #Tmp
OPEN curs_VAPairedComp_Insert
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
@.VAL_ID,
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR 44444 'Could not update or insert.'
ROLLBACK TRANSACTION
RETURN
END
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
END
CLOSE curs_VAPairedComp_Insert
DEALLOCATE curs_VAPairedComp_Insert
Thanks!
moondaddy@.nospam.nospamI don't like your cursor, either - no need for it :)
You should lose the cursors altogether instead of fixing them to BPA
compliance.
What possible errors could happen? FK errors? Don't know where the
transaction is, but since you're rolling back all the inserts, this
should work just fine in a set-based, not row-based, fashion.
assuming that: @.VP_ID and @.UserID are already determined elsewhere in
the code, and that the transaction has already begun. if the transaction
only includes this insert, then the rollback is unnecessary, as the
whole insert will fail.
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
VAL_ID
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
from
#Tmp
IF @.@.ERROR<>0
BEGIN
RAISERROR...
ROLLBACK TRANSACTION
END
moondaddy wrote:
> I'm running the Best Practice Analyzer on my sql 2k server and one of many
> issues is about a bunch of cursors. The issue is for this cursor in an SP
.
> Actually there are the same 2 issues listed for a lot of SPs they are:
> This cursor is declared as updatable (either explicitly or implicitly),
> however it doesn't seem to be updated.
> and
> This cursor does not specify explicit updatability information (FOR UPDATE
> clause or READ_ONLY clause).
> However, I declared the cursor as read only so I don't understand why I
> still get this Non-Compliance issue. Here's the cursor:
>
> DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
> SELECT VAL_ID
> FROM #Tmp
> OPEN curs_VAPairedComp_Insert
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> @.VAL_ID,
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR 44444 'Could not update or insert.'
> ROLLBACK TRANSACTION
> RETURN
> END
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> END
> CLOSE curs_VAPairedComp_Insert
> DEALLOCATE curs_VAPairedComp_Insert
>
> Thanks!
>|||OK point made about being set-based, however, it only rolls back if there is
an error. Also, the real issue is WHY is this getting picked up in the
first place by the Analyzer? There are numerous other cursors that are
getting picked up also, but they are different from this one and are needed.
moondaddy@.nospam.nospam
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uxBN7WI7FHA.3276@.TK2MSFTNGP15.phx.gbl...
>I don't like your cursor, either - no need for it :)
> You should lose the cursors altogether instead of fixing them to BPA
> compliance.
> What possible errors could happen? FK errors? Don't know where the
> transaction is, but since you're rolling back all the inserts, this should
> work just fine in a set-based, not row-based, fashion.
> assuming that: @.VP_ID and @.UserID are already determined elsewhere in the
> code, and that the transaction has already begun. if the transaction only
> includes this insert, then the rollback is unnecessary, as the whole
> insert will fail.
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> VAL_ID
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> from
> #Tmp
> IF @.@.ERROR<>0
> BEGIN
> RAISERROR...
> ROLLBACK TRANSACTION
> END
>
> moondaddy wrote:|||it's probably just that there insert/update/delete statement(s) inside
the cursor(s) in question [BPA does say "based on apparent usage"]
moondaddy wrote:
> OK point made about being set-based, however, it only rolls back if there
is
> an error. Also, the real issue is WHY is this getting picked up in the
> first place by the Analyzer? There are numerous other cursors that are
> getting picked up also, but they are different from this one and are neede
d.
>

Best Performance

I wanna know. I have 2 ways to copy data.
way I
INSERT into A
SELECT * from B
Way II
DECLARE CURSOR ... For
SELECT * from B
WHILE ... BEGIN
/* insert one by one to A
END
...
...
Which the best, way I or II? How much its speed comparison?I. is better. How much faster depends on many things; it could
be 2, 10, or 50 times as fast. Experiment and find out.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:

>I wanna know. I have 2 ways to copy data.
>way I
>INSERT into A
>SELECT * from B
>Way II
>DECLARE CURSOR ... For
>SELECT * from B
>WHILE ... BEGIN
> /* insert one by one to A
>END
>...
>...
>Which the best, way I or II? How much its speed comparison?
>
>|||Hi
" I - Way" is best
rely on cursor if u dont find any other way to do things
(but there will be always another way to achieve things easily)
refer:http://www.sql-server-performance.com/cursors.asp
Regards
Magesh
"Bpk. Adi Wira Kusuma" wrote:

> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
>|||Absolutely you should avoid using cursor
So What would I use a Cursor for?
Solution:
In Favour
Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.
The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database
Cursors are mainly used to traverse reach row in the result of a query.
If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL
Most of the time you *can* conditionally perform an operation for each
row using a WHERE clause or using some combination of a WHERE clause
and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.
The other sensible application for a cursor is the - administrative and
management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.
Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually. or Use corelated Subquery
With Warm Regards
Jatinder Singh|||A cursor typically takes twice as long as a set-based select, and that's
only for reading. Inserting within the fetch loop will be several
magnitudes slower, because each separate insert forces all indexes to be
updated. A set-based insert allows SQL Server to optimize all of the index
updates.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#23PK#LkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>

Besides using cursor to iterate one by one, any better solutio

the sencond one: physically, they are different two orders. you may
interprete that a customer reordered exactly what he/she ordered previously.
"Michael C#" wrote:

> Quick question - are these two orders considered the exact same physical
> order, or are they actually 2 different orders that just happen to have th
e
> same values in the various columns? I.e., In the following:
>
> Is order 101 a duplicate of order 95, or is it a completely separate order
> which just happens to have the same information in it? Thanks.
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:BCA584D6-1F6F-4F4C-AB70-26DABD41756E@.microsoft.com...
>
>Check out Celko's post - your main problem is that your data is not
normalized
I'm still not sure exactly where your ItemPropertyID and ItemPriority
columns fall into the grand scheme of things... Or how your ItemID is
supposed to relate to the ItemExt table... One item on one order can easily
relate to 2, 3, or 100 ItemExt records. What are you trying to accomplish
with that?
Thanks
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:2C61D5F8-284E-4F78-AD59-6C3DD89B9D70@.microsoft.com...
> the sencond one: physically, they are different two orders. you may
> interprete that a customer reordered exactly what he/she ordered
> previously.
>