SQL Server Performance Forum – Threads Archive

Set logical store procedure

I wanna know if somebody have experience working with T-SQL store procedure, transforming a store procedure builded with procedure logical to a store procedure set logical.
I need make a code like this.
Thank’s

What you mean by logical, do you have any example in your cae? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Transform a cursor to a temporal table for example, an then both behaviors must be equal. It’s just a example, have a lot code SQL with this kind of problem.
I want to take of a behavior T-SQL to pl-SQL, in other words.
Can you help me ?

How should we say if we can help, when we don’t see your code? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] But you are not talking about Oracle’s PL/SQL, right? – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Cursors & Temp table method are different to each other, both have pros & cons.
http://www.eggheadcafe.com/articles/20010823.asp My experience is to work with temp. tables & joins clauses (mostly) by default. I tend to incline towards cursors to solve problems not solvable by other means. That said, I don’t seem to have encountered terribly poor performance using cursors. When you say ‘better’ you haven’t really identified your criteria, whether it’s performance or ease of coding. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I have a store procedure with a cursor like this: DECLARE cur_acciones CURSOR FOR
SELECT
PER_ID,
… i can change it this way: CREATE TABLE #CUR_ACCIONES
(
ID1INTPRIMARY KEYIDENTITY(1,1),
PER_IDINT,
… and then execute a INSERT instruction: INSERT #CUR_ACCIONES
SELECT
PER.per_id,
… It’s all. Then, i can use this temporal table like i did use the cursor.
Both of them have the same behavor, but the second structure is more efficient. I have other structure more complex with the same problem.
It was inderstood ?, i need this kind of solutions. Anybody of yours can help me converting store procedure with procedural logical store procedure to set logical store procedure?

Are you looking for performance or ease of porting data ? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I’m looking for perfomance, it’s more important, but i don’t wanna loose logical behaivor.

Have you looked at the execution plan of the queries you are comparing? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Yes, i don’t have any doubt about it. I did prove this in a lot of situations.
This kind of work really improve query.
Can you help with this kind of work ?

Post the procedure/tsql code to get the correct recommendation… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

One example of cursor abuse is when people start writing T-SQL as if it was a programming language like VB, usually lacking knowing of basic query syntax like JOIN: DECLARE @i INT, @var VARCHAR(10), @lookup @VARCHAR(100) DECLARE abc CURSOR
FOR
SELECT int_col, var_col FROM source_table
OPEN abc
FETCH NEXT FROM abc INTO @i, @var WHILE @@FETCH_STATUS = 0
BEGIN SELECT @lookup = lookup_col FROM other_table WHERE key_col = @var INSERT INTO target_table (int_col, other_col)
VALUES (@i, @lookup) FETCH NEXT FROM abc INTO @i, @var END CLOSE abc
DEALLOCATE abc ***************************** The set-based version of that is: INSERT INTO target_table (int_col, other_col)
SELECT s.int_col, o.lookup_col
FROM source_table AS s
INNER JOIN other_table AS o
ON s.var_col = o.key_col Not only is the code more compact, there will be just one insert action, instead of one for each row in the source table. If you get paid for each line of code that you write, re-negotiate so that they pay you extra for code that performs well.

oK, this is what i do, when i got a code with procedural logical or cursor-base code: ——————————————————————–
CREATE TABLE #ABC
(
int_colintPRIMARY KEY,
var_col varchar(10),
lookup varchar(100) )
CREATE INDEX IDX_ABC_VAR_COL ON #ABC(var_col)
——————————————————————-
INSERT #ABC
SELECT int_col, var_col,” AS ‘lookup’ FROM source_table
——————————————————————-
UPDATE #ABC
SET lookup = B.lookup_col
FROM #ABC A, other_table B
WHERE
A.var_col=B.key_col
——————————————————————-
INSERT target_table (int_col, other_col)
SELECT int_col, lookup FROM #ABC
——————————————————————-
DROP TABLE #ABC
——————————————————————- Perhaps, this kind of programming code working better with a more complex code, but you now what i mean. Maybe, we can see this example in another point of view like this:
INSERT target_table (int_col, other_col)
SELECT A.int_col, B.lookup
FROM source_table A, other_table B
WHERE
A.var_col=B.key_col But, Adriaan really understood what i was talking about.
And now you have a code set-based logical (i hope no mistake).
So, you can improve it now with Query analizer.
Are you Adriaan working with this kind of problem ? I have a lot of code about this kind of cursor-based code. Thank’s all of yours!

Correct, you do not have to use a temp table. Your final statement is fine, although you are strongly encouraged to use JOIN syntax instead of faking it in a WHERE clause. Use the WHERE clause for filtering. This will keep things neatly organized, and easier to understand for any DBA who ever takes a look at those procedures. I think it was your phrase "logical procedure" that got people confused.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>