Problem reading join tables

Last post 08-24-2009 1:29 AM by jam1982. 4 replies.

Page 1 of 1 (5 items)
	Sort Posts: Previous Next

08-21-2009 7:43 AM

jam1982
Joined on 08-21-2009
Posts 4

Problem reading join tables

Reply Contact

Hi everyone,

I have two tables that I want to join. This is my C# code:

private static void readTable()

{

SQLiteConnection cn = null;

try

{

cn = new SQLiteConnection(Connessione);

cn.Open();

SQLiteCommand cmd = cn.CreateCommand();

cmd.CommandText = "SELECT * FROM OB_Stops INNER JOIN OB_StopDetails ON OB_Stops.IdCp = OB_StopDetails.IdCp WHERE IdStop <=500";

SQLiteDataReader myReader = cmd.ExecuteReader();if (myReader.HasRows)

{

DateTime dStart1 = DateTime.Now;

while (myReader.Read())

{

Console.WriteLine("IdKey = {0}\tIdCp = {1}", myReader[0].ToString(), myReader[1].ToString());

}

DateTime dStop1 = DateTime.Now;

Console.WriteLine(dStart1.ToString("HH:mm:ss.ffff") + " start reading table");

Console.WriteLine(dStop1.ToString("HH:mm:ss.ffff") + " end reading table");

myReader.Close();

myReader = cmd.ExecuteReader();

}

else

{

Console.WriteLine("No records");

}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{

if (cn != null)

cn.Close();

}

I print all records in the right way but after writing last record fields it remains stopped for some seconds on this instruction: while (myReader.Read()).

Then it countinues printing the other lines on the console.

What doesn't work?

thanks in advance.

-jam

Filed under: slow select, join, where, SELECT, DataReader

08-21-2009 3:56 PM In reply to

SqlRanger
Joined on 01-15-2006
Madrid (Spain)
Posts 209

Re: Problem reading join tables

Reply Contact

It sounds like there are many rows with IdStop > 500 and there is not good index to solve the query, so SQLite has to scan the entire table and check if the condition IdStop <= 500 is met. I guess the scan order is similar to IdStop order, therefore after IdStop = 500 is reached, a lot of rows remain to be scanned but none of them meet the condition, that is the reason why you observe that delay, SQLite is scanning the rest of rows.

To solve the problen you need to find a good index. Probably an index with IdStop as the first column in the key and IdCp as the second, will work. You should also experiment with JOIN order. Generally, placing the smaller table first is faster.

Regards

Jesús López

08-23-2009 11:41 PM In reply to

jam1982
Joined on 08-21-2009
Posts 4

Re: Problem reading join tables

Reply Contact

Thanks for your answer. I try with a different index and I'll report the result.

08-24-2009 12:47 AM In reply to

jam1982
Joined on 08-21-2009
Posts 4

Re: Problem reading join tables

Reply Contact

I tried modifying the database as follows:

create index IX_OB_Stops_1 ON OB_Stops (IdStop ASC, IdCp ASC);
create index IX_OB_StopDetails_1 ON OB_StopDetails (IdStopDetail ASC, IdCp ASC);

where IdStop is the primary key of OB_Stops and IdStopDetails is the primary key of OB_StopDetails.

Now the result is that it comes out from the while-condition as desired but the console printing loop is slower than before.

How could I improve this situation now?

08-24-2009 1:29 AM In reply to

jam1982
Joined on 08-21-2009
Posts 4

Re: Problem reading join tables

Answer

Reply Contact

Hi,

it seems to work now! We have used a different couple of indexes:

create index IX_OB_Stops_1 ON OB_Stops (IdCp ASC);

create index IX_OB_StopDetails_1 ON OB_StopDetails (IdCp ASC);

Thanks for your help.

Page 1 of 1 (5 items)