Need to sum columns: loops

Showing posts with label loops. Show all posts

Wednesday, March 21, 2012

Nested SQL Loops, Possible

Hi I am trying to get this to work But it only does the first 135 records.
Can I use Nested loops?
Thanks Daren
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
endHi
Your problem is that you do not reset @.counter2
DECLARE @.Counter int
DECLARE @.Counter2 int
SET @.Counter=1
WHILE @.Counter < 136
BEGIN
SET @.Counter2=1
WHILE @.Counter2 < 136
BEGIN
PRINT '@.counter = ' + CONVERT(varchar,@.counter) + ' @.counter2 = ' +
CONVERT(varchar,@.counter2)
SET @.Counter2 = @.Counter2 + 1
END
set @.Counter = @.Counter + 1
END
John
"Daren Hawes" <newsgroups@.webdesignmagic.com.au> wrote in message
news:eIRcNvnZFHA.3780@.tk2msftngp13.phx.gbl...
> Hi I am trying to get this to work But it only does the first 135
> records.
> Can I use Nested loops?
> Thanks Daren
> --
> declare @.Counter int
> declare @.Counter2 int
> select @.Counter=1
> select @.Counter2=1
> while @.Counter < 136
> begin
> while @.Counter2 < 136
> Begin
> Insert into dbo.tbl_Matrix (FromID,ToID)
> Values (@.Counter,@.Counter2)
> set @.Counter2 = @.Counter2 + 1
> End
> set @.Counter = @.Counter + 1
> end
>|||A table of numbers (table containing every integer from 1 to some
arbitrarily large number) is a handy and much more efficient way to do
the same thing:
INSERT INTO dbo.tbl_Matrix (fromid,toid)
SELECT N1.num, N2.num
FROM Numbers AS N1, tbl_number AS N2
WHERE N1.num BETWEEN 1 AND 135
AND N2.num BETWEEN 1 AND 135
David Portas
SQL Server MVP
--

Monday, March 19, 2012

Nested Loops vs. Hash Match

I have two tables, Users and UserFile. Users.UserID is a PK, and
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradI believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>

Nested Loops vs. Hash Match

I have two tables, Users and UserFile. Users.UserID is a PK, and
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
Jerad
I believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>

Nested Loops vs. Hash Match

s on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index S

