March 24, 2014 at 9:02 am
You mention that a lot of customers will use SQL Express with your application.
I think the capabilities of SQL Express should form part of the design limits on your application. One of these aspects is that SQL Express can use a maximum of 1GB memory and 2CPU cores, and this may well impact the decision regarding a single table or multiple tables.
My gut feel is that multiple tables are a better choice in this environment. This is because:
a) The use of SQL Express implies the use of low-end hardware. Most customers will not have the IO capability to use the capabilities of the paid-for editions of SQL Server.
b) The memory needs for a given table in a multi-table setup are likely to be less than for a single table.
c) You want to make it easy for customers to delete a simulation they do not need. Separate tables are quick to drop, row deletion is not. If you make deletion slow then customers will not do it, then they will hit the 10GB DB size limit in SQL Express.
d) Customers are unlikely to run much in the way of index maintenance. If you delete rows from a table then index maintenance is needed to reclaim space. Dropping tables needs no maintenance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 28, 2014 at 7:56 am
Ed,
Thanks for your reply, I think you're correct on all points.
From everyone's feedback so far it looks like what we have at the moment could well be the best solution, but we definitely need to look at re-factoring some of our hideous dynamic SQL.
Regards,
Darren
Viewing 2 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply