d2jsp
Log InRegister
d2jsp Forums > Off-Topic > Computers & IT > Programming & Development > Ways To Store Data That Persists Between Subs?
Add Reply New Topic New Poll
Member
Posts: 35,075
Joined: Jul 26 2006
Gold: 125.00
Oct 18 2018 10:31am
I’m learning, and I’m a bit stuck on this concept. I’m using VBA currently, though I’ll be picking up other languages soon (C#, Python, and/or idk yet). The language is inconsequential to my question, this is more conceptual.

Let’s say I’m using SQL as my database for saving long term.

Let’s say I want to store a bunch of objects in global memory, and keep them in memory until the program is closed. Some might have dictionaries in them, some might have arrays or other objects, etc.

I’m confused about how in programming I would go about this. I know it depends, but I need some guidance on some of my options.

So far, these are the solutions I know of:
1) Recall what I want from SQL directly every time I want it.
2) Create a singleton class with a global list or collection stored inside it. Instantiate that global object when the program starts, and then store what I want within that.

any help is much appreciated

This post was edited by Canadian_Man on Oct 18 2018 10:32am
Member
Posts: 12,703
Joined: May 17 2013
Gold: 12,935.00
Oct 18 2018 03:40pm
All you need to do is keep whatever objects you need stored in a list, and then save that list to a file either as JSON, XML, Binary or just storing the values directly in a database before your program closes.

You don't need to create a class with a list you access. Where to put the list depends on what you want to do, and whether using a list is even the right choice also depends on your specific use case.

I would suggest going the route of using an SQL server or just using SQlite as that's as simple as it gets, and I doubt you _need_ in-memory storage of the objects at all times.

This post was edited by Klexmoo on Oct 18 2018 03:42pm
Member
Posts: 1,039
Joined: Jul 8 2008
Gold: 1,939.50
Oct 18 2018 04:53pm
There are a lot of things to consider. Mainly: What are you retrieving from the database and how much memory is it going to take?
  • If it is minimal configuration data I'd just read it once and hold it in memory until your application closes.
  • If the data is changing and we want up to date data then that approach will not work and you may have to read from SQL every time.
  • If reading from the DB is expensive and you're okay with having somewhat stale data you can set up some caching or have a flag and only pull in data when it has changed.


This post was edited by waraholic on Oct 18 2018 04:54pm
Member
Posts: 35,075
Joined: Jul 26 2006
Gold: 125.00
Oct 24 2018 01:00pm
Thanks I’ve been learning a lot of what you guys mentioned.

I’m still in the weeds with how to proceed, so I’m taking a trial and error approach.

To be more specific (because I want to optimize speed and system resources):

I’m essentially collecting lists (ranges) of data in spreadsheet styles, reading the data and comparing things to other things, creating temporary new ranges or lists or dictionaries, etc.

The type of data is often 2 to 10 columns (categories), by any number of rows (line items such as accounts or transactions). Each sheet has its own specific data such as date received, data type (ie trial balance), date range the data represents, etc.

Relationships between sheets or data within various sheets are also things I need to store and recall, not just the flat grid of data itself as one might see it on a spreadsheet.

Where speed will be required is certain larger loop operations. I’m working on optimizing the logic of operations to minimize unnecessary extra code, but at the core of things I need to make sure I’m keeping info in memory in an ideal state. In a lot of cases I need to check values and relationships between multiple datasets and find matches, differences, and then more complex stuff in from there... there’s just too much to cover regarding how data needs to be available. Simpler Example: reading a cell from an excel sheet, vs reading a cell from a range stored in a global list, vs reading a cell from SQL. (If not a cell, then a class object, or other things, and lots of them).

This post was edited by Canadian_Man on Oct 24 2018 01:01pm
Member
Posts: 12,703
Joined: May 17 2013
Gold: 12,935.00
Oct 24 2018 02:22pm
Quote
In a lot of cases I need to check values and relationships between multiple datasets and find matches, differences, and then more complex stuff in from there


Use SQL. If you have different datasets you just need to get whatever data you need from an SQL server or possibly even SQLite and work on that.
You're fooling yourself if you think you need to keep all data in memory at once to speed your operation. It's a waste to keep everything in memory unless you have literally infinite amounts (which I doubt).

This post was edited by Klexmoo on Oct 24 2018 02:23pm
Banned
Posts: 1,810
Joined: Jan 18 2009
Gold: 0.00
Warn: 20%
Nov 15 2018 01:34am

Quote (Canadian_Man @ Oct 18 2018 09:31am)
I’m learning, and I’m a bit stuck on this concept. I’m using VBA currently, though I’ll be picking up other languages soon (C#, Python, and/or idk yet). The language is inconsequential to my question, this is more conceptual.

Let’s say I’m using SQL as my database for saving long term.

Let’s say I want to store a bunch of objects in global memory, and keep them in memory until the program is closed. Some might have dictionaries in them, some might have arrays or other objects, etc.

I’m confused about how in programming I would go about this. I know it depends, but I need some guidance on some of my options.

So far, these are the solutions I know of:
1) Recall what I want from SQL directly every time I want it.
2) Create a singleton class with a global list or collection stored inside it. Instantiate that global object when the program starts, and then store what I want within that.

any help is much appreciated


I'd use SQLite not sure what "I'm using SQL as my database" mysql? sqlite3? postgresql? mariadb?

Quote (Klexmoo @ Oct 18 2018 02:40pm)
All you need to do is keep whatever objects you need stored in a list, and then save that list to a file either as JSON, XML, Binary or just storing the values directly in a database before your program closes.

You don't need to create a class with a list you access. Where to put the list depends on what you want to do, and whether using a list is even the right choice also depends on your specific use case.

I would suggest going the route of using an SQL server or just using SQlite as that's as simple as it gets, and I doubt you _need_ in-memory storage of the objects at all times.


Yea he can actually use SQLite's :memory: database which is a database in the memory. It's very easy to use, will not persist through reboot. you can dump it to file or just use an on file db.
Go Back To Programming & Development Topic List
Add Reply New Topic New Poll