Why is it even remotely difficult? If there is only one relational database, that will be exactly the problem you are describing: 3 tables and 2 many-to-many relationships. The number "3 billion" appears only if a cursory full Cartesian alliance remains. Just follow the basic, normalized design:
:setvar dbname test :setvar PRODUCTSCOUNT 30000 :setvar PARTSCOUNT 5000 :setvar FINISHESCOUNT 2000 :setvar PRODUCTSPARTS 50 :setvar PARTFINISHES 1 use master; set nocount on; go rollback go :on error exit if db_id('$(dbname)') is not null begin alter database [$(dbname)] set single_user with rollback immediate; drop database [$(dbname)]; end go create database [$(dbname)] on (name = test_data, filename='c:\temp\test.mdf', size = 10GB) log on (name = test_log, filename='c:\temp\test.ldf', size = 100MB); go use [$(dbname)]; go create table Products ( Product_Id int not null identity(0,1) primary key, Description varchar(256)); go create table Parts ( Part_Id int not null identity(0,1) primary key, Description varchar(256)); create table Finishes ( Finish_Id smallint not null identity(0,1) primary key, Description varchar(256)); create table ProductParts ( Product_Id int not null, Part_Id int not null, constraint fk_products_parts_product foreign key (Product_Id) references Products (Product_Id), constraint fk_product_parts_part foreign key (Part_Id) references Parts (Part_Id), constraint pk_product_parts primary key (Product_Id, Part_Id)); create table PartFinishes ( Part_Id int not null, Finish_Id smallint not null, constraint fk_part_finishes_part foreign key (Part_Id) references Parts (Part_Id), constraint fk_part_finishes_finish foreign key (Finish_Id) references Finishes (Finish_Id), constraint pk_part_finishes primary key (Part_Id, Finish_Id)); go -- populate Products declare @cnt int = 0, @description varchar(256); begin transaction; while @cnt < $(PRODUCTSCOUNT) begin set @description = 'Product ' + cast(@cnt as varchar(10)); insert into Products (Description) values (@description); set @cnt += 1; if @cnt % 1000 = 0 begin commit; raiserror (N'Inserted %d products', 0,1, @cnt); begin transaction; end end commit; raiserror (N'Done. %d products', 0,1, @cnt); go -- populate Parts declare @cnt int = 0, @description varchar(256); begin transaction; while @cnt < $(PARTSCOUNT) begin set @description = 'Part ' + cast(@cnt as varchar(10)); insert into Parts (Description) values (@description); set @cnt += 1; if @cnt % 1000 = 0 begin commit; raiserror (N'Inserted %d parts', 0,1, @cnt); begin transaction; end end commit; raiserror (N'Done. %d parts', 0,1, @cnt); go -- populate Finishes declare @cnt int = 0, @description varchar(256); begin transaction; while @cnt < $(FINISHESCOUNT) begin set @description = 'Finish ' + cast(@cnt as varchar(10)); insert into Finishes (Description) values (@description); set @cnt += 1; if @cnt % 1000 = 0 begin commit; raiserror (N'Inserted %d finishes', 0,1, @cnt); begin transaction; end end raiserror (N'Done. %d finishes', 0,1, @cnt); commit; go -- populate product parts declare @cnt int = 0, @parts int = 0, @part int, @product int = 0; begin transaction; while @product < $(PRODUCTSCOUNT) begin set @parts = rand() * ($(PRODUCTSPARTS)-1) + 1; set @part = rand() * $(PARTSCOUNT); while 0 < @parts begin insert into ProductParts (Product_Id, Part_Id) values (@product, @part); set @parts -= 1; set @part += rand()*10+1; if @part >= $(PARTSCOUNT) set @part = rand()*10; set @cnt += 1; if @cnt % 1000 = 0 begin commit; raiserror (N'Inserted %d product-parts', 0,1, @cnt); begin transaction; end end set @product += 1; end commit; raiserror (N'Done. %d product-parts', 0,1, @cnt); go -- populate part finishes declare @cnt int = 0, @part int = 0, @finish int, @finishes int; begin transaction; while @part < $(PARTSCOUNT) begin set @finishes = rand() * ($(PARTFINISHES)-1) + 1; set @finish = rand() * $(FINISHESCOUNT); while 0 < @finishes begin insert into PartFinishes (Part_Id, Finish_Id) values (@part, @finish); set @finish += rand()*10+1; if @finish >= $(FINISHESCOUNT) set @finish = rand()*10+1; set @finishes -= 1; set @cnt += 1; if @cnt % 1000 = 0 begin commit; raiserror (N'Inserted %d part-finishes', 0,1, @cnt); begin transaction; end end set @part += 1; end commit; raiserror (N'done. %d part-finishes', 0,1, @cnt); go
Now, if we put this through a basic test, the results will be good:
set statistics time on; set statistics io on; declare @product int = rand()*30000; select * from Products po join ProductParts pp on po.Product_Id = pp.Product_Id join Parts pa on pa.Part_Id = pp.Part_Id join PartFinishes pf on pf.Part_Id = pa.Part_Id join Finishes f on pf.Finish_id = f.Finish_Id where po.Product_Id = @product;
Lead time:
(33 row(s) affected) Table 'Finishes'. Scan count 0, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Parts'. Scan count 0, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PartFinishes'. Scan count 33, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'ProductParts'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Products'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.
This is a 5 ms runtime for a random product. And it’s nowhere near the “server”, I run it on my laptop. There are no surprises; all calls are covered by clustered indexes in these tables. I will let you set up a stress test for 500 users and measure for yourself how it works in concurrency. I expect him to hold well.