C#•3y ago

Load local db file to memory

How do I load local db file to memory?

68 Replies

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

yeah

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I can do that but this happen when I changed my code to use in memory db

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseSqlite("data source=Data\\master.db");
            }
        }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseSqlite("data source=Data\\master.db");
            }
        }

originally this is fine Changing it back now is fine as well

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

yes So that is why I am seeking help of loading local db to memory properly

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I did a Backup

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

But I would like to load the entire db to memory and use it from there

jcotton42•3y ago

What are you using in-memory for?

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

That was basically what I did But it does not seem to work

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            SQLiteConnection source = new SQLiteConnection("data source=Data\\master.db");
            source.Open();
            SQLiteConnection destination = new SQLiteConnection("data source=:memory:");
            destination.Open();
            source.BackupDatabase(destination, "main", "main", -1, null, 0);
            source.Close();
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseInMemoryDatabase("data source=:memory:");
            };
        }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            SQLiteConnection source = new SQLiteConnection("data source=Data\\master.db");
            source.Open();
            SQLiteConnection destination = new SQLiteConnection("data source=:memory:");
            destination.Open();
            source.BackupDatabase(destination, "main", "main", -1, null, 0);
            source.Close();
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseInMemoryDatabase("data source=:memory:");
            };
        }

I backedup the data to memory

jcotton42•3y ago

My understanding is in memory doesn't model a real database very well I would stay away from it

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I will only be reading 0 writing at all so I would like to see if I can load it from memory to increase performance

jcotton42•3y ago

What measuring have you done to show that that would actually boost perf?

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I asked this question few days ago I was suggested to use a profiler the result was 1 a lot of linq query with the dbcontext 2 a lot of UI redraw 2 was solved 1 I did a predictionary but I still want to see if I can get my db to be loaded to memory

jcotton42•3y ago

That doesn't answer the question though Just use a separate SQLite file

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

Separate sqlite file? So you are implying EF core is not possible to load a file to memory?

jcotton42•3y ago

I'm saying you shouldn't use in-memory, and should use SQLite like you already are

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I want to improve the performance

jcotton42•3y ago

And what makes you think this will have a notable impact? Sure you spend a lot of time in the db But in-memory isn't magic

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

why not? file IO can't compare to something is in memory should be common truth

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

20~30mb

Unknown User•3y ago

Message Not Public

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I installed this nuget already; hence, I was able to use UseInMemoryDatabase method

D.Mentia•3y ago

An in-memory database defeats the purpose of a database and doesn't make sense. If you want it in-memory, just make a List<yourDbEntity> or whatever. Query the db, retrieve every row, store them in your favorite collection, hooray you're in-memory

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

But they made this feature

Patrick•3y ago

highly disagree an in memory database is more than ok - and it's absolutely different to just a list declared by yourself

D.Mentia•3y ago

idk I tend to trust MS on this one

The In-Memory provider was not designed for use outside of testing environments and should never be used as such.

Which combined with the above quote also from MS, means it should just never be used

Patrick•3y ago

not at all it's not for testing substitute of "relational databases" and you seem to think my comment was specifically about EF Core In Memory, which it isn't. There are in memory database engines that work more efficiently than just a "list"

D.Mentia•3y ago

True, I thought you meant EF. What is the benefit of an in-memory db, then? And more efficiently than a list, sure... but a hashset or dictionary? I was always under the impression the only real benefit of a db is when you have so much data that you can't fit it into memory

Unknown User•3y ago

Message Not Public

jcotton42•3y ago

Databases offer other features like ACID on disk @D.Mentia

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

So how do you backup a local db to memory

reacher•3y ago

What exactly are you trying to accomplish here Do you want everything to be in memory but also persisted to disk "backup a local db to memory" is odd terminology that doesn't make sense And doesn't say anything about what the end goal is

MODiX•3y ago

KKSK＠🌸🎵🌸🎵🌸#5391

I want to improve the performance

Quoted by

<@!446416451230760990> from #Load local db file to memory (click here)

React with ❌ to remove this embed.

MODiX•3y ago

KKSK＠🌸🎵🌸🎵🌸#5391

file IO can't compare to something is in memory

