May 2, 2003 at 7:16 am
How about taking a step back to the design and including a sequence number which is unique to each table. When you insert the data, look up the sequence number of the last record and increment it by one. Your table would then look like:
TblSeq Date TableName NoOfRecords
1 23-04-2003 TableA 10
1 23-04-2003 TableB 50
2 24-04-2003 TableA 15
2 24-04-2003 TableB 60
and you could just select something like
select a.Date, a.TableName, a.NoOfRecords,
a.NoOfRecords - b.NoOfRecords Delta
from mytable a, mytable b
where a.TableName = b.TableName
and a.TblSeq -1 = b.TblSeq
This should be fairly quick. You will not return the first record for each table (which has no delta) and you will take a performance hit in inserting the records.
The other obvious solution is to use a cursor. For 21000 records, it should not be slow - although not as fast as the solution above. If you would like an example, I can write one for you.
May 2, 2003 at 8:08 am
Not to beat a dead horse but it is an interesting problem:-)
How about something like:
for table junk -- create table junk (a char(15), b datetime, c int)
create table #tom (z int identity, a char(15), b datetime, c int)
insert into #tom (a, b, c) select a, b, c from junk order by a,b
create index a on #tom (a,z)
select t.a, t.b, t.c, t.c - t1.c delta
from #tom t, #tom t1
where t1.a = t.a
and t1.z = t.z-1
On my desktop, for 98,000 records, it took 8 seconds. Not blazing but no structure changes to your tables.
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply