🚀 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 

Thanks Arnie,

I'm working off of 2 different servers. One of them is in our office area, and another is at a furthur remote site. Both of them are being connected through a network.

Both my TDataModules Use Single Connection's (New / Deleted) when a Query Date is Changed. 

The TADOConnection is clClient; cmReadWrite. Oddly, I find clClient to be faster than clServer. No clue? (That's just for Connection Only)

All the TADOQuery's and TADODataSet's have clUseServer; Lock Type = ltReadOnly; ctStatic. That helps out quite a bit. I found no difference in speed (with the exception of ctKeyset and ctDynamic... make things crawl)

As for people Querying the Server's, it's on the low end. So basically, just "us".

As far as I know in Hardware Setup, I know nothing. Supposedly one of the Servers is New... so I'm assuming it's good. 20K. As for the other one... ???

Each of the Tables have 3 indicies / 3 primary keys.
Yes I know the difference. =)

I'll definitely look into the Parameterized Statements! I didn't know it was that useful. It's just basically me around here, and I'm going with what I know or research.

Thanks a bundle... Hopefully all your suggestions work out. Which I'm sure they will! I will try them out.

And as for the Burger comment... Good to see some people have a sense of humour.

Thanks,
Heinz... <- The non-eddable kind




"Arnie" <none> wrote:
>"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 
>
>