can someone improve this

Case of study:
I have warehouse which stores items.
Wharehouse has defined different locations - location is represented with
locationID.
(location has also type and distance - that is the distance in
meters from start position, but this data is used just for order)
Each location has one or more palette. Palette has unique label: itemOZN.
Each palette has many items(articles), and item is marked with itemID.
Each combination of palette and item has different stockID
(on the same palette can be items with the same itemID but different
properties)
So, if you want to define one item in stock, you must have its stockID.
When I get request to export one ot more items(articles) with some quantity,
I must find
all the positions of the items in the warehouse
(the same article can be on different locations) and take the most
appropriate ones (already reserved are at the end of the choice, and also
closer articles has
the precedence over the more distantiated ones,
also if the production date is older, than the article must go out from
stock before the new ones, and so on..)
When I find the appropriate items, I must wite them into reservation table.
That is stockID and quantity.
I hope that it's clear enough. It's simple example and I solve it with 2
cursor.
It works perfect but with slow performance.
You have my example with all DDL and test data.
I would like to know, if it's possible to create the same solution without
cursor because of performance reason.
The working code:
declare @.itemID varchar(10),@.quality int,@.requiredQ decimal(15,5),@.pDate
datetime,@.lokType int,@.stockID int
declare @.availableQ decimal(15,5)
BEGIN TRANSACTION
--first, I lookup for all required items to export from warehouse
declare cCur cursor local for select
itemID,quality,quantity,productionDate,l
okType from dbo.tblRequiredItems
open cCur
fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
while @.@.fetch_status=0
begin
--for each item I find all stocks and I order stocks from most appropriate
to the less appropriate(ORDER BY)
--if quality is gived I must find the items with required or better quality
else doesn't matter, the same is with production date
declare cCur1 cursor local for
select T1.stockID,T1.availableQ FROM
(select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
JOIN dbo.tblItems i1
ON r1.stockID=i1.stockID WHERE i1.itemID=@.itemID) then 1 else 0 end,
l.distance
from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
i.locationID=l.locationID
AND (@.lokType is null OR l.lokType=@.lokType) LEFT JOIN dbo.tblReservations
r ON i.stockID=r.stockID
WHERE i.itemID=@.itemID AND (@.pDate is null OR i.itemProductionDate>=@.pDate)
AND
(@.quality is null OR i.itemQuality>=@.quality)
GROUP BY i.stockID,i.itemQuantity,l.distance
having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
open cCur1
fetch next from cCur1 INTO @.stockID,@.availableQ
--I reserve items until reach the required quantity
while @.@.fetch_status=0
begin
if @.availableQ>=@.requiredQ
begin
if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
UPDATE dbo.tblReservations SET quantity=quantity+@.requiredQ WHERE
stockID=@.stockID
else
INSERT INTO dbo.tblReservations(stockID,quantity)
SELECT @.stockID,@.requiredQ
SET @.requiredQ=0
BREAK
end
else
begin
SET @.requiredQ=@.requiredQ-@.availableQ
if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
UPDATE dbo.tblReservations SET quantity=quantity+@.availableQ WHERE
stockID=@.stockID
else
INSERT INTO dbo.tblReservations(stockID,quantity)
SELECT @.stockID,@.availableQ
end
fetch next from cCur1 INTO @.stockID,@.availableQ
end
close cCur1
deallocate cCur1
if @.requiredQ>0
begin
RAISERROR(60017,11,1)
break
end
fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
end
close cCur
deallocate cCur
delete from dbo.tblRequiredItems
if @.@.error=0
COMMIT TRANSACTION
else
ROLLBACK TRANSACTION
The DDL with sample data:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblItems]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblLocation]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblRequiredItems]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblReservations]
GO
CREATE TABLE [dbo].[tblItems] (
[stockID] [int] IDENTITY (1, 1) NOT NULL ,
[itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
[itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[itemQuantity] [decimal](15, 5) NOT NULL ,
[itemQuality] [int] NOT NULL ,
[itemProductionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLocation] (
[locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[lokType] [int] NULL ,
[distance] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblRequiredItems] (
[requireID] [int] IDENTITY (1, 1) NOT NULL ,
[itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[quality] [int] NULL ,
[quantity] [decimal](15, 5) NOT NULL ,
[productionDate] [datetime] NULL ,
[lokType] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblReservations] (
[reservationID] [int] IDENTITY (1, 1) NOT NULL ,
[stockID] [int] NULL,
[quantity] [decimal](15, 5) NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblItems] ADD
CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED
(
[stockID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLocation] ADD
CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
(
[locationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblRequiredItems] ADD
CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY CLUSTERED
(
[requireID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblReservations] ADD
CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
(
[reservationID]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
VALUES('A001',1,100)
INSERT INTO [tblLocation]([locationID], [lokType], [distance])
VALUES('B001',1,200)
INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
VALUES('C001',2,150)
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0001','A001',20,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0002','A001',15,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0003','A001',50,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0001','B001',30,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0002','B001',60,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0003','B001',40,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0001','B001',5,1,'200502
02')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0002','B001',10,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0003','B001',30,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0001','C001',20,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0002','C001',5,1,'200502
02')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0003','C001',25,1,'20050
202')
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(1,5)
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(5,10)
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(9,12)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0001',1,40,'20050101',null)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0002',1,10,'20041212',null)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0003',null,10,null,2)>> can someone improve this
Nope.
"simon" wrote:

> Case of study:
> I have warehouse which stores items.
> Wharehouse has defined different locations - location is represented with
> locationID.
> (location has also type and distance - that is the distance in
> meters from start position, but this data is used just for order)
> Each location has one or more palette. Palette has unique label: itemOZN.
> Each palette has many items(articles), and item is marked with itemID.
> Each combination of palette and item has different stockID
> (on the same palette can be items with the same itemID but different
> properties)
> So, if you want to define one item in stock, you must have its stockID.
> When I get request to export one ot more items(articles) with some quantit
y,
> I must find
> all the positions of the items in the warehouse
> (the same article can be on different locations) and take the most
> appropriate ones (already reserved are at the end of the choice, and also
> closer articles has
> the precedence over the more distantiated ones,
> also if the production date is older, than the article must go out from
> stock before the new ones, and so on..)
> When I find the appropriate items, I must wite them into reservation table
.
> That is stockID and quantity.
> I hope that it's clear enough. It's simple example and I solve it with 2
> cursor.
> It works perfect but with slow performance.
> You have my example with all DDL and test data.
> I would like to know, if it's possible to create the same solution without
> cursor because of performance reason.
> The working code:
> declare @.itemID varchar(10),@.quality int,@.requiredQ decimal(15,5),@.pDate
> datetime,@.lokType int,@.stockID int
> declare @.availableQ decimal(15,5)
> BEGIN TRANSACTION
> --first, I lookup for all required items to export from warehouse
> declare cCur cursor local for select
> itemID,quality,quantity,productionDate,l
okType from dbo.tblRequiredItems
> open cCur
> fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
> while @.@.fetch_status=0
> begin
> --for each item I find all stocks and I order stocks from most appropriate
> to the less appropriate(ORDER BY)
> --if quality is gived I must find the items with required or better qualit
y
> else doesn't matter, the same is with production date
> declare cCur1 cursor local for
> select T1.stockID,T1.availableQ FROM
> (select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
> reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
> JOIN dbo.tblItems i1
> ON r1.stockID=i1.stockID WHERE i1.itemID=@.itemID) then 1 else 0 end,
> l.distance
> from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
> i.locationID=l.locationID
> AND (@.lokType is null OR l.lokType=@.lokType) LEFT JOIN dbo.tblReservation
s
> r ON i.stockID=r.stockID
> WHERE i.itemID=@.itemID AND (@.pDate is null OR i.itemProductionDate>=@.pDat
e)
> AND
> (@.quality is null OR i.itemQuality>=@.quality)
> GROUP BY i.stockID,i.itemQuantity,l.distance
> having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
> ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
> open cCur1
> fetch next from cCur1 INTO @.stockID,@.availableQ
> --I reserve items until reach the required quantity
> while @.@.fetch_status=0
> begin
> if @.availableQ>=@.requiredQ
> begin
> if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
> UPDATE dbo.tblReservations SET quantity=quantity+@.requiredQ WHERE
> stockID=@.stockID
> else
> INSERT INTO dbo.tblReservations(stockID,quantity)
> SELECT @.stockID,@.requiredQ
> SET @.requiredQ=0
> BREAK
> end
> else
> begin
> SET @.requiredQ=@.requiredQ-@.availableQ
> if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
> UPDATE dbo.tblReservations SET quantity=quantity+@.availableQ WHERE
> stockID=@.stockID
> else
> INSERT INTO dbo.tblReservations(stockID,quantity)
> SELECT @.stockID,@.availableQ
> end
> fetch next from cCur1 INTO @.stockID,@.availableQ
> end
> close cCur1
> deallocate cCur1
> if @.requiredQ>0
> begin
> RAISERROR(60017,11,1)
> break
> end
> fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
> end
> close cCur
> deallocate cCur
> delete from dbo.tblRequiredItems
> if @.@.error=0
> COMMIT TRANSACTION
> else
> ROLLBACK TRANSACTION
> The DDL with sample data:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblItems]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblLocation]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblRequiredItems]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[tblReservations]
> GO
> CREATE TABLE [dbo].[tblItems] (
> [stockID] [int] IDENTITY (1, 1) NOT NULL ,
> [itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
> [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [itemQuantity] [decimal](15, 5) NOT NULL ,
> [itemQuality] [int] NOT NULL ,
> [itemProductionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblLocation] (
> [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [lokType] [int] NULL ,
> [distance] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblRequiredItems] (
> [requireID] [int] IDENTITY (1, 1) NOT NULL ,
> [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [quality] [int] NULL ,
> [quantity] [decimal](15, 5) NOT NULL ,
> [productionDate] [datetime] NULL ,
> [lokType] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblReservations] (
> [reservationID] [int] IDENTITY (1, 1) NOT NULL ,
> [stockID] [int] NULL,
> [quantity] [decimal](15, 5) NOT NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblItems] ADD
> CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED
> (
> [stockID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblLocation] ADD
> CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
> (
> [locationID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblRequiredItems] ADD
> CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY CLUSTERED
> (
> [requireID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblReservations] ADD
> CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
> (
> [reservationID]
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('A001',1,100)
> INSERT INTO [tblLocation]([locationID], [lokType], [distance])
> VALUES('B001',1,200)
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('C001',2,150)
>
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0001','A001',20,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0002','A001',15,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0003','A001',50,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0001','B001',30,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0002','B001',60,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0003','B001',40,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0001','B001',5,1,'200502
02')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0002','B001',10,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0003','B001',30,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0001','C001',20,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0002','C001',5,1,'200502
02')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0003','C001',25,1,'20050
202')
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(1,5)
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(5,10)
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(9,12)
>
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0001',1,40,'20050101',null)
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0002',1,10,'20041212',null)
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0003',null,10,null,2)
>
>|||Once you design actual tables and partially miss the business requirements,
there's not much anyone can do about it. Don't design any tables until you
know exactly what you need.
Good designs look good on paper first. Identify your entities, identify
relationships, and build a use case before you start designing tables.
ML