November 26, 2016 at 12:57 pm
Some one help me to convert the recursive method to iterative function.
USE [db]
GO
/****** Object: StoredProcedure [dbo].[proc_geneatecombkachha] Script Date: 11/27/2016 00:41:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_geneatecombkachha]
as
begin
delete from TempComb
Declare @NumberOfRows int;
Set @NumberOfRows = (Select Count(*) From KacchaStock)
Declare @Numbers Table(Number int);
insert into TempComb
select KPID,KPWeight,kpTouch,KPPure from KacchaStock
;With cte As
(Select 1 As Number
Union All
Select Number + 1
From cte
Where Number < Power(2, @NumberOfRows))--@MaxPowerValue * 2 - 1)
Insert @Numbers(Number)
Select Number From cte option (maxrecursion 0);
Declare @Summed Table(Name varchar(10), Value1 int, Value2 int,Value3 int, Number int);
;With cteGrouped As
(Select KPID, KPWeight, KPPure, KPTouch, Row_Number() Over(Order By KPID) As rn
From KacchaStock)
Insert @Summed(Name, Value1, Value2, Value3, Number)
Select KPID, Sum(KPWeight) Over(Partition By Number) As Value1,
Sum(KPTouch) Over(Partition By Number) As Value2,
Sum(KPPure) Over(Partition By Number) As Value3,
Number
From cteGrouped c
Cross Join @Numbers n
Where Power(2, rn - 1) & Number = Power(2, rn - 1)
Order By n.Number, KPID;
;With cte As
(Select Distinct Stuff((Select ',' + s2.Name
From @Summed s2 Where s.Number = s2.Number
Order By s2.Name
For XML Path('')),1,1,'') As Combination,
s.Value1, s.Value2, s.Value3
From @Summed s)
insert into TempComb
Select Combination, Value1 as Weight, (Value3*100)/Value1 as Touch, Value3 as Pure
From cte
Where PatIndex('%,%', Combination) > 1;
select * from TempComb
end
November 28, 2016 at 6:30 am
urbanromio2 (11/26/2016)
Some one help me to convert the recursive method to iterative function.USE [db]
GO
/****** Object: StoredProcedure [dbo].[proc_geneatecombkachha] Script Date: 11/27/2016 00:41:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_geneatecombkachha]
as
begin
delete from TempComb
Declare @NumberOfRows int;
Set @NumberOfRows = (Select Count(*) From KacchaStock)
Declare @Numbers Table(Number int);
insert into TempComb
select KPID,KPWeight,kpTouch,KPPure from KacchaStock
;With cte As
(Select 1 As Number
Union All
Select Number + 1
From cte
Where Number < Power(2, @NumberOfRows))--@MaxPowerValue * 2 - 1)
Insert @Numbers(Number)
Select Number From cte option (maxrecursion 0);
Declare @Summed Table(Name varchar(10), Value1 int, Value2 int,Value3 int, Number int);
;With cteGrouped As
(Select KPID, KPWeight, KPPure, KPTouch, Row_Number() Over(Order By KPID) As rn
From KacchaStock)
Insert @Summed(Name, Value1, Value2, Value3, Number)
Select KPID, Sum(KPWeight) Over(Partition By Number) As Value1,
Sum(KPTouch) Over(Partition By Number) As Value2,
Sum(KPPure) Over(Partition By Number) As Value3,
Number
From cteGrouped c
Cross Join @Numbers n
Where Power(2, rn - 1) & Number = Power(2, rn - 1)
Order By n.Number, KPID;
;With cte As
(Select Distinct Stuff((Select ',' + s2.Name
From @Summed s2 Where s.Number = s2.Number
Order By s2.Name
For XML Path('')),1,1,'') As Combination,
s.Value1, s.Value2, s.Value3
From @Summed s)
insert into TempComb
Select Combination, Value1 as Weight, (Value3*100)/Value1 as Touch, Value3 as Pure
From cte
Where PatIndex('%,%', Combination) > 1;
select * from TempComb
end
After looking at this query and wondering what on earth it was trying to accomplish, I realized that there might be a "scale" problem with your POWER function. As it turns out, any number of records larger than 30 will blow up the POWER function you have coded. If we change it to POWER(CAST(2 AS bigint), @NumberOfRows), then we can get to 62 records. Beyond the use of integer values, this methodology breaks down due to the limitations of the float data type. If you want to test that, just compare the results of 2 to the 100th power in the Windows calculator, vs. changing your code to POWER(2., @NumberOfRows), where you manually set @NumberOfRows to 100. I'm pretty sure that what you're trying to do is get all possible combinations of something, and I'm not sure that what you're doing is either realistic or desirable. The string processing alone will be extraordinarily expensive from a processor usage standpoint, so understanding just what you need to accomplish would be far more useful at this point.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 28, 2016 at 6:51 am
urbanromio2 (11/26/2016)
Some one help me to convert the recursive method to iterative function.
To be honest, I just don't have the time to try to science out what your undocumented code is trying to do. A description of what the intent is would be really useful in trying to help you.
A quick gander tells me that you're making at least three mistakes other than the one already pointed out.
1. You should use TRUNCATE rather than DELETE. It's much faster and much easier on the transaction log file.
2. Using a recursive CTE to increment a count is worse than using a WHILE loop for such a thing. Please see the following article on some alternate methods that will be MUCH quicker and less resource intensive. Please don't tell me that it's for a limited number of rows so that performance doesn't matter. The article will show you how bad things get even for a rowcount of 10.
http://www.sqlservercentral.com/articles/T-SQL/74118/
3. Get into the habit of using the 2 part naming convention for all objects. It does help a tiny, tiny bit for performance but, more importantly, will save your life someday when someone adds another schema.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2016 at 7:41 am
Jeff,
This poster has posted twice in this same forum area, and this was the one without any of the explanation on what is needed. The other post indicates they are indeed looking for all possible combinations, and the original goal was for up to 100 elements, but clearly, that's not realistic. A later post there indicated a willingness to settle for 30, but I honestly think that until we start measuring processor speeds in TeraHertz, or perhaps PetaHertz, that level of data quantity is not a realistic goal in any reasonable period of time. I'm also fairly certain that we're going to have to get optical circuitry instead of silicon-based circuitry before we have any chance at that kind of speed, unless a major scientific breakthrough occurs ... Either that or having 1,024-core processors at no greater cost than our current quad-core ones... AND the ability to "break up" the problem into more easily managed pieces that can be efficiently handled by such a processor-beast.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 28, 2016 at 10:02 am
I hate double postings for this very reason. :crazy: Thanks for the heads up. I left my spare CRAY somewhere and forgot where I put it so I guess I'll be moving on. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2016 at 11:15 am
Jeff Moden (11/28/2016)
urbanromio2 (11/26/2016)
Some one help me to convert the recursive method to iterative function.To be honest, I just don't have the time to try to science out what your undocumented code is trying to do. A description of what the intent is would be really useful in trying to help you.
A quick gander tells me that you're making at least three mistakes other than the one already pointed out.
1. You should use TRUNCATE rather than DELETE. It's much faster and much easier on the transaction log file.
2. Using a recursive CTE to increment a count is worse than using a WHILE loop for such a thing. Please see the following article on some alternate methods that will be MUCH quicker and less resource intensive. Please don't tell me that it's for a limited number of rows so that performance doesn't matter. The article will show you how bad things get even for a rowcount of 10.
http://www.sqlservercentral.com/articles/T-SQL/74118/
3. Get into the habit of using the 2 part naming convention for all objects. It does help a tiny, tiny bit for performance but, more importantly, will save your life someday when someone adds another schema.
4. Undefined variables
5. Unopened parens
No claim has been made by the OP that this eccentric piece of code actually works, but imho it's implied, and it wouldn't half help if it did.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 28, 2016 at 5:08 pm
Jeff Moden (11/28/2016)
I hate double postings for this very reason. :crazy: Thanks for the heads up. I left my spare CRAY somewhere and forgot where I put it so I guess I'll be moving on. 😛
It's a shame you can't "Like" a post here... 'cause I'd be liking your post repeatedly.... Do let me know if your spare CRAY shows up... Then look for the ON button... and press it... and then guess what??? You'll have a CRAY-ON !!! Sure wish there was some useful conclusion you could DRAW from that... But perhaps you'd be better off WAX-ing rhapsodic about all the "pictures" your CRAY-ON can handle...
I'm now laughing so hard I'm rolling on the floor and my butt has fallen off... :-D:-D:-D
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 28, 2016 at 6:02 pm
sgmunson (11/28/2016)
Jeff Moden (11/28/2016)
I hate double postings for this very reason. :crazy: Thanks for the heads up. I left my spare CRAY somewhere and forgot where I put it so I guess I'll be moving on. 😛It's a shame you can't "Like" a post here... 'cause I'd be liking your post repeatedly.... Do let me know if your spare CRAY shows up... Then look for the ON button... and press it... and then guess what??? You'll have a CRAY-ON !!! Sure wish there was some useful conclusion you could DRAW from that... But perhaps you'd be better off WAX-ing rhapsodic about all the "pictures" your CRAY-ON can handle...
I'm now laughing so hard I'm rolling on the floor and my butt has fallen off... :-D:-D:-D
Heh... if we gut the box and use it as a boat, would that mean that we're CRAY-fishing? I am kinda hooked on CRAYs and I'll drop you a line if I find mine. OAR, since I'm getting older, I might have to shift to an MPP box. That's a box provided by the military police that you can pee in, right? Since I am older, that suggests there are two meanings to "It Depends". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2016 at 7:23 pm
Jeff Moden (11/28/2016)
sgmunson (11/28/2016)
Jeff Moden (11/28/2016)
I hate double postings for this very reason. :crazy: Thanks for the heads up. I left my spare CRAY somewhere and forgot where I put it so I guess I'll be moving on. 😛It's a shame you can't "Like" a post here... 'cause I'd be liking your post repeatedly.... Do let me know if your spare CRAY shows up... Then look for the ON button... and press it... and then guess what??? You'll have a CRAY-ON !!! Sure wish there was some useful conclusion you could DRAW from that... But perhaps you'd be better off WAX-ing rhapsodic about all the "pictures" your CRAY-ON can handle...
I'm now laughing so hard I'm rolling on the floor and my butt has fallen off... :-D:-D:-D
Heh... if we gut the box and use it as a boat, would that mean that we're CRAY-fishing? I am kinda hooked on CRAYs and I'll drop you a line if I find mine. OAR, since I'm getting older, I might have to shift to an MPP box. That's a box provided by the military police that you can pee in, right? Since I am older, that suggests there are two meanings to "It Depends". 🙂
Yep, it always depends... and if you replace a bad power supply with a non-OEM unit, does that mean you can see through your old self because now you have an EX-CRAY ? And if we start rapping about this story line, then maybe our new rap name will be CRAY-ZEE ??? And if we have that box removed precisely because we gutted it and used it as a boat and now it's stinky with swamp water, that we experienced DE-CRAY ? OAR am I just experiencing an excess of CRAY-matter?
And if you change your name to Suzie while still in possession of that machine, do you become a CRAYPE-SUZETTE ? OAR if I keep this up, do I drive you to drink CRAY Goose (vodka)?
Nahhhhhhhhhhhh..... :hehe::hehe::hehe:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply