Interbase inconsistant speed 2

[BorlandDave]

I have an interbase database with a table containing 3 fields. The DB has about 3000 entries. I have some code which should pull out half of the entries. I am timing this function using timeGetTime() and I am getting some very inconsistant results. Sometimes the function takes less than 0.1 seconds and other times it takes over 11 seconds. Why is this the case? Here is my code...

-------------------------------------------------------------

int res;
int iField2;
AnsiString sField3;

res = -1;
//Make sure we have a db name
if (InterbaseDB->DatabaseName.Length() >0)
{
try
{
//Make connection to DB
InterBaseDB->Connected = true;

try
{
InterBase_Q->SQL->Clear();
InterBase_Q->SQL->Add("SELECT * FROM TABLE_1");
InterBase_Q->SQL->Add("WHERE FIELD_1 = "+IntToStr(1));
InterBase_Q->SQL->Add("ORDER BY FIELD_2");
InterBase_Q->Prepare();

try
{
InterBase_Q->Open();
InterBase_Q->First();

while (!InterBase_Q->Eof)
{
//Pull in the data from the InterBase table
iField2=InterBase_Q->FieldByName("FIELD_2")->AsInteger;
sField3=InterBase_Q->FieldByName("FIELD_3")->AsString;
mapInterBase.insert (mapINT_ANSISTRING::value_type(iField2, sField3));

InterBase_Q->Next();
}
}
catch(...)
{
res =5; //Failed to open table
}
}
catch (...)
{
res = 4; //Failed to establish columns
}

//Disconnect the DB.
InterBaseDB->Connected = false;
}
catch (...)
{
res = 2; //Could not open DB
}
}

 

[2ffat]

I'm not that familar with InterBase so my comments/questions may be off the mark. Is the DB local or on a server accessed via a network connection?

If it is on a server maybe it has something to do with networking or someone else accessing the server or DB.

How many records are you accessing with Field1? The more records you have, the longer it takes to order them and iterate through them.


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[BorlandDave]

The DB is local. I am accessing about 1500 records with FIELD_1. This number of records is always constant, however my access times vary greatly. But the strange thing is they are always around 0.1 seconds or around 10 seconds. I never get times at say 5 seconds.

 

[2ffat]

Just for curiosity, comment out the following line.

//mapInterBase.insert (mapINT_ANSISTRING::value_type(iField2, sField3));

See what happens if you just iterate though the records without doing anything.

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[BorlandDave]

Good idea, but it didn't help

I ran the prog 3 times with the line commented out and got the following speeds...

10.203000
0.094000
0.110000

I have no idea why the first time was so much slower than the second and third times.

 

[2ffat]

I've seen the same thing in SQL databases like Sequel and such. I think it may have to do with the DB prepping the query even though you tell it to earlier. Once the DB or table has been accessed, it seems to run faster. I've always thought it had to do with how SQL DB's function because I've seen it in so many DB's.

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[BorlandDave]

Right, but in my tests I'm equally likely to get times of

0.094000
10.203000
0.110000

or

0.094000
0.110000
10.203000

It's driving me crazy! I think it must be something to do with the DB too. This is annoying as there's nothing I can do about it.

 

[ZoneVM]

Does the same thing happen if you run the query from the commany line?

 

[BorlandDave]

Erm... I'm not sure how to run the queries from the command line, my knowledge of working with DBs is limited. However, if I run the queries from within IBConsole (basically a database viewer supplied by Interbase), the speeds are consistantly quick. I can't time them exactly, but they are a fraction of a second.

 

[ZoneVM]

yes, that's what I meant, the sqlplus (oracle) or the isql (firebird) or whatever connection utility the database offers. I only ask because if the query is consistently fast here, the delay may be in getting the connection and not in running the query. You might try to time different parts of your function and see if that's the case. If it is the case, you might consider some sort of connection pooling to alleviate the situation, whereby all connections are made up front and doled out the the application as needed.

Hope this helps.

 

[BorlandDave]

Thanks for the ideas ZoneVM!

I took your advise and calculated some more times throughout the function and guess what...

The part of the program taking all the time is the disconnection of the database! I took times either side of the line: "InterBaseDB->Connected = false;", and that is the culprit.

Any further ideas on how to cure this?

 

[BorlandDave]

Hmm... It's not looking good...

http://groups.google.co.uk/group/bo...38918284c22/4cb63e8ea10ecc97#4cb63e8ea10ecc97

 

[BorlandDave]

...Some more links...

http://groups.google.co.uk/groups?q...illa:en-US:official_s&sa=X&oi=groups&ct=title

 

[2ffat]

Doesn't sound too promising. I wonder what the ThunderBird DB does since it's based upon an ealier version of Interbase?


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[ZoneVM]

Weird. I wouldn't have guessed the disconnect was the culprit. The only easy thing I can think of to remedy it (other than what's in the posts you linked) would be to keep the connection throughout the app, and only disconnect on app shutdown. Put your connection on a data module, and use the same connection for all your queries. Hope this helps.

 

[BorlandDave]

Yes that is one idea. The only thing holding me back is the knowledge that my app is likely to be running continuously for weeks on end, possibly even months and years. Holding a database connection open for that length of time would be a bit worrying.

 

[2ffat]

Maybe a timer that would disconnect once a day (during a period of slow activity) and reconnect just to refresh things abit. Just a thought.



James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]