Topics

C#•2mo ago

Improving performance / caching

Hello, Im coming to an end with my database software. I filled the database with around 150 rows, but now I got the problem that performance is really trash with everything it does, even if it only does single row querys. So I thought maybe it´s possible to create a cache which stores all data once they are loaded which then is being used by all the other methods making a query. This is the code where all aircrafts are being collected, at the moment the data is loaded asynchron to not block the UI. How can I build such a cache with is used by every method?

36 Replies

PANZER234425OP•2mo ago

public List<AircraftData> GetAircrafts(int limit, int offset)
{
    List<AircraftData> aircraftList = new List<AircraftData>();
    HashSet<string> seenRegistrations = new HashSet<string>();

    try
    {
        openConnection();

        string getAircraftsQuery = "SELECT * FROM aircraft LIMIT @Limit OFFSET @Offset;";

        MySqlCommand cmdGetAircrafts = new MySqlCommand(getAircraftsQuery, con);
        cmdGetAircrafts.Parameters.AddWithValue("@Limit", limit);
        cmdGetAircrafts.Parameters.AddWithValue("@Offset", offset * limit);

        using (MySqlDataReader reader = cmdGetAircrafts.ExecuteReader())
        {
            while (reader.Read())
            {
                string? registration = reader.IsDBNull(reader.GetOrdinal("registration")) ? null : reader.GetString("registration");

                if (registration != null && !seenRegistrations.Contains(registration))
                {
                    AircraftData aircraft = new AircraftData()
                    {
                        Registration = registration,
                        Type = reader.IsDBNull(reader.GetOrdinal("type")) ? null : reader.GetString("type"),
                        Seats = reader.GetInt32(reader.GetOrdinal("seats")),

public List<AircraftData> GetAircrafts(int limit, int offset)
{
    List<AircraftData> aircraftList = new List<AircraftData>();
    HashSet<string> seenRegistrations = new HashSet<string>();

    try
    {
        openConnection();

        string getAircraftsQuery = "SELECT * FROM aircraft LIMIT @Limit OFFSET @Offset;";

        MySqlCommand cmdGetAircrafts = new MySqlCommand(getAircraftsQuery, con);
        cmdGetAircrafts.Parameters.AddWithValue("@Limit", limit);
        cmdGetAircrafts.Parameters.AddWithValue("@Offset", offset * limit);

        using (MySqlDataReader reader = cmdGetAircrafts.ExecuteReader())
        {
            while (reader.Read())
            {
                string? registration = reader.IsDBNull(reader.GetOrdinal("registration")) ? null : reader.GetString("registration");

                if (registration != null && !seenRegistrations.Contains(registration))
                {
                    AircraftData aircraft = new AircraftData()
                    {
                        Registration = registration,
                        Type = reader.IsDBNull(reader.GetOrdinal("type")) ? null : reader.GetString("type"),
                        Seats = reader.GetInt32(reader.GetOrdinal("seats")),

                        Engines = reader.IsDBNull(reader.GetOrdinal("engines")) ? null : reader.GetString("engines"),
                        Airline = reader.IsDBNull(reader.GetOrdinal("airline")) ? null : reader.GetString("airline"),
                        FirstFlight = reader.IsDBNull(reader.GetOrdinal("firstFlight")) ? DateTime.MinValue : reader.GetDateTime("firstFlight"),
                        SpecialLivery = reader.IsDBNull(reader.GetOrdinal("specialLiveryName")) ? null : reader.GetString("specialLiveryName"),
                        AircraftPicture = reader.IsDBNull(reader.GetOrdinal("aircraftPicture")) ? null : (byte[])reader["aircraftPicture"],
                        Favorite = reader.GetBoolean(reader.GetOrdinal("favorite"))
                    };
                    aircraftList.Add(aircraft);
                    seenRegistrations.Add(registration);
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error loading aircrafts: " + ex.Message);
    }
    finally
    {
        closeConnection();
    }
    return aircraftList;
}

                        Engines = reader.IsDBNull(reader.GetOrdinal("engines")) ? null : reader.GetString("engines"),
                        Airline = reader.IsDBNull(reader.GetOrdinal("airline")) ? null : reader.GetString("airline"),
                        FirstFlight = reader.IsDBNull(reader.GetOrdinal("firstFlight")) ? DateTime.MinValue : reader.GetDateTime("firstFlight"),
                        SpecialLivery = reader.IsDBNull(reader.GetOrdinal("specialLiveryName")) ? null : reader.GetString("specialLiveryName"),
                        AircraftPicture = reader.IsDBNull(reader.GetOrdinal("aircraftPicture")) ? null : (byte[])reader["aircraftPicture"],
                        Favorite = reader.GetBoolean(reader.GetOrdinal("favorite"))
                    };
                    aircraftList.Add(aircraft);
                    seenRegistrations.Add(registration);
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error loading aircrafts: " + ex.Message);
    }
    finally
    {
        closeConnection();
    }
    return aircraftList;
}

Angius•2mo ago

Oof, doing some manual ADO stuff, my sincere condolences For caching, there's https://www.nuget.org/packages/Microsoft.Extensions.Caching.Memory I believe that's what ASP Core uses under the hood for it's caching All that said, though, you should really not be having any performance issues with a database this small

PANZER234425OP•2mo ago

each row got a picture in it with 5mb

Angius•2mo ago

Why the hell are you storing images in the database

PANZER234425OP•2mo ago

where else xD

Angius•2mo ago

In the file system on disk? Or in a CDN? Then you just save the path in the database and you're good

PANZER234425OP•2mo ago

but its on a local server

Angius•2mo ago

How does that change anything?

PANZER234425OP•2mo ago

yeah, good question I thought this would be the best solution :harold: So shall I scrap it or does the caching also works with the pictures?

Angius•2mo ago

Caching is a solution for a problem that isn't there

Angius•2mo ago

https://tenor.com/view/spoon-stupid-funny-gif-21066994

Tenor

PANZER234425OP•2mo ago

what would you do?

Angius•2mo ago

I would store the images on disk or in a CDN and store just paths to them in the database Or, in case of a CDN, whatever additional ID or whatever is necessary

PANZER234425OP•2mo ago

okay Thanks

Monsieur Wholesome•2mo ago

I approve of what Z said Huge amounts of data (images, arbitrary binary data, big amounts of texts, etc etc) make databases slow And relational databases are very very expensive to host So it's just not a good practice https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/?from=https://research.microsoft.com/apps/pubs/default.aspx?id=64525&type=exact Is a good research paper talking about disk (blob storage being just one way of saving a file on a disk) vs database storage of files

PANZER234425OP•2mo ago

Can you help in me in doing this, I literally builded the whole thing around storing the pictures in the db and dont know how to start now over with this again :sadge: and what if the path changes, eg i buy a new PC

Angius•2mo ago

You use relative paths Paths starting with ./ for example, will be relative to where the .exe is

Monsieur Wholesome•2mo ago

public class File
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
    public ulong Size { get; set; }
}

public class FileManager
{
    public File CreateFile(byte[] fileData, string fileName)
    {
        const string FileDirectory = "./Files";

        var file = new File()
        {
            Name = fileName,
            Size = fileData.Length,
            Location = Path.Combine(FileDirectory, fileName)
        };

        File.WriteAllBytes(file, fileData);

        // Save File to your sql database, which only contains an id, the name, maybe the file size and the Location of your file ("./Files/YourFileName.txt")
        // You can then reference the File within whatever other database row you have via its id
        SaveFileToDatabase(file);

        return file;
    }
}

public class File
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
    public ulong Size { get; set; }
}

public class FileManager
{
    public File CreateFile(byte[] fileData, string fileName)
    {
        const string FileDirectory = "./Files";

        var file = new File()
        {
            Name = fileName,
            Size = fileData.Length,
            Location = Path.Combine(FileDirectory, fileName)
        };

        File.WriteAllBytes(file, fileData);

        // Save File to your sql database, which only contains an id, the name, maybe the file size and the Location of your file ("./Files/YourFileName.txt")
        // You can then reference the File within whatever other database row you have via its id
        SaveFileToDatabase(file);

        return file;
    }
}

PANZER234425OP•2mo ago

So, I Change the datatype in The DB to varchar and create The Method above and just call it whenever I need to interact with a Picture?

Monsieur Wholesome•2mo ago

In the db you change the column from byte[] type you were storing it as To an int Foreign Key, that points to a File object

PANZER234425OP•2mo ago

So shall i create a table and link it to The main table with a foreign Key, The Table then only Stores a path

Monsieur Wholesome•2mo ago

yes The file class i gave as an example of what the table would probably look like

PANZER234425OP•2mo ago

Why not use the current table, instead of The Picture use a string

Monsieur Wholesome•2mo ago

With a separate table for files, you'd have a centralized management point Of course, you can scatter information about the existence of a file around the table, but it can and will in the long run hurt integrity Things will get lost, files will be deleted, and various tables need to be updated extensively A good database design will teach you a thing or two about good practice :catlaugh:

PANZER234425OP•2mo ago

So this is my current database aircraft: airline: picture: You got any further improvments?

No description

Angius•2mo ago

I usually don't prefix/suffix the primary key with table name It's useless information IMO But besides that, LGTM

PANZER234425OP•2mo ago

So thats basically the new table I created now

No description

PANZER234425OP•2mo ago

Okay, so im now copying the file to a directory in the project folder and the database is looking like this now

PANZER234425OP•2mo ago

PANZER234425OP•2mo ago

PANZER234425OP•2mo ago

Is this correct (Besides the suffix) So its saving the picture files, but I just cant get it to work so it shows the picture again in the pictureBox, it just shows an empty box without any error

PANZER234425OP•2mo ago

No description

Monsieur Wholesome•2mo ago

Thaat is up to you to fix now

PANZER234425OP•2mo ago

Okay, so im pretty much done now, I got one last problem Basically when Im deleting a picture it says that its being used by another thread and so it cant delete the picture I already set the pictureBox NULL where the picture is in but it still says that its being used

PANZER234425OP•2mo ago

No description

PANZER234425OP•2mo ago

        private Home home;
        public void Application_Exit(object sender, EventArgs e)
        {
            if (home != null)
            {
                home.SetPictureNull();
            }

            MessageBox.Show("Application Exit Called");
            MessageBox.Show("Number of paths to delete: " + imagePathsToDelete.Count);

            string exePath = AppDomain.CurrentDomain.BaseDirectory;

            foreach (var relativePath in imagePathsToDelete)
            {
                string fullImagePath = Path.Combine(exePath, relativePath);

                if (System.IO.File.Exists(fullImagePath))
                {
                    try
                    {
                        GC.Collect();
                        GC.WaitForPendingFinalizers();

                        MessageBox.Show("Deleting file: " + fullImagePath);
                        System.IO.File.Delete(fullImagePath);
                        MessageBox.Show("Deleted: " + fullImagePath);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error deleting file: " + ex.Message);
                    }
                }
                else
                {
                    MessageBox.Show("File not found: " + fullImagePath);
                }
            }

            Application.Exit(); 
        }

        private Home home;
        public void Application_Exit(object sender, EventArgs e)
        {
            if (home != null)
            {
                home.SetPictureNull();
            }

            MessageBox.Show("Application Exit Called");
            MessageBox.Show("Number of paths to delete: " + imagePathsToDelete.Count);

            string exePath = AppDomain.CurrentDomain.BaseDirectory;

            foreach (var relativePath in imagePathsToDelete)
            {
                string fullImagePath = Path.Combine(exePath, relativePath);

                if (System.IO.File.Exists(fullImagePath))
                {
                    try
                    {
                        GC.Collect();
                        GC.WaitForPendingFinalizers();

                        MessageBox.Show("Deleting file: " + fullImagePath);
                        System.IO.File.Delete(fullImagePath);
                        MessageBox.Show("Deleted: " + fullImagePath);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error deleting file: " + ex.Message);
                    }
                }
                else
                {
                    MessageBox.Show("File not found: " + fullImagePath);
                }
            }

            Application.Exit(); 
        }

public void SetPictureNull()
{
    MessageBox.Show("Called");
    Dispose();
    Hide();
    picMostRecent.Image = null;
    picRandomAircraft.Image = null;
    Thread.Sleep(5000);
}

public void SetPictureNull()
{
    MessageBox.Show("Called");
    Dispose();
    Hide();
    picMostRecent.Image = null;
    picRandomAircraft.Image = null;
    Thread.Sleep(5000);
}

We are a programming server aimed at coders discussing everything related to C# (CSharp) and .NET.

54KMembers

View on Discord

Want results from more Discord servers?

Add your server