SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cross Join Help


Cross Join Help

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39447 Visits: 38552
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?

Cool
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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86267 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 775
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.
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10335 Visits: 13559
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86267 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 775
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')
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 775
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
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 775
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86267 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 775
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search