Re: [firebird-support] Generator Performance
Hello Matheus, Generator values are stored on generator pages. In shared cache architectures (i.e. SuperServer) a connection that changes a generator value gets a transient write lock on the page, makes its change and releases the page. This page is the mapping of a set of table records into a memory/disk page, right? Right. Everything stored in the database, data, metadata, and structural data, is stored on fixed sized pages. Pages come in different types: data, blob, index, generator, page inventory, transaction inventory, header, index root, and pointer. A generator page consists of a standard page header (~20 bytes) plus an array of 64 bit integers. Every page in the database is the same size, either 4, 8, or 16Kb. So, if I had a large number of generators, for workloads which accessed generators on different pages (say, generator ID 10 and generator ID 4000) there wouldn't be contention, right? Depending on the page size, right. Is there a way of finding out how many pages are used to store a given table? Or documentation about how the mapping from tables to memory and disk pages are implemented on firebird? Pretty simple. If you care about detail, look at ods.h for the size of the PAG structure and the exact layout of a generator page. For a rough approximation of the number of generators on a page, divide the page size by 8. Note that this algorithm works ONLY for generators which are not normal table data. Figuring out how many data records fit on a page is an advanced exercise. Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] How does the optimizer factor in views?
I have the following view: CREATE VIEW as example AS SELECT col1 FROM table1 INNER JOIN table2 USING(col1); Then when I use it: SELECT a.col1, a.col2, a.col3 FROM table1 AS a INNER JOIN example USING (col1); Is the optimizer smart enough to figure out that it can join table1 directly to table 2 and get col1, col2, col3? Or does it process the view first and then join it to table1 (redundant) and get col1, col2, col3? Is it possible to display a detailed execution plan?
[firebird-support] Delete Performance Issue
I am using FB 1.5 and doing a delete from a large table using the primary key as a reference from a sub select delete from tableA where ID in (select ID from tableB where someval = 'x') The subquery is fast and looks like it uses the index. But the plan for the delete is (ID NATURAL) and take a minute to run. Other then creating a stored procedure to do this, how can I improve the query? -- Thanks, Gordon
Re: [firebird-support] How does the optimizer factor in views?
On Mon, Sep 26, 2011 at 12:06 PM, firebirdsql firebird...@yahoo.com wrote: I have the following view: CREATE VIEW as example AS SELECT col1 FROM table1 INNER JOIN table2 USING(col1); Then when I use it: SELECT a.col1, a.col2, a.col3 FROM table1 AS a INNER JOIN example USING (col1); Is the optimizer smart enough to figure out that it can join table1 directly to table 2 and get col1, col2, col3? Or does it process the view first and then join it to table1 (redundant) and get col1, col2, col3? In this case yes, but some views must be instantiated first, so the general case is more complicated. Is it possible to display a detailed execution plan? With isql, you can say set plan, or set plan only. Good luck, Ann ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