on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
The following gave me the desired execution plan and query cost (similar to
plan and cost of Query 1), but it uses a temp table which I don't want to
(and shouldn't have to) do:
declare @.Table table(UserFileID int)
insert into @.Table
select UserFile.UserFileID
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
select count(*)
from @.Table t
join UserFile
on UserFile.UserFileID = t.UserFileID
where UserFileBlockTrades = 0
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Basically, I'm just trying to force it to filter UserFileBlockTrades *after*
the join, instead of before. Surely this is possible.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradHi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:eZzJ7Up4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index S

s on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index S

on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> The following gave me the desired execution plan and query cost (similar
> to plan and cost of Query 1), but it uses a temp table which I don't want
> to (and shouldn't have to) do:
> declare @.Table table(UserFileID int)
> insert into @.Table
> select UserFile.UserFileID
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> select count(*)
> from @.Table t
> join UserFile
> on UserFile.UserFileID = t.UserFileID
> where UserFileBlockTrades = 0
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Basically, I'm just trying to force it to filter UserFileBlockTrades
> *after* the join, instead of before. Surely this is possible.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Oh wow, you're exactly right Greg. I didn't have an index set up for
UserFileBlockTrades, and once I added it, both queries generated the exact
same plan.
Here's my question now (just so I understand this). The reason I didn't
have an index on UserFileBlockTrades in the first place, is because it is a
bit field, and the selectivity on it is extremely low (as I said in my
original post). So I thought SQL would ignore this index anyway.
So can you help me understand why this made a difference (why SQL did, in
fact, use my index), even considering the low selectivity of that filter? I
generally (if not always) avoid indexing my bit fields for this reason, but
now I see it is needed in some cases.
Thanks so much for your help.
Jerad
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OqgdXWp4FHA.2060@.TK2MSFTNGP09.phx.gbl...
> Hi Jerad
> The answer to this most likely lies in indexing, not re-structuring the
> query. What indexes are available on these tables? Running sp_helpindex
> [tablename] against both table would give us more of a full picture.
> As a started, I'd suggest that the following two indexes should be there,
> but the recommendation could change depending on whether either table has
> clustered indexes & on which columns..
> Users (UserLastActive, UserID)
> UserFile (UserID, UserFileBlockTrades)
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jerad Rose" <no@.spam.com> wrote in message
> news:eZzJ7Up4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>|||Seems like the optimiser found that bit column helpful in this case, but
keep in mind that including columns in indexes isn't always about
selectivity & row identification. It usually helps to have all columns
included in indexes in situations like this (whether in the WHERE, JOIN or
SELECT part of query), where not too many columns are involved in the query.
This allows SQL Server to find all the information in indexes, without
having to go back to the underlying table structures. This is important
because indexes are packed far more densely than tables (many more rows per
page) & can therefore usually give far higher performance.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:OtQo5fp4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Oh wow, you're exactly right Greg. I didn't have an index set up for
> UserFileBlockTrades, and once I added it, both queries generated the exact
> same plan.
> Here's my question now (just so I understand this). The reason I didn't
> have an index on UserFileBlockTrades in the first place, is because it is
> a bit field, and the selectivity on it is extremely low (as I said in my
> original post). So I thought SQL would ignore this index anyway.
> So can you help me understand why this made a difference (why SQL did, in
> fact, use my index), even considering the low selectivity of that filter?
> I generally (if not always) avoid indexing my bit fields for this reason,
> but now I see it is needed in some cases.
> Thanks so much for your help.
> Jerad
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OqgdXWp4FHA.2060@.TK2MSFTNGP09.phx.gbl...
>|||On Sun, 6 Nov 2005 01:04:53 -0500, Jerad Rose wrote:

>So can you help me understand why this made a difference (why SQL did, in
>fact, use my index), even considering the low selectivity of that filter?
I
>generally (if not always) avoid indexing my bit fields for this reason, but
>now I see it is needed in some cases.
Hi Jerad,
You didn't mention whether the new index was actually used in the
execution plans. Since you're saying that both queries are now using the
exact same plan, I suspect that the index was NOT used.
My assumption on why the index made a difference - I guess that there
were no statistics yet for the UserFileBlockTrades column. Since it's a
bit column, the optimizer would probably have estimated a selectivity of
50%. After adding the index, there were statistics; the optimizer now
knows that the selectivity will actually be 96% and decides on another
plan.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Jerad Rose (no@.spam.com) writes:
> Oh wow, you're exactly right Greg. I didn't have an index set up for
> UserFileBlockTrades, and once I added it, both queries generated the exact
> same plan.
I think Hugo's remark about statistics is right on the money, but as an
addendum, permit me to point out a gotcha with index on bit columns. This
was the query:
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
Say now that UserFileBlockTrades = 0 would be very selective, and give a
mere handful of rows. Would the index be used now? Probably not, because
you need to do this:
and UserFile.UserFileBlockTrades = convert(bit, 0)
this is because of the rules for implicit conversion in SQL Server, which
says that these are always performed according to a data-type precendence
order (which is in Books Online). And bit is converted to integer, and
0 is an integer.
I'm adding this, because I did exactly this mistake recently when I added
indexes on some bit columns, and then was surprised that the performance
boost was nowhere near to what I expected it to be.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hugo's point about statistics is probably relevant, but the fact that the
new index/s precisely cover the query probably has more to do with the
consistency in the plans because the optimiser will take their i/o
efficiency into account. Obviously its better to pull the
UserFileBlockTrades value out off the covering index than do a bookmark or
heap lookup to get the value during a s

.
Of course it would far clearer if Jerad would post the table structures,
index structures & execution plans.
Regards,
Greg Linwood
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970679CB84306Yazorman@.127.0.0.1...
> Jerad Rose (no@.spam.com) writes:
> I think Hugo's remark about statistics is right on the money, but as an
> addendum, permit me to point out a gotcha with index on bit columns. This
> was the query:
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> Say now that UserFileBlockTrades = 0 would be very selective, and give a
> mere handful of rows. Would the index be used now? Probably not, because
> you need to do this:
> and UserFile.UserFileBlockTrades = convert(bit, 0)
> this is because of the rules for implicit conversion in SQL Server, which
> says that these are always performed according to a data-type precendence
> order (which is in Books Online). And bit is converted to integer, and
> 0 is an integer.
> I'm adding this, because I did exactly this mistake recently when I added
> indexes on some bit columns, and then was surprised that the performance
> boost was nowhere near to what I expected it to be.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||On Sun, 6 Nov 2005 22:24:47 +1100, Greg Linwood wrote:

>Hugo's point about statistics is probably relevant, but the fact that the
>new index/s precisely cover the query probably has more to do with the
>consistency in the plans because the optimiser will take their i/o
>efficiency into account. Obviously its better to pull the
>UserFileBlockTrades value out off the covering index than do a bookmark or
>heap lookup to get the value during a s

.
Hi Greg,
Ah, I now see that I've been guilty of sloppy reading. I didn't check
the indexes you suggested carefully enough; I missed that you included
the joining column as well as the searched column in the index.
That brings me on yet another theory: maybe the optimizer decided to use
the new index because there was at first not a single index on the
joining column. That could be verified by changing the index to include
only the joining column.

>Of course it would far clearer if Jerad would post the table structures,
>index structures & execution plans.
Yes, indeed!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The new theory is also probably correct as well, but I wasn't what indexes
he had on those tables, so I just suggested new ones.
If Jerad sends through his existing indexes & the showplan, it'd make things
nice & clear! (c:
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:im0sm156ei3bau989tf0o7vi8mn2t4blaf@.
4ax.com...
> On Sun, 6 Nov 2005 22:24:47 +1100, Greg Linwood wrote:
>
> Hi Greg,
> Ah, I now see that I've been guilty of sloppy reading. I didn't check
> the indexes you suggested carefully enough; I missed that you included
> the joining column as well as the searched column in the index.
> That brings me on yet another theory: maybe the optimizer decided to use
> the new index because there was at first not a single index on the
> joining column. That could be verified by changing the index to include
> only the joining column.
>
> Yes, indeed!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

nested loops join

Hi,
I have a select statement that gets data from only one table.
When I write OPTION(LOOP JOIN) after this query and run it, the
execution time is 2-3 times faster than without OPTION(LOOP JOIN).
If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
JOIN)
Does anyone know why its faster with nested loops join even though I don't
join any tables?
Thanks!
//MalinDid you look at the actual execution plan to see what it is doing in both
cases?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a select statement that gets data from only one table.
> When I write OPTION(LOOP JOIN) after this query and run it, the
> execution time is 2-3 times faster than without OPTION(LOOP JOIN).
> If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
> JOIN)
> Does anyone know why its faster with nested loops join even though I don't
> join any tables?
> Thanks!
> //Malin
>|||The graphical execution plans are identical. (select <-- Clustered index
s

)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Thanks for helping.
// Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
> cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||for instance, ...perhaps you have a couple of search arguments ANDed and SQL
Server can join these
by two indexes (aka index intersection) and this is the join which is influe
nced by your hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||Hej :-),
I only have one argument in the where statement.
My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably already have seen in my previous message)
That's why I wonder where the "join" is?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> for instance, ...perhaps you have a couple of search arguments ANDed and
> SQL Server can join these by two indexes (aka index intersection) and this
> is the join which is influenced by your hint.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||Hej. :-)
Strange... Did you look at the execution plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
> Hej :-),
> I only have one argument in the where statement.
> My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably
> already have seen in my previous message)
> That's why I wonder where the "join" is?
> // Malin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>|||The graphical execution plans are identical. (select <-- Clustered index
s

)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Anything more I can do to find out what this can depend on?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVHTpHbRFHA.3076@.tk2msftngp13.phx.gbl...
> Hej. :-)
> Strange... Did you look at the execution plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
>|||In this example you have the value for Col1 as an integer. In the real
table is the datatype for Col1 an Integer?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The graphical execution plans are identical. (select <-- Clustered index
> s

)
> If I have set showplan_text on there is a difference.
> select col1, col2, col3
> from table1
> where col1=1234
> option(loop join)
> |--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
>
> select col1, col2, col3
> from table1
> where col1=1234
> |--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=1234) ORDERED FORWARD)
> Does "Convert([@.1])" have something to do with the execution time of the
> query?
> Thanks for helping.
> // Malin
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||yes "col1" is an integer in the real table, the query looks exactly as I
have written except the names :-)
//Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
> In this example you have the value for Col1 as an integer. In the real
> table is the datatype for Col1 an Integer?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||I will post to the internal group and see if anyone has seen this before.
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uvQUHabRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> yes "col1" is an integer in the real table, the query looks exactly as I
> have written except the names :-)
> //Malin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
>

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Nested Loops in the Control Flow

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Monday, March 12, 2012

