I would like to construct queries against each of the individual digital channels or combinations of them.
M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.
The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.
In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.
hi,
SQL Server can pad up to 8 bit columns together to save space, but this is another concern in your problem..
I'd actually go for a more normalized model.. you can break the 200 bit columns in a separated entity referincing the same transaction... this not only is more "elegant" and correct, but solves problem where you have to add/modify a device..
something like
SET NOCOUNT ON;USE tempdb;
GO
CREATE TABLE dbo.Devices (
Id int NOT NULL PRIMARY KEY,
Description varchar(10) NOT NULL
);
CREATE TABLE dbo.DeviceTran (
Id int NOT NULL IDENTITY PRIMARY KEY , -- for sake of simplicity
otherData varchar(10) NULL,
TimeRecorded datetime DEFAULT GETDATE()
);
CREATE TABLE dbo.DeviceTranOutput (
Id int NOT NULL IDENTITY PRIMARY KEY , -- for sake of simplicity
IdTran int NOT NULL
CONSTRAINT fk_DeviceTran_DeviceTranOutput
FOREIGN KEY
REFERENCES dbo.DeviceTran (Id),
IdDevice int NOT NULL
CONSTRAINT fk_Devices_DeviceTranOutput
FOREIGN KEY
REFERENCES dbo.Devices (Id),
TValue bit NOT NULL DEFAULT 0
);
GO
PRINT 'available devices';
INSERT INTO dbo.Devices VALUES ( 1 , 'PDA' );
INSERT INTO dbo.Devices VALUES ( 2 , 'PBAX' );
INSERT INTO dbo.Devices VALUES ( 3 , 'PC' );
INSERT INTO dbo.Devices VALUES ( 4 , 'xxx' );
SELECT * FROM dbo.Devices;
PRINT '--';
DECLARE @.i int, @.y int, @.id int;
SET @.i = 1
WHILE @.i < 10 BEGIN
INSERT INTO dbo.DeviceTran VALUES ( 'Data ' + CONVERT(varchar, @.i), DEFAULT);
SELECT @.id = SCOPE_IDENTITY();
SET @.y = 1;
WHILE @.y < 5 BEGIN
INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.y , (@.i + @.y) % 2);
SET @.y = @.y +1;
END
SET @.i = @.i +1
END;
GO
PRINT 'Transaction Report';
SELECT t.Id, t.otherData, t.TimeRecorded, d.Description, o.TValue
FROM dbo.DeviceTranOutput o
JOIN dbo.Devices d
ON d.Id = o.IdDevice
JOIN dbo.DeviceTran t
ON t.Id = o.IdTran;
GO
DROP TABLE dbo.DeviceTranOutput, dbo.DeviceTran, dbo.Devices;
resulting in something likeTransaction Report
Id otherData TimeRecorded Description TValue
-- - -- --
1 Data 1 2006-07-17 18:23:01.717 PDA 0
1 Data 1 2006-07-17 18:23:01.717 PBAX 1
1 Data 1 2006-07-17 18:23:01.717 PC 0
1 Data 1 2006-07-17 18:23:01.717 xxx 1
2 Data 2 2006-07-17 18:23:01.717 PDA 1
2 Data 2 2006-07-17 18:23:01.717 PBAX 0
2 Data 2 2006-07-17 18:23:01.717 PC 1
2 Data 2 2006-07-17 18:23:01.717 xxx 0
3 Data 3 2006-07-17 18:23:01.717 PDA 0
3 Data 3 2006-07-17 18:23:01.717 PBAX 1
-- result abrdiged..
storage will be better used, you have some penality in both reporting and inserting, as JOIN operations are involved, but you get far better design you can expand/modify with no worries about modifications in the device pattern...
regards
|||Thanks for that.I'll probably adopt your suggestion.
In order to save space, I am considering only storing the ocasions when the digital values change (rather than every time step).
Using your method, I don't need to store every channel every time so that is another reason in favour of it.
It does lead to another question though..
Taking your example: say a DeviceTran record is generate every timestep for the benefit of some analogue channel 'X' that is continuously changing.
Take a digital channel called 'Y' that is only recorded every time it changes.
Then, if I do an outer join to gather all the data at every time step, I might get something like this,,,
Id TimeRecorded X Y
-- -- --
1 2006-07-17 18:23:01.000 1.0 false
2 2006-07-17 18:23:02.000 1.1 null
3 2006-07-17 18:23:03.000 1.2 null
4 2006-07-17 18:23:04.000 1.1 true
5 2006-07-17 18:23:05.000 1.4 null
6 2006-07-17 18:23:06.000 1.5 null
7 2006-07-17 18:23:07.000 1.2 false
8 2006-07-17 18:23:08.000 0.9 null
9 2006-07-17 18:23:09.000 0.8 nullFor a particular value of DATETIME I would like to get the most recent
record for Y.
A straight outer join would just show null values for those not present.
Is there a clever way of joining but using the 'last' value based on
datetime or id.
Regards
|||
hi,
I'm not sure I fully understand your requirements..
I think you mean you just avoid to insert repeted values as following..
SET NOCOUNT ON;SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.Devices (
Id int NOT NULL PRIMARY KEY,
Description varchar(10) NOT NULL
);
CREATE TABLE dbo.DeviceTran (
Id int NOT NULL IDENTITY PRIMARY KEY ,
otherData varchar(10) NULL,
TimeRecorded datetime NOT NULL DEFAULT GETDATE()
);
CREATE TABLE dbo.DeviceTranOutput (
Id int NOT NULL IDENTITY PRIMARY KEY ,
IdTran int NOT NULL
CONSTRAINT fk_DeviceTran_DeviceTranOutput
FOREIGN KEY
REFERENCES dbo.DeviceTran (Id),
IdDevice int NOT NULL
CONSTRAINT fk_Devices_DeviceTranOutput
FOREIGN KEY
REFERENCES dbo.Devices (Id),
TValue bit NULL
);
GO
DECLARE @.t datetime, @.id int, @.dev int;
SELECT @.dev = 1, @.t = GETDATE();
INSERT INTO dbo.Devices VALUES ( @.dev , 'PBAX' );
INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);
SELECT @.id = SCOPE_IDENTITY();
INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);
WAITFOR DELAY '00:00:01'
SELECT @.t = GETDATE();
INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);
SELECT @.id = SCOPE_IDENTITY();
--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);
WAITFOR DELAY '00:00:01'
SELECT @.t = GETDATE();
INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);
SELECT @.id = SCOPE_IDENTITY();
--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);
WAITFOR DELAY '00:00:01'
SELECT @.t = GETDATE();
INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);
SELECT @.id = SCOPE_IDENTITY();
INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 1);
WAITFOR DELAY '00:00:01'
SELECT @.t = GETDATE();
INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);
SELECT @.id = SCOPE_IDENTITY();
--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 1);
PRINT 'Transaction Report';
PRINT '';
PRINT 'IdDevice is NULL, you can''t directly reference';
PRINT 'the devices, you have to resort on ''specific'' queries';
SELECT t.Id, t.otherData, t.TimeRecorded, d.Description,
(SELECT TOP 1 o2.TValue
FROM dbo.DeviceTranOutput o2
WHERE o2.IdDevice = d.Id
AND o2.IdTran <= t.Id
ORDER BY o2.Id DESC) AS TValue
FROM dbo.DeviceTranOutput o
JOIN dbo.Devices d ON d.Id = o.IdDevice
RIGHT JOIN dbo.DeviceTran t ON t.Id = o.IdTran
ORDER BY t.TimeRecorded;
GO
PRINT 'as passing a [@.DeviceId] as a parameter';
DECLARE @.DeviceId int;
DECLARE @.DeviceDescription varchar(10);
SELECT @.DeviceId = 1;
SELECT @.DeviceDescription = Description
FROM dbo.Devices
WHERE Id = @.DeviceId;
SELECT t.Id, t.otherData, t.TimeRecorded,
@.DeviceDescription AS [Description],
(SELECT TOP 1 o2.TValue
FROM dbo.DeviceTranOutput o2
WHERE o2.IdDevice = @.DeviceId
AND o2.IdTran <= t.Id
ORDER BY o2.Id DESC) AS TValue
FROM dbo.DeviceTranOutput o
JOIN dbo.Devices d ON d.Id = o.IdDevice
RIGHT JOIN dbo.DeviceTran t ON t.Id = o.IdTran;
GO
DROP TABLE dbo.DeviceTranOutput, dbo.DeviceTran, dbo.Devices;
--<-
Transaction Report
IdDevice is NULL, you can't directly reference
the devices, you have to resort on 'specific' queries
Id otherData TimeRecorded Description TValue
-- - -- --
1 Data Pbax 2006-07-18 22:56:51.810 PBAX 0
2 Data Pbax 2006-07-18 22:56:52.827 NULL NULL
3 Data Pbax 2006-07-18 22:56:53.827 NULL NULL
4 Data Pbax 2006-07-18 22:56:54.827 PBAX 1
5 Data Pbax 2006-07-18 22:56:55.827 NULL NULL
as passing a [@.DeviceId] as a parameter
Id otherData TimeRecorded Description TValue
-- - -- --
1 Data Pbax 2006-07-18 22:56:51.810 PBAX 0
2 Data Pbax 2006-07-18 22:56:52.827 PBAX 0
3 Data Pbax 2006-07-18 22:56:53.827 PBAX 0
4 Data Pbax 2006-07-18 22:56:54.827 PBAX 1
5 Data Pbax 2006-07-18 22:56:55.827 PBAX 1
but my idea is you'll go into troubles both at insert time, as you have to check if the current value is the same as the last one, and later at query time, as you miss some references..
you can scan for an older value in dbo.DeviceTranOutput of a previous transaction, but you miss the IdDevice value... if you query for a specified device then it's allright, as you pass the IdDevice as a parameter, which becames a constant, but a general purpose query to list all transactions (orderd by TimeRecorded and IdDevice) becames heavy, for every row ...
at insert time this is a heavy load as well as instead of just inserting you have to check, and this can cost a lot in real time apps..
considering you're collecting data in quiet real time, I'd go for the quicker (is it english?
) way to pump data in, without trigger to filter out repeated values...
more, transactionally, it breaks a rule of atomicity of a row, as it depends on the values of previous rows... it makes all the design trickier, and of corse coding as well.. my $0.02..
regards
|||Thanks for that.You have understood my requirements exactly.
I was hoping to save disk space by not storing all the values for a timestep that haven't changed. But you are right about breaking the rule of atomicity, because the value of a particular column now depends on its 'last' non-null value. I hadn't thought of it like that.
Whatever the gains I make in disk space I have to seriously consider the penalty in SQL complexity. Trying to extract the 'current' value for just one or maybe a handful of channels can be done (as you have shown above) but a generic query to return values for all channels might be very complex/slow.
I'll go away and think some more on it.
Thanks once again.
Regards
No comments:
Post a Comment