July 30, 2014 at 10:40 am
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')
July 30, 2014 at 10:50 am
So we end up with...
PlantMaterial_numberProduction_VersionResource_Code
CHANC11C-82600001 APAG
CHANC11C-82600001 SIL
CHANC11C-82610001 APAG
CHANC11C-82610001 SIL
CHANC21E-4440 0002 SIL
LEX11C-82600001 SIL
LEX11C-82610001 APAG
LEX11C-82620001 SIL
LEX11C-82630001 SIL
LEX21E-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
July 30, 2014 at 11:04 am
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.
July 30, 2014 at 11:36 am
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
Change is inevitable... Change for the better is not.
July 30, 2014 at 1:58 pm
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.
July 30, 2014 at 3:49 pm
I don't really understand why "product per lin within a plant" would be end up as a cross join.
Based on your example you have two rows provided:
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')
Based on your sample data resource APAG goes from 11C-8260 to 11C-8261 at CHANC.
How do you end up with a requested output of
Resource APAG
From Material To Material
11C-8260 11C-8260
11C-8260 11C-8261
11C-8261 11C-8261
11C-8261 11C-8260
for CHANC??
Going from 11C-8260 to 11C-8260 is nowwhere mentioned nor is 11C-8261 to 11C-8261 or 11C-8261 to 11C-8260.
What is the business rule leading to this result set based on the given sample data?
You'll never be able to determine the time from 11C-8260 to 11C-8260 nor 11C-8261 to 11C-8261 nor 11C-8261 to 11C-8260 since there's not a single information available...
July 30, 2014 at 6:09 pm
dwilliscp (7/30/2014)
Do you have a good link for "Triangular" join, never heard of it.
The author 😀 of the article at the following link has put it pretty simply although it's for a correlated sub-query instead of being in the FROM clause. The same effect is realized, though.
http://www.sqlservercentral.com/articles/T-SQL/61539/
Oh BTW the 500 * 500 takes 10 seconds to execute (select and insert). I need your hardware LOL.
Something is definitely wrong there. Even my circa 2002 32 bit single CPU box does better than that. What do you get for an output from the following?
SET STATISTICS TIME ON
;
SELECT TOP 250000
RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
INTO #MyHead
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
SET STATISTICS TIME OFF
;
DROP TABLE #MyHead
;
GO 3
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply