Board index » delphi » Strategies to improve performance?

Strategies to improve performance?

Hi Group!

I am "playing" around a little bit with Interbase to find out
limitations and behaviour using a PII 400MHz on NT4.0 Workstation SP6

I generated a database which at the moment holds a table with 5
million records.

Inserting works nice, I get something like 750 Records per second.

Getting the recourd count with SELECT COUNT(*)  seems to take very
long. I messured 144 seconds on this one :-(

Using a DBTable on a query/datasource in combination with a
DBNavigator , pressening nbLast  -> I was not able to await this :-((

Are there any good strategies on what to do and what not to do on
huges tables? What would be the fastest way to get the "RecordCount"?

Are there links on www that cover this subject?

Thanks for any hints, links and comments

Rainer

 

Re:Strategies to improve performance?


You should use IBX components or IBObjects components and not BDE
components.

Also never use TTable components - use Query components.

Quote
"Rainer Queck" <Rainer.Qu...@t-online.de> wrote in message

news:3beb9566.6013136@newsgroups.borland.com...
Quote
> Hi Group!

> I am "playing" around a little bit with Interbase to find out
> limitations and behaviour using a PII 400MHz on NT4.0 Workstation SP6

> I generated a database which at the moment holds a table with 5
> million records.

> Inserting works nice, I get something like 750 Records per second.

> Getting the recourd count with SELECT COUNT(*)  seems to take very
> long. I messured 144 seconds on this one :-(

> Using a DBTable on a query/datasource in combination with a
> DBNavigator , pressening nbLast  -> I was not able to await this :-((

> Are there any good strategies on what to do and what not to do on
> huges tables? What would be the fastest way to get the "RecordCount"?

> Are there links on www that cover this subject?

> Thanks for any hints, links and comments

> Rainer

Re:Strategies to improve performance?


Thanks for the hints.
I am using IBX components and I am not using TTable.

On Fri, 9 Nov 2001 10:55:20 +0200, "Ruaan Barnard"

Quote
<ru...@nospam.ccs-software.co.za> wrote:
>You should use IBX components or IBObjects components and not BDE
>components.

>Also never use TTable components - use Query components.
>"Rainer Queck" <Rainer.Qu...@t-online.de> wrote in message
>news:3beb9566.6013136@newsgroups.borland.com...
>> Hi Group!

>> I am "playing" around a little bit with Interbase to find out
>> limitations and behaviour using a PII 400MHz on NT4.0 Workstation SP6

>> I generated a database which at the moment holds a table with 5
>> million records.

>> Inserting works nice, I get something like 750 Records per second.

>> Getting the recourd count with SELECT COUNT(*)  seems to take very
>> long. I messured 144 seconds on this one :-(

>> Using a DBTable on a query/datasource in combination with a
>> DBNavigator , pressening nbLast  -> I was not able to await this :-((

>> Are there any good strategies on what to do and what not to do on
>> huges tables? What would be the fastest way to get the "RecordCount"?

>> Are there links on www that cover this subject?

>> Thanks for any hints, links and comments

>> Rainer

Re:Strategies to improve performance?


By the way - recordcount on 5 million entries will take a bit of time. The
faster your processor the better Interbase will run.
Quote
"Rainer Queck" <Rainer.Qu...@t-online.de> wrote in message

news:3beba04a.8800905@newsgroups.borland.com...
Quote
> Thanks for the hints.
> I am using IBX components and I am not using TTable.

> On Fri, 9 Nov 2001 10:55:20 +0200, "Ruaan Barnard"
> <ru...@nospam.ccs-software.co.za> wrote:

> >You should use IBX components or IBObjects components and not BDE
> >components.

> >Also never use TTable components - use Query components.
> >"Rainer Queck" <Rainer.Qu...@t-online.de> wrote in message
> >news:3beb9566.6013136@newsgroups.borland.com...
> >> Hi Group!

> >> I am "playing" around a little bit with Interbase to find out
> >> limitations and behaviour using a PII 400MHz on NT4.0 Workstation SP6

> >> I generated a database which at the moment holds a table with 5
> >> million records.

> >> Inserting works nice, I get something like 750 Records per second.

> >> Getting the recourd count with SELECT COUNT(*)  seems to take very
> >> long. I messured 144 seconds on this one :-(

> >> Using a DBTable on a query/datasource in combination with a
> >> DBNavigator , pressening nbLast  -> I was not able to await this :-((

> >> Are there any good strategies on what to do and what not to do on
> >> huges tables? What would be the fastest way to get the "RecordCount"?

> >> Are there links on www that cover this subject?

> >> Thanks for any hints, links and comments

> >> Rainer

Re:Strategies to improve performance?


If it is a recordcount for only one table and the SQL will stay the same
then setup a StoredProc to return the recordcount for you
otherwise COUNT is your only other solution.
Also remember that when you do the insert's you should do a hard commit
every 400-500 entries.
Quote
"Rainer Queck" <Rainer.Qu...@t-online.de> wrote in message

news:3beba04a.8800905@newsgroups.borland.com...
Quote
> Thanks for the hints.
> I am using IBX components and I am not using TTable.

> On Fri, 9 Nov 2001 10:55:20 +0200, "Ruaan Barnard"
> <ru...@nospam.ccs-software.co.za> wrote:

> >You should use IBX components or IBObjects components and not BDE
> >components.

> >Also never use TTable components - use Query components.
> >"Rainer Queck" <Rainer.Qu...@t-online.de> wrote in message
> >news:3beb9566.6013136@newsgroups.borland.com...
> >> Hi Group!

> >> I am "playing" around a little bit with Interbase to find out
> >> limitations and behaviour using a PII 400MHz on NT4.0 Workstation SP6

> >> I generated a database which at the moment holds a table with 5
> >> million records.

> >> Inserting works nice, I get something like 750 Records per second.

> >> Getting the recourd count with SELECT COUNT(*)  seems to take very
> >> long. I messured 144 seconds on this one :-(

> >> Using a DBTable on a query/datasource in combination with a
> >> DBNavigator , pressening nbLast  -> I was not able to await this :-((

> >> Are there any good strategies on what to do and what not to do on
> >> huges tables? What would be the fastest way to get the "RecordCount"?

> >> Are there links on www that cover this subject?

> >> Thanks for any hints, links and comments

> >> Rainer

Re:Strategies to improve performance?


Set the cache size for the database to 10,000 pages. SELECT COUNT(*) will
never be fast because the server has to scan the records to count them. If
you need the count frequently for some strange reason you might consider
maintaining a counter using after insert and after delete triggers and a
generator.

In a client/server application with any database, not just InterBase, you
should never use a table component or a query that selects all rows and
allow the user to browse the data. SQL database servers are designed to let
the user select a small set of records (no more than a couple of hundred),
work with those then select another set of records.

--
Bill
(TeamB cannot answer questions received via email)

Re:Strategies to improve performance?


Bill,

Quote
>Set the cache size for the database to 10,000 pages.

How does one do that?

Quote
>SELECT COUNT(*) will never be fast because the server has

to scan the >records to count them.

This behavior has always amused me. In an environment where
primary keys are so strongly used, I've never understood why
count(*) didn't count the entries in the primary index, if
one exists.(Interbase is not the only RDB that counts the
rows)

Phil Cain

Re:Strategies to improve performance?


gfix -buffers 10000 -user sysdba -password masterkey employee.gdb

--
Bill
(TeamB cannot answer questions received via email)

Re:Strategies to improve performance?


Quote
> This behavior has always amused me. In an environment where
> primary keys are so strongly used, I've never understood why
> count(*) didn't count the entries in the primary index, if
> one exists.(Interbase is not the only RDB that counts the
> rows)

> Phil Cain

I think it has to do with Interbase's multi-generational architecture.
Every record (even ones that are deleted but still visible to active
transactions) will have an active index entry.  The count function has to
actually look at the physical record to determine if it's deleted or not in
the current transaction context.  This is why sweeping and garbage
collection (and short transactions) can make a big difference on
performance.

Dan

Re:Strategies to improve performance?


Quote
Rainer Queck wrote:

> Getting the recourd count with SELECT COUNT(*)  seems to take very
> long. I messured 144 seconds on this one :-(

        My advice is whenever possible don't do record counts.  The very notion
of a record count is something of a fiction in a multiuser environment,
particularly if your transaction isolation mode is not SNAPSHOT.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Re:Strategies to improve performance?


The only way is with gfix? No GUI or meta command for this?

Phil

Re:Strategies to improve performance?


Thanks,  Dan. That's interesting.

Phil Cain

Re:Strategies to improve performance?


gfix is the only way that I know of.

--
Bill
(TeamB cannot answer questions received via email)

Re:Strategies to improve performance?


"Bill Todd (TeamB)" <bill.nos...@dbginc.com> wrote in message
news:3bebe546$1_1@dnews...

Quote
> Set the cache size for the database to 10,000 pages. SELECT COUNT(*) will
> never be fast because the server has to scan the records to count them. If
> you need the count frequently for some strange reason you might consider
> maintaining a counter using after insert and after delete triggers and a
> generator.

Depending on your usage pattern, this is a very bad idea. Generators are not
affected by rollback so your count would be a lousy approximation at best.

Quote
> In a client/server application with any database, not just InterBase, you
> should never use a table component or a query that selects all rows and
> allow the user to browse the data. SQL database servers are designed to
let
> the user select a small set of records (no more than a couple of hundred),
> work with those then select another set of records.

What is very nice is when you have your cake and you get to eat it too.

IBO allows you to call Last and it virtualizes your window of the dataset to
just the portion you are interested in. IBO has built into it the smarts of
how to refine the records brought back to the client. Some tuning needs and
limitations exist but it is very powerful stuff.

This works for bookmark seeks, locates (even on columns other than the ones
ordered by), etc.

In short, if you know how to setup your database and know IBO you can have
just about any usage pattern you want and still remain efficient as Bill is
encouraging you to.

Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

Re:Strategies to improve performance?


Quote
> If it is a recordcount for only one table and the SQL will stay the same
> then setup a StoredProc to return the recordcount for you
> otherwise COUNT is your only other solution.

Unless he actually maintains a special table for storing counts that the
stored procedure would return, I fail to see how this could be of any
benefit. Also, knowing how to maintain statistical aggregations and avoiding
deadlocks or inaccuracies is not trivial.

Quote
> Also remember that when you do the insert's you should do a hard commit
> every 400-500 entries.

This is a myth.

Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

Go to page: [1] [2]

Other Threads