🚀 go-pugleaf

"Heinz Ketchup" 
<heinz_like_the_ketchup@h0tmail.n0spam.c0m.n0spam> wrote in 
message news:42c1a5b2$1@newsgroups.borland.com...
>
> Hello to Everyone,
>
> I've been googling, trying to find different advice pages on 
> suggestions to increase the speed of Querying / Updating. Some 
> of them have been quite helpful.
>
> Such as Setting up your Cursor for ctStatic; Cursor Location 
> clUseClient or clUseServer.
>
> Now I've managed to reduce the query time of 5000 records from 
> 4.5 minutes down to 1 minute... Yeah, I said 5000 records...

That's horrible.  What's your configuration?  Client, server, 
network?  What values for which properties did you end up with? 
If you simply want to SELECT and read through a result set, I'd 
suggest the following:

clUseServer
ctOpenForwardOnly
ltReadOnly
CacheSize = 1000   // This can be a big one

Note that if a bunch of people are querying the server, 
clUseServer can use a fair amount of server resources.  On the 
other hand, clUseClient will read the entire result set onto your 
client before you get control back.  This can severely punish a 
'light' client machine.

>
> I have problems updating 1536 records in a decent amount of 
> time... Right now it takes 3.25 minutes to update/insert 
> records into the database.
>

Also horrible.  Does the server have enough RAM to work 
efficiently with excessive swapping?  Are you doing this inside 
of a transaction bracket?  How many indices are on the table in 
question?

> We are using a Sybase Database through an ODBC Connection.
>
> Personally, I don't understand why querying and updating is so 
> ridiculously slow. Different programs we have updating our DB 
> are practically instantaneous.

Personally, I have no experience with SyBase.  We use SQL Server 
2000, IBM DB2 and Oracle 8/9/10.  We have some heavy duty console 
apps.  They cache some columns in a std::map for in-memory lookup 
speed up front.  We can typically read 1.25 million rows of 
smallint, int, varchar(20) and datetime in under 10 seconds. 
That's either on the server itself or across a 100 MB LAN to a 
P4/3GHZ client.

>
> My method for Updating are based around using the
> SQL Exec Method.
>
> //SQL Query Method
> AssignAndQueryDateParameters(TADOQuery* QueryPointer, TDateTime 
> SQLStartDate, TDateTime SQLEndDate)
> {
>   // Perform SQL Operations
>   QueryPointer->Close();
>
>   QueryPointer->Parameters->ParamByName("StartDate")->Value = 
> SQLStartDate;
>   QueryPointer->Parameters->ParamByName("EndDate")->Value = 
> SQLEndDate;
>
>   QueryPointer->DisableControls();

I've never seen much in the way of a performance increase for 
DisbleControls().  BCB5

>   QueryPointer->Open();
> }
>
> // Used for Updating / Inserting
> // SQL Statement String has the SQL Statement with Value for
> // Updating / Inserting
> ExecuteADOMethodSQLStatement(TADOQuery* QueryPointer, 
> AnsiString SQLStatementString)
> {
>   QueryPointer->Close();
>   QueryPointer->SQL->Clear();
>
>   QueryPointer->SQL->Text = SQLStatementString;
>
>   QueryPointer->ExecSQL();
> }

Hmmm.  No parameters?  Always use parameterized statements.  This 
allows a (decent) server to parse the query and to save it and 
the calculated query plan in its cache.  In other words, a 
statement like:

UPDATE Foo SET X = 22 WHERE Y = 44

will have to be reparsed for each execution.  Whereas, a 
statement like:

UPDATE Foo SET X = :x WHERE Y = :y

will NOT be reparsed.  Of course, this goes for all statement 
types.

>
>
> Does anyone, have any suggestions on how I can increase the 
> speed of the updates? Each Query / Update is executed 
> individually. The SQL Where clause is quite specific. Hence, 
> individual updates.
>
> Oh yes, there are NO DB Visual components of any sort in the 
> application being built.
>
> I'm open to anything that will speed the DB process up.
> Query or Update.
>
> I can tolerate the slow query, but the update I can't. Anything 
> more than a minute is painful.
>
> Again, Any and All Suggestions / Feedback are greatly 
> Appreciated!!!!!

Get a faster client or server?

>
> Thanks,
> Heinz

I'll see you on my burger tonight ;-)

- Arnie