Quoted by

<@!446416451230760990> from #Load local db file to memory (click here)

React with ❌ to remove this embed.

jcotton42•3y ago

¯\_(ツ)_/¯ I doubt it'll make much difference personally

reacher•3y ago

How about you write more efficient code The DB isn't the problem It's how you use it and the rest of the code that's the problem

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

As mentioned in the title; load a local db file to memory and use it (readonly needed) Never asked for anything else

reacher•3y ago

Readonly is not in the title But if that's all you need, just load the data from the DB and cache it

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

That was what I did But that was not my original question I was asking specifically about this

reacher•3y ago

You don't, that's not how it works

jcotton42•3y ago

What would "load to memory" even mean? Just stuffing the SQLite db into RAM? What would that accomplish? And don't say "performance" without evidence that IO is your bottleneck Because it's probably not

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I am not sure what is the confusion here and why am I keep getting asked with same questions But all I ever need since the beginning was load a local Db to memory and establish the connection

jcotton42•3y ago

Because what you're trying to do makes no sense

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

That does not matter

reacher•3y ago

Making sense doesn't matter?

jcotton42•3y ago

$xy

MODiX•3y ago

http://xyproblem.info/

The XY Problem

Asking about your attempted solution rather than your actual problem

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

Of course not VFS have long been available; UseInMemoryDatabase was made with a nugget specifically for it I understand how you hate about doing it as memory But a feature is created with a purpose So people can use it

reacher•3y ago

You're asking a completely different question but you don't realize it

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

I am only asking to open a Db in memory and use it

reacher•3y ago

And that's not how it works That's why there's no answer

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

There is Because I used it with external dll with vfs handle

reacher•3y ago

OK so what is the problem then if you have it solved

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

Is fine if you do not know it Because is an external dll; I want to know how to accomplish this without using external native library

reacher•3y ago

Mount a ramdisk and copy your DB there problem solved

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

To be honest if you don’t know the answer and is just opposing it I am not sure why you even bother for this long

reacher•3y ago

Because that is the only option But you don't want to listen Multiple people are telling you the same thing

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

what answer? You start by not providing anything and say that is not how you do it Then you shifted to there is no answer

reacher•3y ago

Most databases do not natively support loading the DB file into memory and working from there

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

Now you are saying load it to a RAM disk

reacher•3y ago

So the only way you can make that work is to put it in a ramdisk which is still just fooling it by thinking it's on a real file system But that still won't solve your speed problem because that's not the actual issue You can blame the DB all you want but it's not the DB's fault And if you put it in a ramdisk you have to be really sure you are only reading from it because it won't be persisted back to the actual disk So then what's the point, if you want a read-only in memory cache of the data just read it from the DB and keep the data in process memory instead of copying the DB file to RAM and using it as a DB

KKSK＠🌸🎵🌸🎵🌸OP•3y ago

Okay here is the solution for anyone who might need it in the future

public static class SqliteInMemoryDatabase
    {
        private static object key = new();
        static SqliteInMemoryDatabase()
        {
            lock (key)
            {
                SqliteConnection source = new SqliteConnection("data source=Data\\master.db");
                source.Open();
                Connection = new SqliteConnection("Filename=:memory:");
                Connection.Open(); 
                source.BackupDatabase(Connection);
                source.Close();
            }
        }

        public static SqliteConnection Connection { get; set; }
    }

public static class SqliteInMemoryDatabase
    {
        private static object key = new();
        static SqliteInMemoryDatabase()
        {
            lock (key)
            {
                SqliteConnection source = new SqliteConnection("data source=Data\\master.db");
                source.Open();
                Connection = new SqliteConnection("Filename=:memory:");
                Connection.Open(); 
                source.BackupDatabase(Connection);
                source.Close();
            }
        }

        public static SqliteConnection Connection { get; set; }
    }

First you create a class that load the local db to your in memory db

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlite(SqliteInMemoryDatabase.Connection);
            }
        }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlite(SqliteInMemoryDatabase.Connection);
            }
        }

Then in EF you just call the field And yes as expected, the result is flying

Unknown User•3y ago

Message Not Public

Gaming

Programming

Load local db file to memory