Hi,

Pleas show us

gstat -h
and plan for this query

I see 4 possibilites but can be more...
1. Long running transaction
2. Hard drive i to slow ( many operations on it)
3. Network problem try ping
4. You youse big amount of transaction - look at readonly transaction by it you can check if you have some data and you do not need to commit it


I have table with 300 000 000 and time to lookup record by pk took few ms.

Remember that if you do only select and start new transaction for it you have min to save to HDD

Regards,
Karol Bieniaszewski

----- Reply message -----
Od: "trskopo" <trskopo@...>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Re: How to improve Firebird performance on a large table?
Data: pon., mar 4, 2013 08:50
Dear Alan,



Thanks for your reply.



This is my table ddl :

CREATE TABLE LOG

(

ID Integer NOT NULL,

ID_TBL Integer NOT NULL,

TBL_NM Varchar(40) NOT NULL,

OPR Char(1) NOT NULL,

LOG_AT Timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,

SNC Smallint DEFAULT 0,

PRIMARY KEY (ID)

);



This table is used to record changes from other tables in same database and then make a sync to sync database in the same network.



I have to two databases in the network, DbaA and DbaB.

DbaA is the database that has log table. DbaB is the sync database.



When there are changes in tables in DbaA, the id of these tables recorded in log table.



There is an app that regularly check log table to see the value of snc field. If the value is 0, then, the app do the sync and then set the snc's field value to 1.



So, in this case, network traffic is really high.

When records in log table still only a few thousands, there is no problem with performance, but now even I do select with :



Select id from log where snc = 0 or

Select first 50 id from log where snc = 0 or



still took about 20 sec. So, in this case, is there any thing that I can do to improve performance?



Thanks and regards,

Anto.

--- In firebird-support@yahoogroups.com, Alan J Davies wrote:

>

> Without knowing your setup, its difficult to say. You may have 100

> fields in each record? In a network environment try to minimise the

> amount of network traffic.

> So, you should try to refine your select statement by returning only

> what you need e.g.

> instead of >Select * from table where field_value = 1.

> try:

> select field_value,field_value1,field_value2,field_value100

> from table

> where field_value = 1 (and field_value2=2)

> order by field_value,field_value2 (aim for more selective index - 2

> fields, not just 1)

> then even better:

> What do you want to do with the returned data? View it? Can you handle

> more than 20 records at a time on your screen?

> select first 20 field_value,field_value1,field_value2,field_value100

> from table

> where field_value = 1 (and field_value2=2)

> order by field_value,field_value2

>

> Good luck

> Alan

>

> Alan J Davies

> Aldis

>

>

> On 04/03/2013 06:18, trskopo wrote:

> > Hi all,

> >

> > I have a table with about 5,000,000 records and it's growing.

> >

> > I run slq statement like this :

> >

> > Select * from table where field_value = 1.

> >

> > Table already indexed on field_value (int type).

> >

> > On local hardisk, where there is only 1 user and no update/edit/insert

> > operation, it took about 1-2 seconds to get the result, but on Local

> > Area Network, where there about 20 users attached to that database, lots

> > of update/edit/insert operation on this table,it took almost 20 seconds.

> >

> > Is there any tips / methods to improve performance?

> >

> > Thanks and regards,

> > Anto.

> >

> >

>











[Non-text portions of this message have been removed]