September 16, 2013 at 1:14 pm
Hi guys,
Can i load a cursor from a procedure?
Like
DECLARE cursor_importedPatients CURSOR FOR
EXEC procedure
Thanks!
September 16, 2013 at 1:20 pm
fernando.desena (9/16/2013)
Hi guys,Can i load a cursor from a procedure?
Like
DECLARE cursor_importedPatients CURSOR FOR
EXEC procedure
Thanks!
No that won't work quite like that. You could create a temp table first and insert into the temp table and then define your cursor to use the temp table. Why do you need a cursor? They are generally very slow and should be used for a few administrative tasks. With more details I bet we could find a way to eliminate the cursor entirely. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2013 at 1:40 pm
Emmm its like this:
Some process imports the information from a file into a DB table. Here is the thing, i have to take, one by one, the rows of this table and persist them into other 4 tables.
For example:
A process imports clients from a file into a "importedClients" table. Later, you see the importation details (like "50 new clientes, 2 duplicated, etc.") and want, efectively, to do the importation. So, you will execute a procedure which opens a cursor for the records of the "importedClients" table and insert them in "clientContact", "clients", "clientsSocialWork", "etc".
The process will import 200.000 registers :S
And i made that question because i have to import only the records that doesnt exist in other table. Yes, i can make that in a SELECT but i already have other SP for that thing.
September 16, 2013 at 2:29 pm
FDS_ (9/16/2013)
Emmm its like this:Some process imports the information from a file into a DB table. Here is the thing, i have to take, one by one, the rows of this table and persist them into other 4 tables.
For example:
A process imports clients from a file into a "importedClients" table. Later, you see the importation details (like "50 new clientes, 2 duplicated, etc.") and want, efectively, to do the importation. So, you will execute a procedure which opens a cursor for the records of the "importedClients" table and insert them in "clientContact", "clients", "clientsSocialWork", "etc".
The process will import 200.000 registers :S
And i made that question because i have to import only the records that doesnt exist in other table. Yes, i can make that in a SELECT but i already have other SP for that thing.
A cursor over 200,000 rows with a stored proc call for each line is going to be a performance issue for certain.
As I said previously if you want to use a cursor for the output of a stored proc you will have first create the temp table and then insert into it with your proc.
Create table #MyTable
(
Column definition to match the output of your proc.
)
insert #MyTable
exec MyProc
**cough**
cursor goes here
**cough**
i have to take, one by one, the rows of this table and persist them into other 4 tables.
I am 99.99999% certain this can be done without RBAR processing. It would take your 200,000 row insert from 30 minutes to a few seconds.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2013 at 2:38 pm
what does "RBAR processing" means? sry about my ignorance...
September 16, 2013 at 2:40 pm
RBAR = "Row-By-Agonizing-Row".
It's something we try to avoid in databases. Rather we try to do all of the rows at once.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2013 at 2:40 pm
FDS_ (9/16/2013)
what does "RBAR processing" means? sry about my ignorance...
Sorry. That is an abbreviation around here (coined by Jeff Moden) that means Row By Agonizing Row. It is used to defined looping which is just horribly inefficient in sql server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2013 at 2:35 am
FDS_ (9/16/2013)
Hi guys,Can i load a cursor from a procedure?
Like
DECLARE cursor_importedPatients CURSOR FOR
EXEC procedure
Thanks!
Something like this can be done, but as other mentioned, it's usually not a good idea!
IF OBJECT_ID('SP_TEST') IS NOT NULL
DROP PROCEDURE SP_TEST
GO
CREATE PROCEDURE SP_TEST
@pParam_1 INT
,@pCursor CURSOR VARYING OUTPUT
AS
SET@pCursor = CURSOR READ_ONLY FORWARD_ONLY FOR
SELECTc.cust_no
FROMcustomer c
WHEREcust_id = @pParam_1
OPEN @pCursor
GO
DECLARE @cr_X CURSOR
,@cust_no VARCHAR(30)
EXECSP_TEST 2, @pCursor = @cr_x OUTPUT
FETCH NEXT FROM @cr_x INTO @cust_no
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @cust_no
FETCH NEXT FROM @cr_x INTO @cust_no
END
CLOSE @CR_X
DEALLOCATE @CR_X
GO
September 17, 2013 at 9:19 am
Thanks all! i did it that way and its working ok.
Now i will take your advise and i will try to do a masive inserts. Like:
INSERT dbo.Clients
SELECT id, name
FROM dbo.ClientsToImport.
I think it will be faster than my actual way. I will let you know about this !!
Again,ty all.
September 17, 2013 at 4:34 pm
FDS_ (9/17/2013)
Thanks all! i did it that way and its working ok.Now i will take your advise and i will try to do a masive inserts. Like:
INSERT dbo.Clients
SELECT id, name
FROM dbo.ClientsToImport.
I think it will be faster than my actual way. I will let you know about this !!
Again,ty all.
"Massive Inserts"... you need to be aware that each system has a tipping point and that each "massive insert" can also blow the log file out to incredible sizes. With that thought in mind, be a bit careful as to how big your "massive inserts" are. Sometimes it's a very good thing to split such inserts up into smaller batches. It's still "set based"... just smaller, iterative sets.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2013 at 9:44 am
Well i did it and its working fine! Thanks all for the answers.
September 25, 2013 at 9:52 am
FDS_ (9/25/2013)
Well i did it and its working fine! Thanks all for the answers.
It would be great if you could post what you did. It may very well help someone else out that comes along later and views this thread.
Glad you were able to solve your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply