When to read data tables?
-
I’m writing an exam management program for a high school, using a remote SQL Server database. In the project, I access the database quite often, to get lists of student names, subjects, sets etc. The student (about 2200 records), Teacher (about 50 records) and subject (20 records) tables contents change very infrequently, I was wondering: 1. Would it be more efficient to read these tables at program start, and retain the information as lists in memory, or should I read each table whenever the user needs it (possibly five times per session)? 2. Is SQL Server 2012 overkill for this project? I’ve thought about using SQLite in a local database, but for the life of me, I cannot get it to work. (This is the problem of working by myself, I’ve no-one to bounce ideas off). 3. Do any of you boffins have any better suggestions as to how to go about this? I’m putting this in the C# forum, as that’s the language the database will be accessed from, but if you think that it should be in the database forum, please feel free to move it. (Or tell me and I’ll re-write it there).
-
I’m writing an exam management program for a high school, using a remote SQL Server database. In the project, I access the database quite often, to get lists of student names, subjects, sets etc. The student (about 2200 records), Teacher (about 50 records) and subject (20 records) tables contents change very infrequently, I was wondering: 1. Would it be more efficient to read these tables at program start, and retain the information as lists in memory, or should I read each table whenever the user needs it (possibly five times per session)? 2. Is SQL Server 2012 overkill for this project? I’ve thought about using SQLite in a local database, but for the life of me, I cannot get it to work. (This is the problem of working by myself, I’ve no-one to bounce ideas off). 3. Do any of you boffins have any better suggestions as to how to go about this? I’m putting this in the C# forum, as that’s the language the database will be accessed from, but if you think that it should be in the database forum, please feel free to move it. (Or tell me and I’ll re-write it there).
It really all depends on what you are doing! :laugh: There is no "this is best" solution - or we would all just use that and the other solutions would fall by the wayside... The SQL server SQLite decision is a case in point: Are you expecting only one person to use the information, or multiple people? If it's one person, then yes, SQL Server can be overkill. But if it's two people who need access to the DB, then a server based solution (SQL Server or MySql) is absolutely a good idea, and SQLite is a very poor decision because it will give you enormous hassles. But...Yesterday I spent ten minutes swapping one of my utility apps from SQLCE to SQL Server despite being the only person who uses it, simply because I was fed up with the low speed on start up / shut down - so it's isn't always as black and white as it seems... :laugh: The tables are the same: it depends what you are doing! I have systems which do it both ways: but generally if two people can change the data, then local caching becomes a problem, unless you use some SQL triggering to force an update on your application - which is not a trivial job! So no, we can't tell you which way to go: just keep you data layer separate from your business and presentation layers so if you did chose the wrong solution, you can "undo" it as easily as possible!
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952) Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
-
It really all depends on what you are doing! :laugh: There is no "this is best" solution - or we would all just use that and the other solutions would fall by the wayside... The SQL server SQLite decision is a case in point: Are you expecting only one person to use the information, or multiple people? If it's one person, then yes, SQL Server can be overkill. But if it's two people who need access to the DB, then a server based solution (SQL Server or MySql) is absolutely a good idea, and SQLite is a very poor decision because it will give you enormous hassles. But...Yesterday I spent ten minutes swapping one of my utility apps from SQLCE to SQL Server despite being the only person who uses it, simply because I was fed up with the low speed on start up / shut down - so it's isn't always as black and white as it seems... :laugh: The tables are the same: it depends what you are doing! I have systems which do it both ways: but generally if two people can change the data, then local caching becomes a problem, unless you use some SQL triggering to force an update on your application - which is not a trivial job! So no, we can't tell you which way to go: just keep you data layer separate from your business and presentation layers so if you did chose the wrong solution, you can "undo" it as easily as possible!
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952) Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
Thanks Griff, you've put into words exactly what I wanted to hear. there's about 50 teachers, and it's remotely possible that they will all be entering results at the same time. Taking your advice, I've decided to use SQL server. The project will be on separate laptops, each with 8Gb of memory (don't you just love school standardisation?), so downloading all the data at run time won't be a problem, and I will have a trigger to flag updates. Not quite figured out how to do that yet, but I'm sure it can be done. I'm going to use Entity Framework, as my data layer, with the business and presentation layers separated as well. In fact the only problem I can see is convincing the management team that the data is actually secure, even though it is on a remote server.
-
Thanks Griff, you've put into words exactly what I wanted to hear. there's about 50 teachers, and it's remotely possible that they will all be entering results at the same time. Taking your advice, I've decided to use SQL server. The project will be on separate laptops, each with 8Gb of memory (don't you just love school standardisation?), so downloading all the data at run time won't be a problem, and I will have a trigger to flag updates. Not quite figured out how to do that yet, but I'm sure it can be done. I'm going to use Entity Framework, as my data layer, with the business and presentation layers separated as well. In fact the only problem I can see is convincing the management team that the data is actually secure, even though it is on a remote server.
You're welcome!
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952) Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
-
Thanks Griff, you've put into words exactly what I wanted to hear. there's about 50 teachers, and it's remotely possible that they will all be entering results at the same time. Taking your advice, I've decided to use SQL server. The project will be on separate laptops, each with 8Gb of memory (don't you just love school standardisation?), so downloading all the data at run time won't be a problem, and I will have a trigger to flag updates. Not quite figured out how to do that yet, but I'm sure it can be done. I'm going to use Entity Framework, as my data layer, with the business and presentation layers separated as well. In fact the only problem I can see is convincing the management team that the data is actually secure, even though it is on a remote server.
Just a thought: You could keep a table in the database with revision numbers per table (or the whole database). Cash the data and refresh when the revision number is higher then local (small query that runs every minute). When you update, delete or insert data increment the revision number. You should use stored procedures in sql server, queries are very slow.
-
Just a thought: You could keep a table in the database with revision numbers per table (or the whole database). Cash the data and refresh when the revision number is higher then local (small query that runs every minute). When you update, delete or insert data increment the revision number. You should use stored procedures in sql server, queries are very slow.
-
Sprocs are compiled and then stored. queries have to be compiled every time.
-
Sprocs are compiled and then stored. queries have to be compiled every time.
-
Stored Procedures DO NOT increase performance[^]
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
oow...i see. Back in the old days when I learned sql server (4.1 or so) they where faster. sorry!
