Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Cross Join Help Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 9:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
dwilliscp (7/25/2014)
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'APAG', N'1000.000', N'KG', N'4.000', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'400.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'4.138', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'21E-4440', N'0002', N'SIL', N'1000.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8260', N'0001', N'SIL', N'1000.000', N'KG', N'1.200', N'H', N'0.000', N'999999999.000', N'FX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'3.154', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'21E-4440', N'0001', N'SIL', N'985.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'KG', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8262', N'1', N'SIL', N'1018.000', N'KG', N'0.250', N'STD', N'0.000', N'99999999.000', N'EX', N'2', N'4', N'KG', N'18.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8263', N'1', N'SIL', N'208.000', N'KG', N'0.100', N'STD', N'0.000', N'99999999.000', N'60', N'2', N'4', N'kg', N'8.000', N'')



NONE of your columns in the DDL provided are declared using NVARCHAR, all are VARCHAR. Why are all of your string values preceded by N to indicate UNICODE string values?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1596300
Posted Friday, July 25, 2014 11:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?


Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.

Also, I'd personally like to know what it is you mmean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1596328
Posted Sunday, July 27, 2014 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
Jeff Moden (7/25/2014)
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?


Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.

Also, I'd personally like to know what it is you mmean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.


Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.
Post #1596621
Posted Sunday, July 27, 2014 8:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:40 AM
Points: 6,842, Visits: 13,372
Once we have sample data to work with it should be a straight forward approach.
It looks like you're looking for the setup time per resource and product.
If that's the case you'll also need the business hours in order to ignore off-time (e.g. breaks or shutdown time).
Furthermore, the results need to be analyzed further to take additional down time into account (e.g. waiting for raw material).

@Jeff: the resource in a plant is the bathroom and the time is needed between the "usage of the toilet paper holder" ("part A") and the "hot water heater" ("Part B"). The "setup" that needs to be done is flush the toilet, put on your trousers, close toilet lid, leave the toilet and approach the sink to wash your hands




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1596622
Posted Monday, July 28, 2014 7:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
dwilliscp (7/27/2014)
Jeff Moden (7/25/2014)
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?


Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.

Also, I'd personally like to know what it is you mean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.


Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.


Maybe it's just these old eyes but I see a cursor and sample data with "Material_Number" in them but no "Part_Number". Is "Material_Number" what you're talking about?

Also, I'm still not bagging what you're raking about the From_Material and To_Material. To me, your cursor...

declare @plant as varchar(4)
, @Resource_Code as varchar(8)
, @Material_Number as varchar(18)
set nocount on;
declare i_cursor insensitive cursor
for Select Plant, Resource_Code, Material_Number From Resource_Mat
open i_Cursor

while @@fetch_status = 0
begin
insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material)
select 'SPOR' as Company
, Plant
, Resource_Code
, @Material_Number as From_Material
, Material_Number as To_Material
from Resource_Mat
where @Plant = plant and @resource_code = resource_code
end
fetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number
close i_Cursor
deallocate i_cursor
set nocount off;
go


... looks like a massive CROSS JOIN of the Resource_Mat table to itself based on the "plant" and "resource_code". While that doesn't make sense to me (even though you mentioned that in the title of the post), if that's what you really need, the following code should do it for you...

 INSERT INTO dbo.Trans_Matrix
(Company, Plant, Resource_Code, From_Material, To_Material)
SELECT Company = 'SPOR'
,Plant = frommat.Plant
,Resource_Code = frommat.Resource_Code
,From_Material = frommat.Material_Number
,To_Material = tomat.Material_Number
FROM dbo.Resource_Mat frommat
JOIN dbo.Resource_Mat tomat --This is going to work like a conditional CROSS JOIN
ON frommat.Plant = tomat.Plant
AND frommat.Resource_Code = tomat.Resource_Code
;

... and, compared to the cursor, it will run like the wind generating millions of rows in just a couple of seconds if you have some decent indexing on the Resource_Mat table.

Of course, you never answered my question about what you meant by a "massive" table. How many rows does it actually have in it???

Last but not least, if you go back and look at HanShi's post (the second post on this thread), he suggested the exact same thing I just did. You just needed to study the code a bit to realize that it's probably exactly what you wanted sans some of your column names and the missing criteria for the Resource_Code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1597133
Posted Wednesday, July 30, 2014 10:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
I cut the table down to the fields needed... and fixed Plant, when I renamed the plants I exceeded the field size.

CREATE TABLE [dbo].[Resource_Mat](
[Plant] [varchar](5) NOT NULL,
[Material_number] [varchar](18) NOT NULL,
[Production_Version] [varchar](4) NOT NULL,
[Resource_Code] [varchar](8) NOT NULL,

CONSTRAINT [PK_Resource_Mat] PRIMARY KEY CLUSTERED
(
[Plant] ASC,
[Material_number] ASC,
[Production_Version] ASC,
[Resource_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8260', '0001', 'SIL')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8260', '0001', 'APAG')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8261', '0001', 'APAG')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8261', '0001', 'SIL')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '21E-4440', '0002', 'SIL')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8260', '0001', 'SIL')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8261', '0001', 'APAG')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '21E-4440', '0001', 'SIL')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8262', '0001', 'SIL')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8263', '0001', 'SIL')
Post #1597858
Posted Wednesday, July 30, 2014 10:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
So we end up with...

Plant Material_number Production_Version Resource_Code
CHANC 11C-8260 0001 APAG
CHANC 11C-8260 0001 SIL
CHANC 11C-8261 0001 APAG
CHANC 11C-8261 0001 SIL
CHANC 21E-4440 0002 SIL
LEX 11C-8260 0001 SIL
LEX 11C-8261 0001 APAG
LEX 11C-8262 0001 SIL
LEX 11C-8263 0001 SIL
LEX 21E-4440 0001 SIL

So we should end up with:

Plant CHANC
-------------
Resource APAG

From Material To Material
11C-8260 11C-8260
11C-8260 11C-8261
11C-8261 11C-8261
11C-8261 11C-8260

Resource SIL
From Material To Material
11C-8260 11C-8260
11C-8260 11C-8261
11C-8260 21E-4440
11C-8261 11C-8260
11C-8261 11C-8261
11C-8261 21E-4440
21E-4440 11C-8260
21E-4440 11C-8261
21E-4440 21E-4440




Post #1597861
Posted Wednesday, July 30, 2014 11:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
Jeff Moden (7/28/2014)
dwilliscp (7/27/2014)
Jeff Moden (7/25/2014)
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?


Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.

Also, I'd personally like to know what it is you mean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.


Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.


Maybe it's just these old eyes but I see a cursor and sample data with "Material_Number" in them but no "Part_Number". Is "Material_Number" what you're talking about?

Also, I'm still not bagging what you're raking about the From_Material and To_Material. To me, your cursor...

declare @plant as varchar(4)
, @Resource_Code as varchar(8)
, @Material_Number as varchar(18)
set nocount on;
declare i_cursor insensitive cursor
for Select Plant, Resource_Code, Material_Number From Resource_Mat
open i_Cursor

while @@fetch_status = 0
begin
insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material)
select 'SPOR' as Company
, Plant
, Resource_Code
, @Material_Number as From_Material
, Material_Number as To_Material
from Resource_Mat
where @Plant = plant and @resource_code = resource_code
end
fetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number
close i_Cursor
deallocate i_cursor
set nocount off;
go


... looks like a massive CROSS JOIN of the Resource_Mat table to itself based on the "plant" and "resource_code". While that doesn't make sense to me (even though you mentioned that in the title of the post), if that's what you really need, the following code should do it for you...

 INSERT INTO dbo.Trans_Matrix
(Company, Plant, Resource_Code, From_Material, To_Material)
SELECT Company = 'SPOR'
,Plant = frommat.Plant
,Resource_Code = frommat.Resource_Code
,From_Material = frommat.Material_Number
,To_Material = tomat.Material_Number
FROM dbo.Resource_Mat frommat
JOIN dbo.Resource_Mat tomat --This is going to work like a conditional CROSS JOIN
ON frommat.Plant = tomat.Plant
AND frommat.Resource_Code = tomat.Resource_Code
;

... and, compared to the cursor, it will run like the wind generating millions of rows in just a couple of seconds if you have some decent indexing on the Resource_Mat table.

Of course, you never answered my question about what you meant by a "massive" table. How many rows does it actually have in it???

Last but not least, if you go back and look at HanShi's post (the second post on this thread), he suggested the exact same thing I just did. You just needed to study the code a bit to realize that it's probably exactly what you wanted sans some of your column names and the missing criteria for the Resource_Code.


Thanks Jeff, I had not thought to write the query this way.. It is a bit hard to tell just how many rows I will end up with.. since I have to merge .dat files from each plant, but it is comon for a resource to have around 500 materials + version... but we are only taking the highest version number for this process. (I tend to say part number, but the MRP software uses the name "material number". Sorry for the confusion.) Straight math would be 500 * 500 per resource per plant. Our first plant.. that we are working on has over 1 Mil rows.. but then again I would have to filter down for max version, to see just how many will be fed into this process... I am working on a stored proc, that will load these into a "IMP" table then use that table to create the matrix.
Post #1597866
Posted Wednesday, July 30, 2014 11:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
500*500 is only a quarter million row Cross Join and would probably take less than a quarter second to materialize and just a second or two to insert into an indexed table.

For the million row thing, you definitely have to be careful because 1*10^6 squared is 1*10^12 or a Trillion rows. Some of this could be mitigated by using a "Triangular" join, which would produce a bit less than half that of a full up Cross Join but 500 Billion rows is still nothing to sneeze at either duration wise or storage wise.

My recommendation would be to NOT turn this "Matrix" into a table.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1597876
Posted Wednesday, July 30, 2014 1:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
Jeff Moden (7/30/2014)
500*500 is only a quarter million row Cross Join and would probably take less than a quarter second to materialize and just a second or two to insert into an indexed table.

For the million row thing, you definitely have to be careful because 1*10^6 squared is 1*10^12 or a Trillion rows. Some of this could be mitigated by using a "Triangular" join, which would produce a bit less than half that of a full up Cross Join but 500 Billion rows is still nothing to sneeze at either duration wise or storage wise.

My recommendation would be to NOT turn this "Matrix" into a table.


The 500 rows was only one Resource at one plant. My cross join was doing more than it should.. but still.. this is working great for one plant. Once I get all the files imported into SQL server I will test run time. I am hoping that since a material can have several versions.. and thus rows.. I will not end up with 1,000,000 * 1,000,000.. or worse since I still have a second company to work with. Do you have a good link for "Triangular" join, never heard of it.

Oh I have also asked if it would help to build a table to show what resources we can leave out of this process, I know our plant scheduling software does not use every resource that plant has in our ERP software.

Oh BTW the 500 * 500 takes 10 seconds to execute (select and insert).. I need your hardware LOL.
Post #1597950
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse