If not object_id('[ItemSKU]') is null
? Drop table [ItemSKU]
Go
Create table [ItemSKU]([SKU] int,[Qty] int)
go
declare @i int?
set @i=1?
while(@i <=100000)?
begin?
insert into ItemSKU values(@i,@i*2)?
set @[email protected]+1?
end?
go
select * from ITEMSKU
go
If not object_id('[ItemSold]') is null
? Drop table [ItemSold]
Go
Create table [ItemSold]([SKU] int,[Qty] int)
go
declare @i int?
set @i=1?
while(@i <=100000)?
begin?
insert into ItemSold values(@i,@i-1)?
set @[email protected]+2?
end?
go
select * from ItemSold
go
If not object_id('[ItemRecd]') is null
? Drop table [ItemRecd]
Go
Create table [ItemRecd]([SKU] int,[Qty] int)
go
declare @i int?
set @i=2?
while(@i <=100000)?
begin?
insert into ItemRecd values(@i,@i-1)?
set @[email protected]+2?
end?
go
select * from ItemRecd
go
If not object_id('[ItemTransf]') is null
? Drop table [ItemTransf]
Go
Create table [ItemTransf]([SKU] int,[Qty] int)
go
declare @i int?
set @i=1?
while(@i <=100000)?
begin?
insert into ItemTransf values(@i,@i)?
set @[email protected]+1?
end?
go
select * from ItemTransf
go
select a.SKU, isnull(a.[Qty],0) as BB, isnull(b.[Qty],0) as sold, ISNULL(c.[Qty],0) as received, ISNULL(d.[Qty],0) as transfer, isnull(a.[Qty],0)-isnull(b.[Qty],0)+ISNULL(c.[Qty],0)+ISNULL(d.[Qty],0) as balance
from
? ItemSKU a?
? full join ItemSold b on a.SKU=b.SKU?
? full join ItemRecd c on a.SKU=c.SKU?
? full join ItemTransf d on a.SKU=d.SKU?
go
If not object_id('[ItemInventory]') is null
? Drop table [ItemInventory]
Go
Create table [ItemInventory]([SKU] int,[OpenQty] int,[Qty] int,[Recd] int,[Sold] int,[TranIn] int,[TransOut] int,[Return] int,[Adj] int)
go
declare @i int?
set @i=1?
while(@i <=100000)?
begin?
insert into ItemInventory values(@i,@i*10,@i+5,@i+8,@i+2,@i+1,@i,@i,@i*-1)?
set @[email protected]+1?
end?
go
select *,[OpenQty]+[Qty]+[Recd]-[Sold]+[TranIn]-[TransOut]-[Return]+[Adj] balance from ItemInventory
go
我最近因为想对我的系统作出数据库上的最后决定，测试了一下JOIN的效率问题， 发现效率却是非常的高， 远远超出我的想象，高手们看看我的思维有什么问题。
我要建立一个库存系统，其实一个库存是可以分成两部分，一部分是销售数据，一部分是进出库数据，从理论上讲，销售是频繁发生的，进出是比较不活跃的。 所以如果要把一个商品的整个活动用一条记录来讲，销售数据是密集的，进出数据是个稀疏的。这会造成存储空间的极大浪费。因此想把两者分开放，然后在查询制表的时候重新组合起来，我一直担心这个效率问题。所以我做了上述的SQL的实践。
关键是俩条语句，
A: from ItemSKU a?
? full join ItemSold b on a.SKU=b.SKU?
? full join ItemRecd c on a.SKU=c.SKU?
? full join ItemTransf d on a.SKU=d.SKU?
B: select *,[OpenQty]+[Qty]+[Recd]-[Sold]+[TranIn]-[TransOut]-[Return]+[Adj] balance from ItemInventory
用A的方法,我可以做到每条数据都是事实实在在有用的,没有丝毫的浪费, 用B的方法,会造成许多空白单元.
我的测试环境是这样的,
软件: WINDOWS2003, MSSQL2005 以及 SQL Server Management Studio Express.
硬件: COMPAQ PROLIANT 6500, 相当老的一台服务器,用500元钱买来的二手货用来玩的, 但是装2003一点都没有问题,也不需要装任何驱动. 很慢的,但是可靠, 双CPU的XEON 400MHz 一个18G+四个9G的SCSI磁盘组成的RAID.
我用了三组数据, 每组都用同样的结构,但是数据量在三个不同的级别,
第一个: 10万级
插入记录大概是27秒, A查询用了16秒,B查询用了14秒
第二个: 1百万级
插入记录大概是22分, A查询用了2分4秒,B查询用了2分6秒
第三个: 1千万级
插入记录大概是XX小时(没有记录,以后补上,一天一夜大该可以插入4.8千万记录), A查询用了23分7秒,B查询用了21分56秒.
我感到很吃惊, JOIN原来是不可怕的, 我的思路有没有问题, 欢迎高手低手们的意见.
------解决方案--------------------
请把表长度扩大到每张表10-30个字段，中间至少包含三分之一以上的字符数据类型
------解决方案--------------------
是的,关系数据库的原理就是join, 另不需要两个系统吧,直接两个表就行了阿, 其实现在硬盘很大, 完全可以放到一张表里(但建议还是3范式的好)
------解决方案--------------------
16秒还不可怕？一两秒正好，超过三秒客户就没耐心了