nested cursors? @@FETCH_STATUS

Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am at
the end of the rowset....will the internal loop screw things up for the
external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
use that to control the loop?
Will this work or fail:
DECLARE vcursor cursor local for
select i.id from inserted
Open vcursor
FETCH NEXT FROM vcursor into @.id
WHILE @.@.FETCH_STATUS=0
BEGIN
declare acursor cursor local for
select classcode FROM CLASSES WHERE ID=@.ID
Open acursor
FETCH NEXT FROM acursor into @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM acursor into @.aclasscode
END
CLOSE acursor
DEALLOCATE acursor
END
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
eg will the internal loop reaching the last recods and setting
@.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they each
have their own 'instance' of @.@.FETCH_STATUS
Al Blake, Canberra, AustraliaYour code will work fine. You are fetching the records in each of the loop
and @.@.FETCH_STATUS contains the latest value. So it won't make any problem
Babu M K
Comat Techonologies Pvt. Ltd.
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:%231YQkILFFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
> If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am
at
> the end of the rowset....will the internal loop screw things up for the
> external loop? Do I need to save @.@.FETCH_STATUS off to another variable
and
> use that to control the loop?
> Will this work or fail:
> DECLARE vcursor cursor local for
> select i.id from inserted
> Open vcursor
> FETCH NEXT FROM vcursor into @.id
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> declare acursor cursor local for
> select classcode FROM CLASSES WHERE ID=@.ID
> Open acursor
> FETCH NEXT FROM acursor into @.aclasscode
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> -- do processing
> --
> FETCH NEXT FROM acursor into @.aclasscode
> END
> CLOSE acursor
> DEALLOCATE acursor
> END
> FETCH NEXT FROM vcursor into @.id
> END
> CLOSE vcursor
> DEALLOCATE vcursor
> eg will the internal loop reaching the last recods and setting
> @.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they
each
> have their own 'instance' of @.@.FETCH_STATUS
> Al Blake, Canberra, Australia
>|||On Thu, 17 Feb 2005 16:32:56 +1100, Al Blake wrote:

>Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
>If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am a
t
>the end of the rowset....will the internal loop screw things up for the
>external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
>use that to control the loop?
>Will this work or fail:
Hi Al,
As Babu said: this will work. But it will probably be S-L-O-W.
From your code, I see absolutely no reason to use two nested cursors. You
can just do it in one cursor. Depending on what "-- do processing" really
is, you might even be able to do it without cursors at all.
Here's a version with just one cursor. If you need help to create a
completely set-based version, post some more information about what this
code actually does - and check out www.aspfaq.com/5006 for how to provide
the information.
DECLARE vcursor cursor local for
SELECT i.id, c.classcode
FROM inserted AS i
INNER JOIN classes AS c
ON c.ID = i.ID
Open vcursor
FETCH NEXT FROM vcursor into @.id, @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Wednesday, March 21, 2012

Nested SQL Loops, Possible

Monday, March 19, 2012

Nested Loops vs. Hash Match

Nested Loops vs. Hash Match

Nested Loops vs. Hash Match

Nested Loops vs. Hash Match

nested loops join

Nested Loops in the Control Flow

Nested Loops in the Control Flow

Monday, March 12, 2012

nested cursors? @@FETCH_STATUS

Need to sum columns

Blog Archive

About Me