samedi 20 février 2016

SQL method taking a long time

I'm using SQLite with C#.

I use the following method to loop through a list of tv shows and their episodes and insert them into the database. It ends up entering about 1200 rows and takes 2 minutes 28 seconds. It feels like that's kinda slow so I would like people to look over my method and see if i'm doing something stupid (I'm sorta new to this).

public void InsertAllEpisodes(List<TVDBSharp.Models.Show> showList)
    {

        using (SQLiteConnection dbconnection = new SQLiteConnection(connectionString))
        {
            string seasonNumber;
            string episodeNumber;
            string insertShowSQL = @"INSERT INTO AllEpisodes (Key, ShowName, ShowId, EpisodeName, SeasonEpisode) VALUES (@Key, @ShowName, @ShowId, @EpisodeName, @SeasonEpisode)";

            foreach (var show in showList)
            {
                foreach (var episode in show.Episodes)
                {
                    if (episode.SeasonNumber != 0)
                    {
                        string seasonEpisode = "default";
                        if ((episode.SeasonNumber != 0) && (episode.SeasonNumber.ToString().Length == 1)) { seasonNumber = "0" + episode.SeasonNumber.ToString(); }
                        else { seasonNumber = episode.SeasonNumber.ToString(); }
                        if (episode.EpisodeNumber.ToString().Length == 1) { episodeNumber = "0" + episode.EpisodeNumber.ToString(); }
                        else { episodeNumber = episode.EpisodeNumber.ToString(); }
                        seasonEpisode = seasonNumber + episodeNumber;
                        SQLiteCommand command = new SQLiteCommand(insertShowSQL, dbconnection);
                        command.Parameters.AddWithValue("Key", show.Id + seasonEpisode);
                        command.Parameters.AddWithValue("ShowName", show.Name);
                        command.Parameters.AddWithValue("ShowId", show.Id);
                        command.Parameters.AddWithValue("EpisodeName", episode.Title);
                        command.Parameters.AddWithValue("SeasonEpisode", seasonEpisode);
                        dbconnection.Open();
                        try
                        {
                            command.ExecuteScalar();
                        }
                        catch (SQLiteException ex)
                        {
                            if (ex.ResultCode != SQLiteErrorCode.Constraint)
                            {
                                File.AppendAllText(programDataPath + "SQLErrors.txt", ex.Message + "\n");
                            }
                        }
                        dbconnection.Close();
                    }
                }

            }
        }

    }

Aucun commentaire:

Enregistrer un commentaire