While loop taking so much time in SQL Server
0
In my stored procedure, I have 2 while loops and I need to avoid those loops and use cursor or recursive cte
create procedure Parent_Child_UserDetails_Schedule
as
begin
Set nocount on
create table #temptab(id int, userid int, parentid int)
select userid, 1 as valid
into #users
from userdetails
where isactive = 1
truncate table Parent_Child_UserDetails
while(select count(*) from #users where valid = 1) > 0
begin
declare @userid int
select top 1 @userid = userid
from #users
where valid = 1
truncate table #temptab
insert into #temptab(id, userid, parentid)
values(1, @userid, @userid)
declare @id int
set @id = 1
while((select count(*) from userdetails
where parentid in (select userid from #temptab where id=@id ) and isactive = 1) > 0)
begin
insert into #temptab (id, userid, parentid)
select @id + 1, userid, @userid
from userdetails
where parentid in (select userid from #temptab where id = @id)
and isactive = 1
set @id= @id + 1
end
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from #temptab
update #users
set valid = 0
where userid = @userid
end
drop table #temptab
drop table #users
Set nocount off
end
Please help me .....
sql
sql-server tsql sql-server-2008 edited Nov 14 '18 at 10:10
marc_s
575k12811101257
asked Nov 14 '18 at 10:05
katrajkatraj
1307
|
show 3 more comments
0
In my stored procedure, I have 2 while loops and I need to avoid those loops and use cursor or recursive cte
create procedure Parent_Child_UserDetails_Schedule
as
begin
Set nocount on
create table #temptab(id int, userid int, parentid int)
select userid, 1 as valid
into #users
from userdetails
where isactive = 1
truncate table Parent_Child_UserDetails
while(select count(*) from #users where valid = 1) > 0
begin
declare @userid int
select top 1 @userid = userid
from #users
where valid = 1
truncate table #temptab
insert into #temptab(id, userid, parentid)
values(1, @userid, @userid)
declare @id int
set @id = 1
while((select count(*) from userdetails
where parentid in (select userid from #temptab where id=@id ) and isactive = 1) > 0)
begin
insert into #temptab (id, userid, parentid)
select @id + 1, userid, @userid
from userdetails
where parentid in (select userid from #temptab where id = @id)
and isactive = 1
set @id= @id + 1
end
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from #temptab
update #users
set valid = 0
where userid = @userid
end
drop table #temptab
drop table #users
Set nocount off
end
Please help me .....
sql
sql-server tsql sql-server-2008 edited Nov 14 '18 at 10:10
marc_s
575k12811101257
asked Nov 14 '18 at 10:05
katrajkatraj
1307
1
Post your table structure, existing data example and desired output/how you want the tables to look at the end of the process. Picking apart that code and writing something set based that does the same thing is a big ask when we can't see the data it works on or what it should produce, other than by picking through the code
– Caius Jard
Nov 14 '18 at 10:10
1
Don't use loops in SQL. Use a set based approach instead. To help us help you, please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 14 '18 at 10:14
@ZoharPeled sample data even iam not having the table is having millions of data iam not understanding what they are doing please try to understand
– Chanukya
Nov 14 '18 at 10:33
if you can, try and think of how to make the inner loop into a set based operation. A cursor may not be much faster.
– Cato
Nov 14 '18 at 11:10
1
Using a Cursor or rCTE are probably not going to make things any/much better here. AWHILEis simply a different way of writing a cursor, where as an rCTE can be a form of RBAR as well. (Some likeWITH C AS (SELECT 0 AS I UNION ALL SELECT I + 1 FROM C WHERE I +1 < 50) SELECT I FROM C;is actually an RBAR process, for example). The fact that you are using a hierarchy means that an rCTE is likely to be RBAR as well; but without knowing the data, expected results, etc, that's a guess.
– Larnu
Nov 14 '18 at 11:47
|
show 3 more comments
0
0
0
In my stored procedure, I have 2 while loops and I need to avoid those loops and use cursor or recursive cte
create procedure Parent_Child_UserDetails_Schedule
as
begin
Set nocount on
create table #temptab(id int, userid int, parentid int)
select userid, 1 as valid
into #users
from userdetails
where isactive = 1
truncate table Parent_Child_UserDetails
while(select count(*) from #users where valid = 1) > 0
begin
declare @userid int
select top 1 @userid = userid
from #users
where valid = 1
truncate table #temptab
insert into #temptab(id, userid, parentid)
values(1, @userid, @userid)
declare @id int
set @id = 1
while((select count(*) from userdetails
where parentid in (select userid from #temptab where id=@id ) and isactive = 1) > 0)
begin
insert into #temptab (id, userid, parentid)
select @id + 1, userid, @userid
from userdetails
where parentid in (select userid from #temptab where id = @id)
and isactive = 1
set @id= @id + 1
end
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from #temptab
update #users
set valid = 0
where userid = @userid
end
drop table #temptab
drop table #users
Set nocount off
end
Please help me .....
sql
sql-server tsql sql-server-2008 edited Nov 14 '18 at 10:10
marc_s
575k12811101257
asked Nov 14 '18 at 10:05
katrajkatraj
1307
In my stored procedure, I have 2 while loops and I need to avoid those loops and use cursor or recursive cte
create procedure Parent_Child_UserDetails_Schedule
as
begin
Set nocount on
create table #temptab(id int, userid int, parentid int)
select userid, 1 as valid
into #users
from userdetails
where isactive = 1
truncate table Parent_Child_UserDetails
while(select count(*) from #users where valid = 1) > 0
begin
declare @userid int
select top 1 @userid = userid
from #users
where valid = 1
truncate table #temptab
insert into #temptab(id, userid, parentid)
values(1, @userid, @userid)
declare @id int
set @id = 1
while((select count(*) from userdetails
where parentid in (select userid from #temptab where id=@id ) and isactive = 1) > 0)
begin
insert into #temptab (id, userid, parentid)
select @id + 1, userid, @userid
from userdetails
where parentid in (select userid from #temptab where id = @id)
and isactive = 1
set @id= @id + 1
end
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from #temptab
update #users
set valid = 0
where userid = @userid
end
drop table #temptab
drop table #users
Set nocount off
end
Please help me .....
sql
sql-server tsql sql-server-2008 sql
sql-server tsql sql-server-2008 edited Nov 14 '18 at 10:10
marc_s
575k12811101257
asked Nov 14 '18 at 10:05
katrajkatraj
1307
edited Nov 14 '18 at 10:10
marc_s
575k12811101257
asked Nov 14 '18 at 10:05
katrajkatraj
1307
edited Nov 14 '18 at 10:10
marc_s
575k12811101257
edited Nov 14 '18 at 10:10
marc_s
575k12811101257
edited Nov 14 '18 at 10:10
marc_s
575k12811101257
575k12811101257
asked Nov 14 '18 at 10:05
katrajkatraj
1307
asked Nov 14 '18 at 10:05
katrajkatraj
1307
asked Nov 14 '18 at 10:05
katrajkatraj
1307
1307
1
Post your table structure, existing data example and desired output/how you want the tables to look at the end of the process. Picking apart that code and writing something set based that does the same thing is a big ask when we can't see the data it works on or what it should produce, other than by picking through the code
– Caius Jard
Nov 14 '18 at 10:10
1
Don't use loops in SQL. Use a set based approach instead. To help us help you, please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 14 '18 at 10:14
@ZoharPeled sample data even iam not having the table is having millions of data iam not understanding what they are doing please try to understand
– Chanukya
Nov 14 '18 at 10:33
if you can, try and think of how to make the inner loop into a set based operation. A cursor may not be much faster.
– Cato
Nov 14 '18 at 11:10
1
Using a Cursor or rCTE are probably not going to make things any/much better here. AWHILEis simply a different way of writing a cursor, where as an rCTE can be a form of RBAR as well. (Some likeWITH C AS (SELECT 0 AS I UNION ALL SELECT I + 1 FROM C WHERE I +1 < 50) SELECT I FROM C;is actually an RBAR process, for example). The fact that you are using a hierarchy means that an rCTE is likely to be RBAR as well; but without knowing the data, expected results, etc, that's a guess.
– Larnu
Nov 14 '18 at 11:47
|
show 3 more comments
1
Post your table structure, existing data example and desired output/how you want the tables to look at the end of the process. Picking apart that code and writing something set based that does the same thing is a big ask when we can't see the data it works on or what it should produce, other than by picking through the code
– Caius Jard
Nov 14 '18 at 10:10
1
Don't use loops in SQL. Use a set based approach instead. To help us help you, please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 14 '18 at 10:14
@ZoharPeled sample data even iam not having the table is having millions of data iam not understanding what they are doing please try to understand
– Chanukya
Nov 14 '18 at 10:33
if you can, try and think of how to make the inner loop into a set based operation. A cursor may not be much faster.
– Cato
Nov 14 '18 at 11:10
1
Using a Cursor or rCTE are probably not going to make things any/much better here. AWHILEis simply a different way of writing a cursor, where as an rCTE can be a form of RBAR as well. (Some likeWITH C AS (SELECT 0 AS I UNION ALL SELECT I + 1 FROM C WHERE I +1 < 50) SELECT I FROM C;is actually an RBAR process, for example). The fact that you are using a hierarchy means that an rCTE is likely to be RBAR as well; but without knowing the data, expected results, etc, that's a guess.
– Larnu
Nov 14 '18 at 11:47
1
1
Post your table structure, existing data example and desired output/how you want the tables to look at the end of the process. Picking apart that code and writing something set based that does the same thing is a big ask when we can't see the data it works on or what it should produce, other than by picking through the code
– Caius Jard
Nov 14 '18 at 10:10
Post your table structure, existing data example and desired output/how you want the tables to look at the end of the process. Picking apart that code and writing something set based that does the same thing is a big ask when we can't see the data it works on or what it should produce, other than by picking through the code
– Caius Jard
Nov 14 '18 at 10:10
1
1
Don't use loops in SQL. Use a set based approach instead. To help us help you, please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 14 '18 at 10:14
Don't use loops in SQL. Use a set based approach instead. To help us help you, please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 14 '18 at 10:14
@ZoharPeled sample data even iam not having the table is having millions of data iam not understanding what they are doing please try to understand
– Chanukya
Nov 14 '18 at 10:33
@ZoharPeled sample data even iam not having the table is having millions of data iam not understanding what they are doing please try to understand
– Chanukya
Nov 14 '18 at 10:33
if you can, try and think of how to make the inner loop into a set based operation. A cursor may not be much faster.
– Cato
Nov 14 '18 at 11:10
if you can, try and think of how to make the inner loop into a set based operation. A cursor may not be much faster.
– Cato
Nov 14 '18 at 11:10
1
1
Using a Cursor or rCTE are probably not going to make things any/much better here. A
WHILE is simply a different way of writing a cursor, where as an rCTE can be a form of RBAR as well. (Some like WITH C AS (SELECT 0 AS I UNION ALL SELECT I + 1 FROM C WHERE I +1 < 50) SELECT I FROM C; is actually an RBAR process, for example). The fact that you are using a hierarchy means that an rCTE is likely to be RBAR as well; but without knowing the data, expected results, etc, that's a guess.– Larnu
Nov 14 '18 at 11:47
Using a Cursor or rCTE are probably not going to make things any/much better here. A
WHILE is simply a different way of writing a cursor, where as an rCTE can be a form of RBAR as well. (Some like WITH C AS (SELECT 0 AS I UNION ALL SELECT I + 1 FROM C WHERE I +1 < 50) SELECT I FROM C; is actually an RBAR process, for example). The fact that you are using a hierarchy means that an rCTE is likely to be RBAR as well; but without knowing the data, expected results, etc, that's a guess.– Larnu
Nov 14 '18 at 11:47
|
show 3 more comments
1 Answer
1
active
oldest
votes
1
Following our conversation in the comments, what you need is to insert to the Parent_Child_UserDetails table all the parent ids and user ids where both are active,
you can replace that RBAR nightmare of a code with a single insert....select statement, like this:
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from userdetails as t0
where isactive = 1
and exists
(
select 1
from userdetails as t1
where t1.isavtice = 1
and t1.userId = t0.ParentId
)
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53297573%2fwhile-loop-taking-so-much-time-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Following our conversation in the comments, what you need is to insert to the Parent_Child_UserDetails table all the parent ids and user ids where both are active,
you can replace that RBAR nightmare of a code with a single insert....select statement, like this:
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from userdetails as t0
where isactive = 1
and exists
(
select 1
from userdetails as t1
where t1.isavtice = 1
and t1.userId = t0.ParentId
)
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
add a comment |
1
Following our conversation in the comments, what you need is to insert to the Parent_Child_UserDetails table all the parent ids and user ids where both are active,
you can replace that RBAR nightmare of a code with a single insert....select statement, like this:
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from userdetails as t0
where isactive = 1
and exists
(
select 1
from userdetails as t1
where t1.isavtice = 1
and t1.userId = t0.ParentId
)
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
add a comment |
1
1
1
Following our conversation in the comments, what you need is to insert to the Parent_Child_UserDetails table all the parent ids and user ids where both are active,
you can replace that RBAR nightmare of a code with a single insert....select statement, like this:
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from userdetails as t0
where isactive = 1
and exists
(
select 1
from userdetails as t1
where t1.isavtice = 1
and t1.userId = t0.ParentId
)
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
Following our conversation in the comments, what you need is to insert to the Parent_Child_UserDetails table all the parent ids and user ids where both are active,
you can replace that RBAR nightmare of a code with a single insert....select statement, like this:
insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid
from userdetails as t0
where isactive = 1
and exists
(
select 1
from userdetails as t1
where t1.isavtice = 1
and t1.userId = t0.ParentId
)
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
edited Nov 14 '18 at 13:12
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
answered Nov 14 '18 at 12:55
Zohar PeledZohar Peled
53.9k73273
53.9k73273
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53297573%2fwhile-loop-taking-so-much-time-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Post your table structure, existing data example and desired output/how you want the tables to look at the end of the process. Picking apart that code and writing something set based that does the same thing is a big ask when we can't see the data it works on or what it should produce, other than by picking through the code
– Caius Jard
Nov 14 '18 at 10:10
1
Don't use loops in SQL. Use a set based approach instead. To help us help you, please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 14 '18 at 10:14
@ZoharPeled sample data even iam not having the table is having millions of data iam not understanding what they are doing please try to understand
– Chanukya
Nov 14 '18 at 10:33
if you can, try and think of how to make the inner loop into a set based operation. A cursor may not be much faster.
– Cato
Nov 14 '18 at 11:10
1
Using a Cursor or rCTE are probably not going to make things any/much better here. A
WHILEis simply a different way of writing a cursor, where as an rCTE can be a form of RBAR as well. (Some likeWITH C AS (SELECT 0 AS I UNION ALL SELECT I + 1 FROM C WHERE I +1 < 50) SELECT I FROM C;is actually an RBAR process, for example). The fact that you are using a hierarchy means that an rCTE is likely to be RBAR as well; but without knowing the data, expected results, etc, that's a guess.– Larnu
Nov 14 '18 at 11:47