Can you have your cake and eat it, too?
nope.
But there are some things you can do. I've worked with this EHR software called Nextgen. It has an OLTP database that never deletes/archives any data, so it's also the data warehouse.
Short story: it sucks.
less-short story: You can keep things kinda-not-slow if you cluster every table by some kind of date, so the most recent data is on top.
You also have to pay attention to DUI's, making sure they don't lock tables for too long at a time. I would sometimes resort to cursors, which made the overall duration of the DUI longer, but reduced the time of each individual lock.
I also avoided triggers, using jobs running every 5-15 minutes instead, whenever possible.
Here's the script I used to get the whole database at once:
/*this script will find all the heaps in the database that have the columns you specify, then index them on those columns and write a record to jm_clusterize
It was written for sql server enterprise edition, taking advantage of online indexing, so if you have trouble, get rid of 'with (online = on)
You should run this script after hours. It will *try* to do the index online, but if it can't, it will do the index offline, which will lock the table for a
few seconds/minutes
*/
declare @table varchar(255),
@sqlcreate nvarchar(max),
@sqlDrop nvarchar(max),
@column1 varchar(255),
@column2 varchar(255),
@column3 varchar(255)
/*specify the coulumns you want to cluster on here. You always want to cluster on the create timestamp first, and nextgen is pretty good
at having a create_timestamp on every table, but you will want to cluster some tables on seq_no, or uniq_id, then patient demographics tables will need to
use person_id first. I'm on the fence on clustering on 3 columns - it may be faster, it may be slower.
*/
set @column1 = 'create_timestamp'
set @column2 = 'enc_id'
--set @column3 = 'seq_no'
--drop table jm_clusterize
if not exists (select name from sys.objects where name = 'jm_clusterize')
begin
create table jm_clusterize (table_name varchar(255), column1 varchar(255), column2 varchar(255), column3 varchar(255), create_statement nvarchar(max),
drop_statement nvarchar(max), create_timestamp datetime, modify_timestamp datetime)
end
declare c cursor for
--This is the query that will show you all the heaps that could be clustered on the columns you pick
SELECT TBL.name AS TableName
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX
ON TBL.object_id = IDX.object_id
AND IDX.type = 0 -- = Heap
inner join sys.columns sc1 on tbl.object_id = sc1.object_id
inner join sys.columns sc2 on tbl.object_id = sc2.object_id
--inner join sys.columns sc3 on tbl.object_id = sc3.object_id
INNER JOIN
sys.partitions p ON idx.object_id = p.OBJECT_ID AND idx.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
where sc1.name = @column1
and sc2.name = @column2
--and sc3.name = @column3
group by TBL.name
ORDER BY SUM(a.total_pages) desc, TableName
open c
fetch next from c into @table
while @@fetch_status = 0
begin
set @sqlcreate = 'begin try create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
+ @column2
--+ ',' + @column3
+ ') with (online = on) end try
begin catch create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
+ @column2
--+ ','+ @column3
+ ') end catch'
set @sqlDrop = 'drop index inx_jm_clstr on ' + @table
insert into jm_clusterize (table_name, column1, column2, column3, create_statement, drop_statement, create_timestamp, modify_timestamp)
select @table, @column1, @column2, @column3, @sqlcreate, @sqlDrop, current_timestamp, current_timestamp
--comment out the line below and it won't actually create the index
exec sp_executesql @sqlcreate
fetch next from c into @table
end
close c
deallocate c
select * from jm_clusterize
where column1 = @column1
and column2 = @column2
and column3 = @column3