February 27, 2012 at 10:59 am
Posted - 02/24/2012 : 05:30:42
I have 2 tables Loads and Balance
CREATE TABLE [dbo].[Loads](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CardProduct] [nvarchar](50)NOT NULL,
[LoadAmt] [decimal](18, 2) NULL,
[LoadDate] [datetime] NULL,
[LoadType] [nvarchar](50) NULL
)
CREATE TABLE [dbo].[Balance](
[CardProduct] [nvarchar](50) NOT NULL,
[AvlBalance] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO
And it has the following data.
-- Loads
INSERT INTO [loads] VALUES('IP01',10.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',20.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',30.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',40.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',50.00,'2012-02-24','Load')
--Balance
Insert INTO [Balance] VALUES ('IP01', 800)
The data in loads table is inserted almost every second. approximately 30 rows per second.
1) As soon as a record is inserted in Load table it needs to reduce the LoadAmt from the AvlBalance in Balance table based on card product.
So AvlBalance was 800 for IP01
so after the rows in loads table the AvlBalance should become 650.
I am worried about the processing time since record in loads table is inserted in milliseconds frequency.
Kindly let me know the procedure to achieve this.
February 27, 2012 at 11:27 am
How quickly should the data in Balance table be updated? is it instantaneously ?
February 27, 2012 at 11:34 am
If it has to be done for each load, then you either need to add that to the script/proc that puts data in that table, or you need to make it into a trigger. In the script/proc is better.
Alternatively, don't persist that value. Just calculate it when you need it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 28, 2012 at 3:00 am
Thanks for the reply. Balance needs to be updated every minute or if possible after every record inserts.
February 28, 2012 at 4:03 am
Looking into your setup, balance should be updated within the same transaction as insert into Loads.
The one way to achieve it SQLServer is to have a trigger on Loads table:
-- put some relevant comments here
CREATE TRIGGER dbo.t_Load_BalanceUpdate ON dbo.Loads AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE b
SET AvlBalance = AvlBalance - i.LoadAmt
FROM dbo.Balance AS b
JOIN INSERTED AS i
ON i.CardProduct = b.CardProduct
END
GO
I hope you have referential integrity between your Balance and Loads table, so you cannot insert records into Loads until you have relevant Balance record for the CardProduct. If not, your trigger should deal with the fact of not having initial balance.
February 28, 2012 at 10:59 am
Thank you so much for the reply. That worked.
February 28, 2012 at 12:13 pm
You are welcome!
I would like to add some more for your consideration. Usually the solutions like yours would also include some kind of regular (most likely scheduled) reconciliation process, which will reconcile transactions with final balances. This process may just produce warning for any discrepancy problem as well as auto-fix some of them.
February 28, 2012 at 6:14 pm
Could you refactor the INSERT statements into the following UPDATE on the Balance table?
UPDATE b SET [AvlBalance] = [AvlBalance] - [LoadAmt]
OUTPUT Inserted.[CardProduct], x.[LoadAmt], x.[LoadDate], x.[LoadType]
INTO [dbo].[Loads]
FROM [dbo].[Balance] b
INNER JOIN (
SELECT 'IP01' AS [ID],10.00 AS [LoadAmt],'2012-02-24' AS [LoadDate],'Load' AS [LoadType]
) x ON x.[ID] = b.[CardProduct]
This would kill two birds with one stone and avoid the trigger, although it may not work if there's already a trigger on the Loads table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 28, 2012 at 10:01 pm
CELKO (2/28/2012)
Get a book on RDBMS and read the parts about keys and normalization. You have no key and more NULL-able columns than an entire schema. A since you do not know that rows and records are totally different, your mindset has to materialize a computation in a separate “fake punch card” when a VIEW would do the job. Also, why do you allow a fifty character load_type encoding in Chinese Unicode? If you are sloppy with DDL that you grab an absurd default like that, your database will fill with garbage data.This is a model of deposits and withdrawals of something call a card_product. But you have made another design error called attribute splitting. Would you split a Personnel table on sex and have Male_Personnel and Female_Personnel? I am just more used to seeing the term “pulling inventory' insread of loading it
CREATE TABLE Card_Product_Inventory
(card_product CHAR(5) NOT NULL,
inventory_timestamp DATETIME2(3) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (card_product, inventory_timestamp),
product_amt DECIMAL(18, 2) NOT NULL,
inventory_action_type CHAR(5) NOT NULL
CHECK (inventory_action_type IN ('Stock', 'Pull'))
);
INSERT INTO Product_Inventory_Tickets
VALUES ('IP01', '2012-02-23 00:00:00', 800.00, 'Stock'))
('IP01', '2012-02-24 00:00:00', 20.00, 'Pull'),
('IP01', '2012-02-24 00:0030', 30.00, 'Pull'),
('IP01', '2012-02-24 00:01:00', 40.00,'Pull'),
('IP01', '2012-02-24 00:01:30', 50.00, 'Pull');
Now think of rows which can be virtual.
CREATE VIEW Product_Inventory_Balances
AS
SELECT card_product, inventory_timestamp,
SUM (CASE inventory_action_type
WHEN 'Pull' THEN –product_amt ELSE product_amt END)
OVER (PARTITION BY card_product
ORDER BY inventory_timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS card_product_balance
FROM Product_Inventory_Tickets;
This view is always correct and current. Since it will probably be computed in main storage, it will be MUCH faster than the disk access and writing you were doing.
One problem with your view, it won't work with any version of MS SQL Server prior to SQL Server 2012. So I guess the view isn't always correct.
If you are going to give possible solutions, you should make sure that they work in the version of MS SQL Server that is being used.
February 28, 2012 at 10:22 pm
CELKO (2/28/2012)
Get a book on RDBMS and read the parts about keys and normalization. You have no key and more NULL-able columns than an entire schema. A since you do not know that rows and records are totally different, your mindset has to materialize a computation in a separate “fake punch card” when a VIEW would do the job. Also, why do you allow a fifty character load_type encoding in Chinese Unicode? If you are sloppy with DDL that you grab an absurd default like that, your database will fill with garbage data.
Heh... Jeez, Joe... the OP is probably young enough to not even know what a "punch card" is. And you're starting to fall back into your old ways again. Go back to being kind, courteous, and helpful. I was really starting to like that version of Celko. 😉
--Jeff Moden
February 29, 2012 at 2:40 am
dwain.c (2/28/2012)
Could you refactor the INSERT statements into the following UPDATE on the Balance table?
UPDATE b SET [AvlBalance] = [AvlBalance] - [LoadAmt]OUTPUT Inserted.[CardProduct], x.[LoadAmt], x.[LoadDate], x.[LoadType]
INTO [dbo].[Loads]
FROM [dbo].[Balance] b
INNER JOIN (
SELECT 'IP01' AS [ID],10.00 AS [LoadAmt],'2012-02-24' AS [LoadDate],'Load' AS [LoadType]
) x ON x.[ID] = b.[CardProduct]
This would kill two birds with one stone and avoid the trigger, although it may not work if there's already a trigger on the Loads table.
What is this? Where your "load" data comes from? Looks like from some staging tables or what? If so, what will trigger the updated of Balance?
February 29, 2012 at 5:25 am
I've basically turned the statement around so the main UPDATE is to the Balance table and the OUTPUT/INTO statement INSERT the transaction into the loads table.
Ran for me as expected when I tested it.
I confess that my original thought was the trigger approach as that's pretty traditional. Once in a while though, it pays to think out-of-the-box so you have alternatives to play with.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply