fast directory infos for a lot of files

[pitwi]

I'm writing a WPF (C#) app for my MP3 collection with more then 350,000 files. I'm reading path and filenames directly from the HD and store the infos in a SQLite Database. Because of reorganizationes I have to do that in periodic intervals. But the current code needs 17 hours (!!!) for that. I'm sure there must be a faster way. Does anyone have an idea?

    private void ButtonImport\_Click(object sender, RoutedEventArgs e)
    {
        // Import

        string initdir = @"H:\\mp3\\";

        Cursor cu = this.Cursor;
        this.Cursor = Cursors.Wait;
        string errtxt = "";
        // delete all existing database entries
        ToolStripStatusLabel1.Text = "deleting ..."
        try
        {
            SQLiteCommand dbCom = new SQLiteCommand("DELETE FROM Dateien", dbConn);
            dbCom.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "delete error", MessageBoxButton.OK, MessageBoxImage.Error);
            return;
        }
        sPfad = initdir;
        // import
        DateTime t1 = DateTime.Now;
        int z = 0;
        loopDir(sPfad);
        DateTime t2 = DateTime.Now;
        txt = "";
        txt += z.ToString() + " files saved in ";
        txt += ((t2 - t1).TotalSeconds / 60).ToString("0.0") + " min.";
        txt += errtxt != "" ? Environment.NewLine + Environment.NewLine + errtxt : "";
        this.Cursor = cu;
        MessageBox.Show(txt, "Import", MessageBoxButton.OK, MessageBoxImage.Information);
    }


    private void loopDir(string strPath)
    {
        // read directories and files
        string dn = "";
        DirectoryInfo ofs = new DirectoryInfo(strPath);
        bool f = false;
        int n = 0;
        try
        {
            n = ofs.GetDirectories().Length;
        }
        catch (Exception ex)
        {
            f = true;
            errtxt += ex.Message + Environment.NewLine;
        }
        if (f == false)
        {
            foreach (DirectoryInfo d in ofs.GetDirectories())
            {
                dn = strPath + "\\\\" + d.Name;
                dn = dn.Replace("\\\\\\\\", "\\\\");
                loopDir(dn);  // recursive call
            }
            // file loop
            bool ok = false;

[pitwi]

I found an answer myself: Reading all 350,000 files from the HD takes 6.2 minutes (!!!). So the bottleneck is SQLite and I think I have to use SQL transactions to speed up my app.

[Richard Deeming]

Well, that code is ... interesting. There's a lot of needless work going on there. Try something like this instead:

private void loopDir(DirectoryInfo folder, DataTable table, List errors)
{
try
{
foreach (DirectoryInfo subFolder in folder.EnumerateDirectories())
{
loopDir(subFolder, table, errors);
}

    string pfad = folder.FullName.Substring(2);
    foreach (FileInfo file in folder.EnumerateFiles())
    {
        string extension = Path.GetExtension(file.Name);
        if (!fext.Contains(extension)) continue;
        
        table.Rows.Add(pfad, file.Name);
    }
}
catch (Exception ex)
{
    errors.Add(ex.Message);
}

}

private int ImportFiles(string initialFolder, List errors)
{
DirectoryInfo folder = new DirectoryInfo(initialFolder);
DataTable table = new DataTable();
table.Columns.Add("Pfad", typeof(string));
table.Columns.Add("Datei", typeof(string));
loopDir(folder, table, errors);

if (table.Rows.Count == 0)
{
    MessageBox.Show("No files found to import.", "Import", MessageBoxButton.OK, MessageBoxImage.Error);
    return 0;
}

using (SQLiteTransaction dbTrans = dbConn.BeginTransaction())
{
    using (SQLiteCommand dbCom = new SQLiteCommand("DELETE FROM Dateien", dbConn, dbTrans))
    {
        dbCom.ExecuteNonQuery();
    }
    
    using (SQLiteCommand dbCom = new SQLiteCommand("INSERT INTO Dateien (Pfad, Datei) VALUES ($Pfad, $Datei)", dbConn, dbTrans))
    {
        SQLiteParameter pPfad = dbCom.CreateParameter();
        pPfad.ParameterName = "$Pfad";
        dbCom.Parameters.Add(pPfad);
        
        SQLiteParameter pDatei = dbCom.CreateParameter();
        pDatei.ParameterName = "$Datei";
        dbCom.Parameters.Add(pDatei);
        
        foreach (DataRow row in table.Rows)
        {
            pPfad.Value = row\["Pfad"\];
            pDatei.Value = row\["Datei"\];
            dbCom.ExecuteNonQuery();
        }
    }
    
    transaction.Commit();
}

return table.Rows.Count;

}

private void ButtonImport_Click(object sender, RoutedEventArgs e)
{
Cursor cu = this.Cursor;
this.Cursor = Cursors.Wait;
try
{
const string initdir = @"H:\mp3\";
List errors = new List();

[Dave Kreskowiak]

Transactions do not speed up database operations. Transactions encapsulate multiple operations so they execute as a single set. If any operation fails, all of the operations in the transaction are rolled back. What you should have been doing is using a parameterized INSERT query, and NOT that string concatenation crap.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

[pitwi]

Thanks Richard, thats really great. A little correction to the following line:

using (SQLiteCommand dbCom = new SQLiteCommand("INSERT INTO Dateien (Pfad, Datei) VALUES ($Pfad, $Datei)"))

to

using (SQLiteCommand dbCom = new SQLiteCommand("INSERT INTO Dateien (Pfad, Datei) VALUES ($Pfad, $Datei)", dbConn, dbTrans))

And now it works perfectly. And wow, it's done in 12 seconds !!! Very impressive !!! THANKS!