dimanche 2 août 2015

Optimize sqlite query and being able to compare object and database

I'm developing a front end for retro gaming. Upon boot I'm parsing a lot of xml files from another application so I end up with a List of "systems" each containing a list of "games". Parsing the xml data is really fast, but writing all this to the sqlite database is not. Currently it takes ~25 seconds (20.000 records) which may not be too bad, but hopefully I can get some ideas on how to make it even faster.

Ideally I want to be able to compare my object containing all the data with the sqlite database. If anything has changed in the xml files/parsed object the data should be updated or deleted from the database. Is there a better way of solving this then doing a initial import and then dump all of the database and compare it to the object? Basically the whole import code backwards...

This is my current code:

public static void PopulateDatabase(List<RetroBoxSystem> systems)
    {
        using (SQLiteConnection con = new SQLiteConnection("Data Source=RetroBox.db;Version=3;"))
        {
            con.Open();

            using (SQLiteTransaction tr = con.BeginTransaction())
            {
                using (SQLiteCommand cmd = con.CreateCommand())
                {
                    foreach (var system in systems)
                    {
                        cmd.CommandText = @"INSERT OR IGNORE INTO systems(system_id, name)
                                            VALUES ((SELECT system_id FROM systems WHERE name = @name), @name)";

                        cmd.Parameters.Add(new SQLiteParameter("@name", system.Name));
                        cmd.ExecuteNonQuery();
                    }
                }

                using (SQLiteCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = @"INSERT OR IGNORE INTO games(game_id, system_id, name, description, cloneof, manufacturer, genre, rating, year)
                                                VALUES ((SELECT game_id FROM games WHERE name = @name), (SELECT system_id FROM systems WHERE name = @system), @name, @description, @cloneof, @manufacturer, @genre, @rating, @year)";

                    foreach (var system in systems)
                    {
                        foreach (var g in system.GameList)
                        {
                            cmd.Parameters.Add(new SQLiteParameter("@system", system.Name));
                            cmd.Parameters.Add(new SQLiteParameter("@name", g.Name));
                            cmd.Parameters.Add(new SQLiteParameter("@description", g.Description));
                            cmd.Parameters.Add(new SQLiteParameter("@cloneof", g.CloneOf));
                            cmd.Parameters.Add(new SQLiteParameter("@manufacturer", g.Manufacturer));
                            cmd.Parameters.Add(new SQLiteParameter("@genre", g.Genre));
                            cmd.Parameters.Add(new SQLiteParameter("@rating", g.Rating));
                            cmd.Parameters.Add(new SQLiteParameter("@year", g.Year));
                            cmd.ExecuteNonQuery();
                        }
                    }
                }

                tr.Commit();
            }
        }
    }

Aucun commentaire:

Enregistrer un commentaire