System.Data.SQLite

Hi,
I'm using with success sqlite, but I have a little performace problem in a simple select. I have a table with this definition

CREATE TABLE ClientiFornitori
(
 [id_clifor] GUID
,[Tipo] int
,[Codice] int
,[RagioneSociale] varchar(300)
,[NomeSemplificato] varchar(300)
,[Indirizzo] varchar(100)
,[Localita] varchar(100)
,[Provincia] varchar(100)
,[CAP] varchar(100)
,[Nazione] varchar(100)
,[PartitaIVA] varchar(11)
,[CodiceFiscale] varchar(16)
,[id_pagamento] INTEGER
,[Note] varchar(100)
,[CreatedUTCDate] datetime DEFAULT CURRENT_TIMESTAMP
,constraint fk_ClientiFornitori_Pagamenti foreign key(id_pagamento) references Pagamenti(id_pagamento)

and with this index:

CREATE INDEX IX_ClientiFornitori_id_clifor ON ClientiFornitori (id_clifor)
CREATE INDEX IX_ClientiFornitori_Tipo ON ClientiFornitori (Tipo)
CREATE INDEX IX_ClientiFornitori_Codice ON ClientiFornitori (Codice)
CREATE INDEX IX_ClientiFornitori_RagioneSociale ON ClientiFornitori (RagioneSociale)

I insert bulk data very fast as described in the how to, but when I try to select the record the query is very slow: it takes 32seconds to get 10000 record (the db file is about 3.2MB).

I use this code to retrieve the records:

List<ClienteFornitore> cliforList = new List<ClienteFornitore>();using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))

{

using (SQLiteCommand cmd = conn.CreateCommand())

{

StringBuilder sql = new StringBuilder();

sql.Append("SELECT * FROM ClientiFornitori");

sql.Append(" WHERE Tipo=?");

sql.Append(" ORDER BY RagioneSociale");

cmd.CommandText = sql.ToString();

SQLiteParameter par = cmd.CreateParameter();

cmd.Parameters.Add(par);

par.Value = tipo;

conn.Open();

SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (rdr.Read())

{

ClienteFornitore clifor = new ClienteFornitore();

clifor.id_clifor = (Guid)rdr["id_clifor"];

if (rdr["CAP"] != DBNull.Value)

clifor.CAP = (string)rdr["CAP"];

if (rdr["Codice"] != DBNull.Value)

clifor.Codice = (int)rdr["Codice"];

if (rdr["CodiceFiscale"] != DBNull.Value)

clifor.CodiceFiscale = (string)rdr["CodiceFiscale"];

if (rdr["CreatedUTCDate"] != DBNull.Value)

clifor.CreatedUTCDate = (DateTime)rdr["CreatedUTCDate"];

if (rdr["Indirizzo"] != DBNull.Value)

clifor.Indirizzo = (string)rdr["Indirizzo"];

if (rdr["Localita"] != DBNull.Value)

clifor.Localita = (string)rdr["Localita"];

if (rdr["Nazione"] != DBNull.Value)

clifor.Nazione = (string)rdr["Nazione"];

if (rdr["Note"] != DBNull.Value)

clifor.Note = (string)rdr["Note"];

if (rdr["PartitaIVA"] != DBNull.Value)

clifor.PartitaIVA = (string)rdr["PartitaIVA"];

if (rdr["Provincia"] != DBNull.Value)

clifor.Provincia = (string)rdr["Provincia"];

if (rdr["RagioneSociale"] != DBNull.Value)

clifor.RagioneSociale = (string)rdr["RagioneSociale"];

if (rdr["Tipo"] != DBNull.Value)

clifor.Tipo = (eTipo)rdr["Tipo"];

cliforList.Add(clifor);

}

if (!rdr.IsClosed)

rdr.Close();

}

}

 

What can I do to improve performance?

 Thanks, VueMme!

The first thing you can do is not use rdr["fieldname"] and cast the result to something.  If its a string, use rdr.GetString(x).  If its an int, use rdr.GetInt32(x).

Second, if you know the column index, use that instead of the name -- it saves a lookup.

Robert

 

 

Thanks for the answer!

I have made some test:

Using rdr["fieldname"] and cast to the right type without checking for dbnull values takes about 18 seconds, usign rdr[index] and cast the results takes about 5 seconds, and, finally, using rdr.GetString(x), rdr.GetInt32(x) and so on, ecc. takes about 4 seconds.

I get the performance I want, thank you!!!

Valerio.