Board index » delphi » URGENT! Still have SERIOUS speed problems!

URGENT! Still have SERIOUS speed problems!

We have an URGENT need to increase the performance of our database access.
We have a table with a couple thousand records of about 100 fields each,
with a composite index consisting of a single character string field
(Status) and a 7-digit string field (OrderNumber).  We set a range on the
status character field, and loop through until EOF, changing the status of
each record (which obviously moves the record, so we don't do a Next).
  On a Novell network, with a Win95 running Novell Intranetware client, it
takes ONE MINUTE to process ONE RECORD!!!  Even on a win95 box with
Microsoft Client for Netware, it takes 6-7 seconds per record.  We have made
every BDE and network modification that has been advised, and we have only
one user running the app at the time.  We have totally run out of ideas for
speeding this thing up.
  (Our win95 boxes are HP Vectras, at 233Mhz I believe, with 64M of RAM and
loads of disk space.)  Can someone PLEASE help us?!?
Thanks...
-Howard Moon
 

Re:URGENT! Still have SERIOUS speed problems!


Quote
> We have an URGENT need to increase the performance of our database
> access.
> We have a table with a couple thousand records of about 100 fields
> each,
> with a composite index consisting of a single character string field
> (Status) and a 7-digit string field (OrderNumber).  We set a range on
> the
> status character field, and loop through until EOF, changing the
> status of
> each record (which obviously moves the record, so we don't do a Next).

>   On a Novell network, with a Win95 running Novell Intranetware
> client, it
> takes ONE MINUTE to process ONE RECORD!!!  Even on a win95 box with
> Microsoft Client for Netware, it takes 6-7 seconds per record.  We
> have made
> every BDE and network modification that has been advised, and we have
> only
> one user running the app at the time.  We have totally run out of
> ideas for
> speeding this thing up.
>   (Our win95 boxes are HP Vectras, at 233Mhz I believe, with 64M of
> RAM and
> loads of disk space.)  Can someone PLEASE help us?!?
> Thanks...
> -Howard Moon

Is there the need to work like this? A normalization could not improve
the things.

Marcelo Muzilli

Re:URGENT! Still have SERIOUS speed problems!


maybe the indexes have been {*word*30}ed up from some reson, it dose happand
sometimes, try to rebuild them.
are you using paradox tables ?
if it wont make it better I suggest that you send us the code of this function
that make this loop and all the other functions that are being called during
this proccess. if for example you would have done the following :

while not Table1.EOF do
begin
  for i:=0 to Table1.FieldsCount-1 do
    begin
      Table1.Fields[i].AsString:='0';
    end;
  Table1.Next;
end;

if this wont take more then one second per record, so there must be something
wrong there in the code.

Quote
Howard Moon wrote:
> We have an URGENT need to increase the performance of our database access.
> We have a table with a couple thousand records of about 100 fields each,
> with a composite index consisting of a single character string field
> (Status) and a 7-digit string field (OrderNumber).  We set a range on the
> status character field, and loop through until EOF, changing the status of
> each record (which obviously moves the record, so we don't do a Next).
>   On a Novell network, with a Win95 running Novell Intranetware client, it
> takes ONE MINUTE to process ONE RECORD!!!  Even on a win95 box with
> Microsoft Client for Netware, it takes 6-7 seconds per record.  We have made
> every BDE and network modification that has been advised, and we have only
> one user running the app at the time.  We have totally run out of ideas for
> speeding this thing up.
>   (Our win95 boxes are HP Vectras, at 233Mhz I believe, with 64M of RAM and
> loads of disk space.)  Can someone PLEASE help us?!?
> Thanks...
> -Howard Moon

Re:URGENT! Still have SERIOUS speed problems!


I do A LOT of processing on data this way. The key structure is different but
the concept is the same...I iterate through all the records.  In each record I
must change a field's value.  One or more indexes use this field's value.  The
tables are Paradox (fitting name for an Inprise product) tables.  What I ended
up doing is using a TTable component exclusivly for this process.  I also set up

a buffer so only the records that were changed were written to disk.  However,
I only had about 2000 records.  My processing time went from 2 minutes to
less than 40 seconds.  Using a dedicated TTable component meant that I
didn't need to worry about data-aware controls be refreshed each time the
record changed (no need for DisableControls/EnableControls).

Hope-This-Helps

Quote
Howard Moon wrote:
> We have an URGENT need to increase the performance of our database access.
> We have a table with a couple thousand records of about 100 fields each,
> with a composite index consisting of a single character string field
> (Status) and a 7-digit string field (OrderNumber).  We set a range on the
> status character field, and loop through until EOF, changing the status of
> each record (which obviously moves the record, so we don't do a Next).
>   On a Novell network, with a Win95 running Novell Intranetware client, it
> takes ONE MINUTE to process ONE RECORD!!!  Even on a win95 box with
> Microsoft Client for Netware, it takes 6-7 seconds per record.  We have made
> every BDE and network modification that has been advised, and we have only
> one user running the app at the time.  We have totally run out of ideas for
> speeding this thing up.
>   (Our win95 boxes are HP Vectras, at 233Mhz I believe, with 64M of RAM and
> loads of disk space.)  Can someone PLEASE help us?!?
> Thanks...
> -Howard Moon

Re:URGENT! Still have SERIOUS speed problems!


Could you not use an UPDATE query? That would seem like the most
efficient change you can make.

Woody

Quote
Howard Moon wrote in message <72c401$7...@forums.borland.com>...
>We have an URGENT need to increase the performance of our database
access.
>We have a table with a couple thousand records of about 100 fields
each,
>with a composite index consisting of a single character string field
>(Status) and a 7-digit string field (OrderNumber).  We set a range on
the
>status character field, and loop through until EOF, changing the
status of
>each record (which obviously moves the record, so we don't do a
Next).
>  On a Novell network, with a Win95 running Novell Intranetware
client, it
>takes ONE MINUTE to process ONE RECORD!!!  Even on a win95 box with
>Microsoft Client for Netware, it takes 6-7 seconds per record.  We
have made
>every BDE and network modification that has been advised, and we have
only
>one user running the app at the time.  We have totally run out of
ideas for
>speeding this thing up.
>  (Our win95 boxes are HP Vectras, at 233Mhz I believe, with 64M of
RAM and
>loads of disk space.)  Can someone PLEASE help us?!?
>Thanks...
>-Howard Moon

Re:URGENT! Still have SERIOUS speed problems!


Have you tried going through the table using an index that does not include
the field(s) you are changing?

Quote
Howard Moon wrote in message <72c401$7...@forums.borland.com>...
>We have an URGENT need to increase the performance of our database access.
>We have a table with a couple thousand records of about 100 fields each,
>with a composite index consisting of a single character string field
>(Status) and a 7-digit string field (OrderNumber).  We set a range on the
>status character field, and loop through until EOF, changing the status of
>each record (which obviously moves the record, so we don't do a Next).
>  On a Novell network, with a Win95 running Novell Intranetware client, it
>takes ONE MINUTE to process ONE RECORD!!!  Even on a win95 box with
>Microsoft Client for Netware, it takes 6-7 seconds per record.  We have
made
>every BDE and network modification that has been advised, and we have only
>one user running the app at the time.  We have totally run out of ideas for
>speeding this thing up.
>  (Our win95 boxes are HP Vectras, at 233Mhz I believe, with 64M of RAM and
>loads of disk space.)  Can someone PLEASE help us?!?
>Thanks...
>-Howard Moon

Re:URGENT! Still have SERIOUS speed problems!


Quote
Woody wrote in message <72cfpj$7s...@forums.borland.com>...
>Could you not use an UPDATE query? That would seem like the most
>efficient change you can make.

>Woody

Woody,
  thanks, but when we used to use an update query, we only got slightly
better performance, and since we were not in a loop but in an ExecSQL call,
we could not call ProcessMessages, and the users thought our software had
locked up, so they would kill it and call our tech support to complain.
-Howard

Re:URGENT! Still have SERIOUS speed problems!


Quote
Eyal Wilde wrote in message <3649D367.9E9CB...@impactsoft.co.il>...
>maybe the indexes have been {*word*30}ed up from some reson, it dose happand
>sometimes, try to rebuild them.
>are you using paradox tables ?
>if it wont make it better I suggest that you send us the code of this
function
>that make this loop and all the other functions that are being called
during
>this proccess. if for example you would have done the following :

>while not Table1.EOF do
>begin
>  for i:=0 to Table1.FieldsCount-1 do
>    begin
>      Table1.Fields[i].AsString:='0';
>    end;
>  Table1.Next;
>end;

>if this wont take more then one second per record, so there must be
something
>wrong there in the code.

1) We have tried packing the tables, and rebuilding them.  No change.

2) Your code is pretty close to ours.  here is what we do (with fake names):
with thetable do
begin
  setrangestart;
  fieldbyname('status').asstring := 'w';
  setrangeend;
  fieldbyname('status').asstring := 'z'; // get 'w' through 'y'
  applyrange;
  first;
  while not eof do
  begin
    edit;
    fieldbyname('status').asstring := 't';
    // changes index, so 'next' not needed
    post;
    application.processmessages;
  end;
  cancelrange;
end;

Simple, eh?  Works really fast on a stand-alone setup, but very slow on a
Novell network.  Without the ProcessMessages, users think the app has locked
up, and kill it. Any ideas?
-Howard

Re:URGENT! Still have SERIOUS speed problems!


Howard Moon <hm...@landstar.com> wrote in article
<72f7ab$c6...@forums.borland.com>...

<snip>

Quote
>   while not eof do
>   begin
>     edit;
>     fieldbyname('status').asstring := 't';
>     // changes index, so 'next' not needed
>     post;
>     application.processmessages;
>   end;
>   cancelrange;
> end;

Try this while loop

   Edit;
   while not EOF do
   begin
     FieldByName('status').asstring := 't';
     UpdateRecord;
     Application.ProcessMessages;
   end;

You might also want to try persistent fields. They can be MUCH faster than
using FieldByName.

        -- Kirk

Re:URGENT! Still have SERIOUS speed problems!


I have no explanation for this but I had 2 pcs networked 900 records
and simple count going from1st to the last record
tble.First
with table
if something=somethinelse then count:=count+1
Next
everything looping in while EOF

It was taking 50 sec.  I was testing it for 3 days..finlly by accident
I moved my table that I was making  a search on from a datamodule to a
form that was using it and the time went down to a second.. how this
can be explained? if anyone has any clue ...I would greatly appreciate
for any comments..

Paul

Re:URGENT! Still have SERIOUS speed problems!


Per newsgroup guidelines, binary attachments are prohibited.

sig://boB/TeamB

Re:URGENT! Still have SERIOUS speed problems!


In my situation the answer is:  Lookup fields defined on the table
level!!!

On Fri, 13 Nov 1998 02:07:06 GMT, REMOVEfo...@technologist.com (Paul)
wrote:

Quote
>I have no explanation for this but I had 2 pcs networked 900 records
>and simple count going from1st to the last record
>tble.First
>with table
>if something=somethinelse then count:=count+1
>Next
>everything looping in while EOF

>It was taking 50 sec.  I was testing it for 3 days..finlly by accident
>I moved my table that I was making  a search on from a datamodule to a
>form that was using it and the time went down to a second.. how this
>can be explained? if anyone has any clue ...I would greatly appreciate
>for any comments..

>Paul

Re:URGENT! Still have SERIOUS speed problems!


Quote
Bruce Roberts wrote in message <72d9fo$a...@forums.borland.com>...
>Have you tried going through the table using an index that does not include
>the field(s) you are changing?

Thanks, Bruce, but we can't do that.  We have a range set on the table based
on this character, and it is our only index.  Removing the range would only
mean we had to go through every record, which would significantly degrade
the performance on platforms where this problem is not seen.  As I said, it
is only performing poorly on Novell networks, not on NT networks or on
stand-alone PCs.
Thanks though...
-Howard

Re:URGENT! Still have SERIOUS speed problems!


Quote
kroma wrote in message <01be0e68$8300adc0$78029...@damnykm2.un.org>...

>You might also want to try persistent fields. They can be MUCH faster than
>using FieldByName.

> -- Kirk

We are using persistent fields now, but no significant change.
-Howard

Re:URGENT! Still have SERIOUS speed problems!


Quote
Eyal Wilde wrote in message <364B4F8A.EEA6...@impactsoft.co.il>...
>it is very strange but :
>1. I'm asking again - is it a paradox table ?
>2. I did not understand from your answer if you have earsed the indexes ?
>packing the table is something else.
>3. want happan if you do the same but updating NON index field ? if it runs
fast
>then there is probably something wrong with the indexes files.
>4. how many indexes do you have ? if for exsample you have index for 20
fields
>then all those indexe files can be modyfied evry time you change an indexed
>field.
>5. did you try to rebuild the table with 'TableFix.exe' ? if not try this
with
>the attached file but - do the on copied Table !!!! this can also unfixed
the
>table !
>6. in any case, all these kid of experiments you have better do on copied
table,
>not the original.

1. Yes, it is a Paradox table
2. We have not erased it, but have tried rebuilding, restructuring, and
packing.  None helped.  What do you mean by erasing it?  We need the data it
contains.
3. We HAVE to do what we are doing.  We need to index on this field.  We
have checked the indexes, and they are fine.  This problem only happens on
the Novell setup I mentioned.  On a stand-alone or NT network, everything is
fine.
4. This is the only index, consisting of the status field (on character) and
the order number (7 characters).
5. Can't pass binary files on this newsgroup.  As I said, we have rebuilt,
restructured, and checked the tables repeatedly.
Thanks for the help, though...
-Howard
Go to page: [1] [2]

Other Threads