SQLite increase query speed

[Shako]

Hi

How could i increase the reading speed of Sqlite ?

Im using version 3.6.17.

Using transactions does not really help.

This is the function i use to read or write to the database :

int StepQuery(char* query,char* CellText,int ColoumnNumber)
{
int Status=0;
const char *query_tail=NULL;
int MaxQueryLength,NumOfRows;
sqlite3_stmt *stmt=NULL;
const unsigned char* LocalCellText="";
char Error[10000];
sqlite3 *DataBase=NULL;
	
if (DatabaseSwitchFlag==1)
{
	DataBase=TempDatabase;	
}
else
{
	DataBase=SqlDatabaseHndl;	
}

LocalCellText="";
MaxQueryLength=0;
NumOfRows=0;
MaxQueryLength=strlen(query);
Status=sqlite3_prepare_v2(DataBase, query, MaxQueryLength, &stmt, &query_tail);
do 
{
        Status=sqlite3_step(stmt);
        if ((Status==SQLITE_ROW))
	{
                LocalCellText=sqlite3_column_text(stmt, ColoumnNumber);
		if (LocalCellText!=NULL)
		{
			strcpy(CellText,LocalCellText);
		}
		else
		{
			strcpy(CellText,"");	
		}
		NumOfRows++; 
	}
} while (Status==SQLITE_ROW);
Status=sqlite3_finalize(stmt);
strcpy(query,"");
return NumOfRows;
}

 

[Snowman 6008]

Shako,

Depending on your data one easy way to speed up queries is to index heavily searched columns. http://www.sqlite.org/lang_createindex.html  Though the creation of too many indicies can cause your queries to slow down. There is an entire field devoted to speeding up and optimizing SQL queries, here is an entire page devoted to optimizing SQLite  http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html  (though not the same version, many of the same principals should be the same). SQLite even provides an optimization tool http://www.sqlite.org/optoverview.html . Database connections are normally the bottleneck of most applications that use them. Check out these resources provided. You can also consider pulling more data with each query (eager loading, using joins and includes) or pulling less data (if you're not using all of the query that you are pulling in). 

[Shako]

I have attached an example of my database.

This database is normally  run in memory.

I have read throught the SQLite Optimisation and most of it is already implemented.

Please have a look at my database i am not sure how to set up indexes that will improve the speed.

How i query for a value is that i get the rowid first and then query for the rest of the values using that rowid.

I also query for one value at a time because of the function in the program.

I have tried to query for multi values with one query but it did not improve on the speed.

[Snowman 6008]

How long does an average query take you, and are you using sqlite3 on your local machine or on a network server? My experience with sqlite is that write time is slightly slower than when using other ODBC's such as mysql, but I don't have any data to back up that claim. You can create an index of your rowid to increase the speed that your database is capable of finding the row you are looking for, but it will not increase the speed of actually pulling the data.