Dear Experts,
I want to write a very complex query on very huge tables.
The scenario is the following:
I have one table called #Products, one table called #Shops , one table called #Customers and one table called #CollectedInformation.
#Products (ProductID int, ProductName varchar(10))
Product Information. Around 70,000 unique products
#Shops(ShopID smallint, ShopName varchar(10))
Shop Information. Around 100 unique shops
#Customers(CustomerID smallint, CustomerName varchar(10)
Customer Information. Around 100 unique customers
#CollectedInformation(ProductID int, ShopID smallint, CustomerID int, SalesValue money)
Sales Information collected and inserted in a huge table. We don’t sell all products in all shops and to all customers. Maximum 70,000 products * 100 Shops * 100 customers, but usually it will contain around 650 million records instead of 700 million which is the max.
What I would like to get is for all products, fo all shops, for all customers to create a temporary table that will get SalesValue based on joining #Products, #Customers, #Shops with #CollectedInformation.
In case a product or customer or shop doesn’t have a value for field SalesValue I want it to appeear in the final table with Null as SalesValue.
To achieve this I used the CTE in SQL server 2005 to create a cartesian product between #Products, #Shops and #Customers and then I left outer join it with table #CollectedInformation.
(The cte command only with 70,000 products, 100 customers and only 2 - Out of 100 total shops takes around 1:20 minutes on a 2cpu 3.6GHz xeon 2GB Ram server connected with an HP MSA1000 SAN.)
Can you please help me to determine if there is a better way to do it. I Haven't tried it with the full set, but I believe it will be very slow.
Following is an example of the code I used for testing:
declare @.Customers int, @.Shops int, @.Products intdeclare @.counter int
-- select @.Customers = 100, @.Shops = 100, @.Products =70000
--select @.Customers = 100, @.Shops = 2, @.Products =70000
select @.Customers = 100, @.Shops = 2, @.Products =700
create table #Customers(CustomerID smallint, CustomerName varchar(10))
set @.counter=1
while @.counter<=@.Customers
begin
insert into #Customers values(@.counter,'Cn'+str(@.counter,8))
set @.counter = @.counter +1
end
create unique clustered index C1 on #Customers(CustomerID)
create table #Shops(ShopID smallint, ShopName varchar(10))
set @.counter=1
while @.counter<=@.Shops
begin
insert into #Shops values((@.counter*2)+100, 's'+str(@.counter,9))
set @.counter = @.counter +1
end
create unique clustered index S1 on #Shops(ShopID)
create table #Products (ProductID int, ProductName varchar(10))
set @.counter=1
while @.counter<=@.Products
begin
insert into #Products values(@.counter,'p'+str(@.counter,9))
set @.counter = @.counter +1
end
create unique clustered index P1 on #Products(ProductID)
create table #CollectedInformation(ProductID int, ShopID smallint, CustomerID int, SalesValue money)
declare @.TempShopID int
declare Shops_cursor cursor fast_forward for
select ShopID
from #Shops
open Shops_cursor
fetch next from Shops_cursor
into @.TempShopID
while @.@.fetch_status = 0
begin
insert into #CollectedInformation (ShopID, ProductID, CustomerID)
select @.TempShopID, P.ProductID, C.CustomerID
from #Customers C, #Products P
fetch next from Shops_cursor
into @.TempShopID
end
close Shops_cursor
deallocate Shops_cursor
create clustered index ci1 on #CollectedInformation (CustomerID)
create index ci2 on #CollectedInformation (ProductID)
create index ci3 on #CollectedInformation (ShopID)
declare @.random money
set @.random = rand()
update #CollectedInformation
set SalesValue = cast((10000*@.random)+(ProductID*@.random)+(CustomerID*@.random)+(ShopID*@.random) as money)
-- Following is the code that creates the final temporary table:
declare @.PeriodNo int, @.Indicate char(1)
set @.PeriodNo =120
set @.Indicate='A';
with results_cte (ProductID, ProductName, ShopID, ShopName, CustomerID, CustomerName )
as
(
select p.ProductID, p.ProductName, s.ShopID, s.ShopName, c.CustomerID, c.CustomerName
from #Products p, #Shops s, #Customers c
)
select @.PeriodNo as Period , @.Indicate as SpecialSymbol, r.ProductID, r.ProductName, r.CustomerID, r.CustomerName, r.ShopID, r.ShopName, ci.Salesvalue
into #temp
from results_cte r
left outer join #CollectedInformation ci on ci.ProductID=r.ProductID and ci.ShopID=r.ShopID and ci.CustomerID=r.CustomerID
order by r.ProductID, r.CustomerID, r.ShopID
--select top 100 * from #temp
drop table #Products
drop table #Shops
drop table #Customers
drop table #CollectedInformation
drop table #temp
What are you trying to achieve in the end? How is the temporary table going to be used?
If you want to report on this can I suggest you look at analysis services.
In your code, what takes the time? Be aware that you don't have an index on the CollectedInformation table.
In addition when you scale up to 650 million rows. Assuming you have 4 integer fields in this table you will be processing 10Gb of data from 1 table. You are planning on joining two of these together thats 20Gb of data.
Any large processing of this nature is going to take time, reduced by having more processing power and more memory.
|||Dear Simon,
The reason I need to create the temporary table is because I have to pass it through a CLR function which after a lot of processing, it will export data into a text file. (One line from the text file will consist of many rows from the #temp table - Shops will become columns on the text file - Every Product, every customer, salesvalueshop1, salesvalueshop2 etc. The CTE will be executed and processed by the CLR.)
Thanks for your advice on analysis services. I will take a look at it.
I have three indexes on the CollectedInformation
create clustered index ci1 on #CollectedInformation (CustomerID)
create index ci2 on #CollectedInformation (ProductID)
create index ci3 on #CollectedInformation (ShopID)
About the time and the size the table will contain at least: 1 int, 1 smallint, 3 tinyint, and 2 money fields(perhaps 1 can be reduced to smallmoney). The amount of data is per period. On the text file I may have up to 100 periods as well. I processed data period by period so that to reduce the size of the tables.
Actually I don't know if the cardesian product that the CTE creates which then left outer joins with CollectedInformation table is the best way. What i want from the query is to join collectedinformation with products, customers and shops. Some values from products, customers, shops will not be included on the collectedinformation table, but I want them to appear at the final text file with a * instead of the null value for thr collectedinformation.salesvalue (e.g. for one product, for one customer i have salesvalue for shop1 and shop2 only. text file: Product1, customer1, shop1salesvalue, shop2salesvalue, *, * No sales for shop 3 and 4)
Regards,
Spyros Christodoulou
