c#/.NET SQLite -- REINDEX not working?

Question

Hello all,

I'm trying to reindex a table in a simple database that I created using SQLite.NET and VS2008. I need to reindex tables after every DELETE command and here is the code snippet I have written (it does not work):

SQLiteCommand currentCommand;
String tempString = "REINDEX tf_questions";
//String tempString = "REINDEX [main].tf_questions";
//String tempString = "REINDEX main.tf_questions";

currentCommand = new SQLiteCommand(myConnection);
currentCommand.CommandText = tempString;
currentCommand.ExecuteNonQuery()

When run within my program, the code produces no errors, but it also doesn't reindex the "tf_questions" table. In the above example, you will also see the other query strings I've tried that also don't work.

Please help,

Thanks

Answer 1

I figured out a workaround for my problem. Consider the following code:

SQLiteCommand currentCommand;
String tempString;
String currentTableName = "tf_questions";
DataTable currentDataTable;
SQLiteDataAdapter myDataAdapter;

currentDataTable = new DataTable();
myDataAdapter = new SQLiteDataAdapter("SELECT * FROM " + currentTableName, myConnection);
myDataAdapter.Fill(currentDataTable);


//"tf_questions" is the name of the table
//"question_id" is the name of the primary key column in "tf_questions" (set to auto inc.)
//"myConnection" is and already open SQLiteConnection pointing to the db file

for (int i = currentDataTable.Rows.Count-1; i >=0 ; i--)
{
    currentCommand = new SQLiteCommand(myConnection);
    tempString = "UPDATE "+ currentTableName +"\nSET question_id=\'"+(i+1)+"\'\nWHERE (question_id=\'" +
        currentDataTable.Rows[i][currentDataTable.Columns.IndexOf("question_id")]+"\')";
    currentCommand.CommandText = tempString;

    if( currentCommand.ExecuteNonQuery() < 1 )
    {
        throw new Exception("There was an error executing the REINDEX protion of the code...");
    }
}

This code works, though I would have liked to have used the built-in SQL REINDEX command.

Answer 2

If you are doing this for the sake of performance, consider this answer:

REINDEX does not help. REINDEX is only needed if you change a collating sequence.

After a lot of INSERTs and DELETEs, you can sometimes get slightly better performance by doing a VACUUM. VACUUM improves locality of reference slightly.