Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[DougC d...@moosemail.net [firebird-support]]

 On Fri, 04 May 2018 14:10:40 -0400 blackfalconsoftw...@outlook.com 
[firebird-support] firebird-support@yahoogroups.com wrote 



  I am hoping to begin writing a technical article soon that will introduce 
database application developers to these idiosyncrasies who are new to 
Firebird, making their initial forays into using this database engine less 
frustrating.



I have always believed that these types of issues have kept Firebird from being 
realized as a top-contender in the database world.  It is based upon an 
excellent technology foundation and should be exposed more generally for the 
excellent and highly efficient engine that it really is...








I hope you follow through on this, Steve! I agree with your observations.



Doug C.

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2018-05-04 20:10, blackfalconsoftw...@outlook.com [firebird-support] 
wrote:
> I must admit that I have spent many years working with various
> database engines, with SQL Server being the engine where the large
> majority of my professional development had been emphasized.
> Nonetheless, I have worked with Oracle, Sybase, MySQL, and a little
> PostgreSQL and SQLite.

In my experience, each and every database engine has their oddities :)

[..]

> I believe I am finally getting the hang of it.  However, one of the
> areas that I am finding very confusing is the inconsistencies between
> using raw PSQL code in a DB Manager and using it with the Firebird
> ADO.NET Provider that one of your project's third-party contributors
> provides.

PSQL (or procedureal SQL) is specifically the syntax you use in stored 
procedures, execute block, stored functions and triggers. It is not the 
syntax that you use when executing queries in your database manager or 
the Firebird ADO.net driver. That syntax is called DSQL (dynamic SQL). 
This distinction is important, because some things you can do in PSQL 
are not possible in DSQL (and vice versa).

> For example, if I want to run a "selectable" stored procedure in my DB
> Manager, I enter the following for example...
> 
>SELECT * FROM MY_PROCEUDRE_NAME (PARAM)
> 
> However, if you do the same using my own data-access-layer, the
> provider yields a parameter mismatch error.  There is a very specific
> reason for this.

I'm not entirely sure what you mean with this.

> I have written data access layers for a number of database engines,
> including Oracle and PostgreSQL, along with Firebird, which was the
> most recent layer I released.  In all of layers when SQL code is
> passed to a query method, I validate for whether there is a "SELECT"
> statement present.  If so, than the layer determines that is what is
> being passed is raw SQL code.  This is because in all cases, procedure
> names are simply passed with the name and not with any preceding
> "SELECT * FROM" clause.

Are you saying your code is trying to parse SQL within your own 
application? Doing so will likely reject a lot of valid SQL (for 
example, I wonder what happens if a query including a CTE is passed 
through that, or something like an execute block).

> However, with Firebird's documentation (and the lack thereof for the
> ADO.NET provider) developers like myself who are new to Firebird would
> naturally assume that the "SELECT * FROM" clause should be included in
> that part of the query-string that is passed with the procedure name
> to Firebird's ADO.NET Provider, which with my data access layer sees
> the query-string as raw SQL code.

As far as I'm aware (but haven't verified) the ADO.net driver allows you 
to pass the stored procedure name, or EXECUTE PROCEDURE 
procedurename(params) (for an executable procedure) or SELECT * FROM 
procedurename(params) for a selectable one.

> However, after testing the method where the "parameter mismatch" error
> was occurring without the "SELECT * FROM" clause, my data access layer
> saw the query-string as a stored procedure and ran as expected with
> the correct results being returned.

Without a specific reproduction case I find it hard to understand what 
you mean, but it may have to do with the specific handling of the 
ADO.net driver. You may want to consider posting a question on the 
mailing list for the ADO.net provider.

> I am hoping to begin writing a technical article soon that will
> introduce database application developers to these idiosyncrasies who
> are new to Firebird, making their initial forays into using this
> database engine less frustrating.

I'm looking forward to read it!

Mark

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

04.05.2018 20:10, blackfalconsoftw...@outlook.com [firebird-support] wrote:
> Nonetheless, I have worked with Oracle, Sybase, MySQL, and a little 
> PostgreSQL and SQLite.

   I can't believe that you never looked at Oracle's FOR LOOP syntax in 
pipelined 
functions which difference from Firebird is subtle.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[blackfalconsoftw...@outlook.com [firebird-support]]

Thank you, Mark, for clearing up my misunderstanding.  
 

 When I found some sample code on StackOverflow that demonstrated the use of 
the "FOR-DO" construct, I realized what I had misunderstood.
 

 I believe I sent you an email that suggested my correction to my understanding 
of this construct.
 

I must admit that I have spent many years working with various database 
engines, with SQL Server being the engine where the large majority of my 
professional development had been emphasized.  Nonetheless, I have worked with 
Oracle, Sybase, MySQL, and a little PostgreSQL and SQLite.

I am finding the Firebird Engine to be the most quixotic engine I have worked 
with to date but after seeing that Microsoft's SQL Server LocalDB Engine was 
not really suited for embedded database applications (though I wrote a complete 
installer with the use of the small system API to make the installation as 
user-friendly as possible), I decided to delve into Firebird with the 
determination to finally understand its differences when compared to the other 
engines I have worked with.

I believe I am finally getting the hang of it.  However, one of the areas that 
I am finding very confusing is the inconsistencies between using raw PSQL code 
in a DB Manager and using it with the Firebird ADO.NET Provider that one of 
your project's third-party contributors provides.

For example, if I want to run a "selectable" stored procedure in my DB Manager, 
I enter the following for example...

   SELECT * FROM MY_PROCEUDRE_NAME (PARAM)

However, if you do the same using my own data-access-layer, the provider yields 
a parameter mismatch error.  There is a very specific reason for this.

I have written data access layers for a number of database engines, including 
Oracle and PostgreSQL, along with Firebird, which was the most recent layer I 
released.  In all of layers when SQL code is passed to a query method, I 
validate for whether there is a "SELECT" statement present.  If so, than the 
layer determines that is what is being passed is raw SQL code.  This is because 
in all cases, procedure names are simply passed with the name and not with any 
preceding "SELECT * FROM" clause.

However, with Firebird's documentation (and the lack thereof for the ADO.NET 
provider) developers like myself who are new to Firebird would naturally assume 
that the "SELECT * FROM" clause should be included in that part of the 
query-string that is passed with the procedure name to Firebird's ADO.NET 
Provider, which with my data access layer sees the query-string as raw SQL code.

However, after testing the method where the "parameter mismatch" error was 
occurring without the "SELECT * FROM" clause, my data access layer saw the 
query-string as a stored procedure and ran as expected with the correct results 
being returned.

I am hoping to begin writing a technical article soon that will introduce 
database application developers to these idiosyncrasies who are new to 
Firebird, making their initial forays into using this database engine less 
frustrating.

I have always believed that these types of issues have kept Firebird from being 
realized as a top-contender in the database world.  It is based upon an 
excellent technology foundation and should be exposed more generally for the 
excellent and highly efficient engine that it really is...



Steve Naidamast

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2018-05-04 18:56, blackfalconsoftw...@outlook.com [firebird-support] 
wrote:
> I am not sure if I understand your explanation here...
> 
> 1...
> Why would I test the input parameter, "PI_KEY_IN" for each row?  I am
> only doing this to ensure that the client never sends an input value
> of zero(0).

That may be what you  want to do, but it is not what your code will 
actually do. And you may want to consider doing that before producing 
rows, btw.

> 2...
> In the other email you sent you stated that...
> "A FOR .. DO is a loop construct, which either execute a single
> statement for each iteration (each row) in the loop, or a block of
> statements delimited by BEGIN and END."
> 
> However. if this follows as most constructs, the "DO" part is the end
> statement to the "FOR".  Or is this better understood as "FOR"
> everything I do here, "DO" the following...  ???

No, the DO is not the end of the FOR, it signals that what follows is 
done for each row produced by the FOR.

In short:

FOR  DO 

See also:

- WHILE: 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-while
- FOR SELECT: 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-forselect
- FOR EXECUTE STATEMENT: 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-forexec

Mark

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[blackfalconsoftw...@outlook.com [firebird-support]]

I am not sure if I understand your explanation here...  

1...
Why would I test the input parameter, "PI_KEY_IN" for each row?  I am only 
doing this to ensure that the client never sends an input value of zero(0).

2...
In the other email you sent you stated that...
"A FOR .. DO is a loop construct, which either execute a single statement for 
each iteration (each row) in the loop, or a block of statements delimited by 
BEGIN and END."

However. if this follows as most constructs, the "DO" part is the end statement 
to the "FOR".  Or is this better understood as "FOR" everything I do here, "DO" 
the following...  ???

Steve

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 4-5-2018 18:31, Steve Naidamast blackfalconsoftw...@outlook.com 
[firebird-support] wrote:
> How so?  The "SUSPEND" statement is the last statement in the procedure 
> or are you referring to the fact that it is continued as a result of the 
> previous "DO" statement?

A FOR .. DO is a loop construct, which either execute a single statement 
for each iteration (each row) in the loop, or a block of statements 
delimited by BEGIN and END.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support]]

How so?  The "SUSPEND" statement is the last statement in the procedure or are 
you referring to the fact that it is continued as a result of the previous "DO" 
statement?


Steve



From: firebird-support@yahoogroups.com  on 
behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 

Sent: Friday, May 4, 2018 12:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Problem returning multiple rows from a 
CTE-recursive procedure

04.05.2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com 
[firebird-support] wrote:
> If you look at my procedure below, you will note that there is only a single 
> "SUSPEND"
> statement.

   If you look a little more, you'll see that it is called in a loop.


--
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
Firebird: The true open source database for Windows, Linux 
...
www.firebirdsql.org
Firebird SQL: The true open-source relational database


on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

04.05.2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com 
[firebird-support] wrote:
> If you look at my procedure below, you will note that there is only a single 
> "SUSPEND" 
> statement.

   If you look a little more, you'll see that it is called in a loop.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 4-5-2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com 
[firebird-support] wrote:
> However, I am not sure about the multiple "SUSPEND" statements on a 
> per-record basis.
> 
> If you look at my procedure below, you will note that there is only a single 
> "SUSPEND" statement.  Yet, all of the rows for the entered date that is used 
> to execute the procedure (select statement follows module code) are returned 
> as expected (9 rows returned)...
> 
> CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES(
>PS_DATE_IN VARCHAR(10) NOT NULL)
> RETURNS(
>PS_DATE_OUT VARCHAR(10) NOT NULL)
> AS
> DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
> BEGIN
>PS_SQL = 'SELECT DISTINCT';
>PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
>PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM 
> ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
>PS_SQL = PS_SQL || '   ' || '(TRIM(CAST(EXTRACT(DAY FROM 
> ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
>PS_SQL = PS_SQL || '   ' || '(TRIM(CAST(EXTRACT(YEAR FROM 
> ML_CREATE_DATE) AS VARCHAR(4 = ' || :PS_DATE_IN;
>FOR
>EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT
>DO
>SUSPEND;
> END;

> Nonetheless, would you suggest that I put the "SUSPEND" statement within the 
> FOR-DO construct?

The SUSPEND is already in the FOR-DO construct here. What you are 
missing is that the above is equivalent to

FOR ... DO
BEGIN
 SUSPEND;
END

In other words, it means "for each row do a suspend", while in your 
initial question you had

FOR ... DO
IF (PI_KEY_IN = 0) THEN
 EXCEPTION ROOT_CAT_NODE_DELETE;
SUSPEND;

which is equivalent to

FOR ... DO
BEGIN
 IF (PI_KEY_IN = 0) THEN
 EXCEPTION ROOT_CAT_NODE_DELETE;
END
SUSPEND;

which means "for each row do throw an exception if PI_KEY_IN = 0, and 
afterwards suspend a single row"

SUSPEND returns the current values of the output fields, and waits for 
them to be fetched by the client. See also 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-suspend

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support]]

Dimitry ...


Thank you for your reply...  


However, I am not sure about the multiple "SUSPEND" statements on a per-record 
basis.


If you look at my procedure below, you will note that there is only a single 
"SUSPEND" statement.  Yet, all of the rows for the entered date that is used to 
execute the procedure (select statement follows module code) are returned as 
expected (9 rows returned)...


>>>

>>> Stored Procedure

>>>

CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES(
  PS_DATE_IN VARCHAR(10) NOT NULL)
RETURNS(
  PS_DATE_OUT VARCHAR(10) NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
  PS_SQL = 'SELECT DISTINCT';
  PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';

  PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM 
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
  PS_SQL = PS_SQL || '   ' || '(TRIM(CAST(EXTRACT(DAY FROM ML_CREATE_DATE) 
AS VARCHAR(2)))' || '/';
  PS_SQL = PS_SQL || '   ' || '(TRIM(CAST(EXTRACT(YEAR FROM ML_CREATE_DATE) 
AS VARCHAR(4 = ' || :PS_DATE_IN;

  FOR
  EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT
  DO

  SUSPEND;
END;
<<<

>>>
>>> SELECT Statement
>>>
SELECT * FROM  SP_GET_MSGLOG_RECS ('10/17/2017')
<<<

Nonetheless, would you suggest that I put the "SUSPEND" statement within the 
FOR-DO construct?

Thank you...

Steve Naidamast
  Sr. Software Engineer
  blackfalconsoftw...@outlook.com
 [cid:96714d12-5078-4b55-a660-b7d6d4c1b734]







From: firebird-support@yahoogroups.com  on 
behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 

Sent: Thursday, May 3, 2018 5:17 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Problem returning multiple rows from a 
CTE-recursive procedure

03.05.2018 23:04, blackfalconsoftw...@outlook.com [firebird-support] wrote:
> However, I though only the issuance one "SUSPEND" was enough to flush the 
> entire buffer of
> all records...

   No. One SUSPEND - one record in result set. Two SUSPENDs - two records and 
so on.

   Watch this in isql:

SQL> set term GO;
SQL> EXECUTE BLOCK RETURNS (A INTEGER)
CON> AS
CON> begin
CON>  a = 1;
CON>  SUSPEND; -- return a record containing 1
CON>  a = 2;
CON>  SUSPEND; -- return a record containing 2
CON>  SUSPEND; -- return another record containing 2
CON> end
CON> GO

A

1
2
2

--
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
Firebird: The true open source database for Windows, Linux 
...
www.firebirdsql.org
Firebird SQL: The true open-source relational database


on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links





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

Re: [firebird-support] What regular maintenance actions does Firebird need in order to function properly?

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

 What I mean is this: I want to know if a need to increment the page cache 
buffer of Firebird.
And If was thinking of having some metric to measure it. 
This metric would be very convinient If I need for example to justify buying 
more memory for the server.
Thanks in advance. 

Re: [firebird-support] Create a table from Query

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

04.05.2018 14:04, antoedinchar...@gmail.com [firebird-support] wrote:
> Is there any way we could create a table in Firebird using the query?

   No. Firebird cursors (implicit and explicit) are more powerful than MS SQL's 
ones and 
let developers avoid local temporary tables completely.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Create a table from Query

[antoedinchar...@gmail.com [firebird-support]]

Hello,
 Is there any way we could create a table in Firebird using the query?
 

 Ex:-
 

 create table temptable
 as
 select * from tempquerytable t1, tempquery t2 where t1.slno = t2.slno;
 

 this creates a table  temptable with the structure of both t1
 

 Thanks and Regards
 

Re: [firebird-support] [FB3 CS] Server stops accepting new connections - lock denied

[ehmmm.fireb...@seznam.cz [firebird-support]]

Hallo Franco,

I had quite similar problem around 5th December 2017, try to search "lock 
denied" around that date.

In my case it helped to switch to SuperServer mode.
(It required to change some SW to use the "TCP/IP" mode instead of the 
"file" access.)

I remember that I promised that I would test the SuperClassic mode on our 
backup server but I havn't done it yet.

Hope that helps,
Jakub

-- Původní e-mail --
Od: 'Franco Perana' franco.per...@studiocentro.it [firebird-support] <
firebird-support@yahoogroups.com>
Komu: firebird-support@yahoogroups.com
Datum: 4. 5. 2018 3:21:55
Předmět: [firebird-support] [FB3 CS] Server stops accepting new connections 
- lock denied 
" 
  



Classic Server stops accepting new connections, while already active
ones continue to work regularly. 

Load conditions seems irrilevant, we had a total of 4 events in less
than a week, two of these during very low load conditions and two in
average conditions. 

Error in firebird.log (this is the last event this morning, load
conditions at 6 am are extremely low in our environment): 

 

sqlserver Thu May 3 06:17:56 2018 
Database: /opt/firebird/security3.fdb 
page 0, page type 1 lock denied (216) 

sqlserver Thu May 3 06:17:57 2018 
Database: 
page 0, page type 1 lock denied (216) 

 

I've also noticed there are other errors in firebird.log, but these
seems not to be related to any connection problems (I've got no reports
from users). I put them here anyway: 

 

sqlserver Wed May 2 18:43:50 2018 
Database: /opt/firebird/security3.fdb 
page 377, page type 3 lock denied (216) 

sqlserver Wed May 2 18:43:52 2018 
Database: /opt/firebird/security3.fdb 
page 377, page type 3 lock denied (216) 

sqlserver Wed May 2 18:43:53 2018 
Database: /opt/firebird/security3.fdb 
page 377, page type 3 lock denied (216) 

 

This problem seems to be similar to this one:
http://tracker.firebirdsql.org/browse/CORE-5436 which however affects
Firebird on Windows. 

This is a big problem for us, service is going down from time to time
and requires a server restart.

STUDIO CENTRO SRL - Soluzioni e Servizi - www.studiocentro.it
Sede: 36100 Vicenza - Via Luigi Boccherini, 13 Tel. 0444.56.31.66 Fax
0444.96.43.65 email: conta...@studiocentro.it

AVVERTENZE AI SENSI DEL DLGS 196/2003
Le informazioni contenute in questo messaggio di posta elettronica o nei
suoi allegati, sono da considerarsi strettamente riservate.
Il loro utilizzo si consente esclusivamente al destinatario, per le
finalità indicate nel messaggio stesso.. Qualora non siate il
destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e
di procedere alla cancellazione dello stesso dal Vostro sistema.
Costituisce comportamento contrario ai principi dettati dal
Dlgs.196/2003 trattenere il messaggio, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per fini
diversi. 

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






"

Re: [firebird-support] Measure the effectiveness of incrementing page buffer...

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

04.05.2018 11:42, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote:
> the single badly designed query with many page fetches can "improve" this 
> parameter a lot, 
> without any real improvement in the performance, of course.

   The question was about cache effectiveness which is always measured as "hit 
ratio", not 
about abstract "performance" which has no formal metrics.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Measure the effectiveness of incrementing page buffer...

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

I am totally agree with Mark.
Ratio of page reads/page fetches for all queries has a little sense.
For example, the single badly designed query with many page fetches can 
"improve" this parameter a lot, without any real improvement in the 
performance, of course.


Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com





On 04.05.2018 12:29, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:


On 3-5-2018 15:52, Javier Cintron fcintr...@yahoo.com [firebird-support]
wrote:
>
>
> Thank you very much!

You shouldn't focus purely on this metric, greater page caches may lead
to more pages returned from memory, but also increase memory consumption
and overhead from things like cache eviction/coordination in the case of
Classic / SuperClassic (where the cache is per connection).

Mark

> On Thursday, May 3, 2018, 3:30:32 AM CDT, Dimitry Sibiryakov
> s...@ibphoenix.com [firebird-support] 
> wrote:
>
>
> 03.05.2018 7:56, Javier Cintron fcintr...@yahoo.com [firebird-support]
> wrote:
> > Firebird ver 2.5.7.27050 64 bits
> > Windows Server 2012R2
> >
> > ¿Is there a way to calculate the effectiveness of incrementing the
> page cache buffer ?
>
> select cast(mon$page_reads as double precision)/mon$page_fetches from
> mon$io_stats
> where mon$stat_group=0
> If the value is getting lower - you are on the right way.
>
> --
> WBR, SD.
>
>
>
>

--
Mark Rotteveel

Re: [firebird-support] Measure the effectiveness of incrementing page buffer...

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 3-5-2018 15:52, Javier Cintron fcintr...@yahoo.com [firebird-support] 
wrote:
> 
> 
> Thank you very much!

You shouldn't focus purely on this metric, greater page caches may lead 
to more pages returned from memory, but also increase memory consumption 
and overhead from things like cache eviction/coordination in the case of 
Classic / SuperClassic (where the cache is per connection).

Mark

> On Thursday, May 3, 2018, 3:30:32 AM CDT, Dimitry Sibiryakov 
> s...@ibphoenix.com [firebird-support]  
> wrote:
> 
> 
> 03.05.2018 7:56, Javier Cintron fcintr...@yahoo.com [firebird-support] 
> wrote:
>  > Firebird ver 2.5.7.27050 64 bits
>  > Windows Server 2012R2
>  >
>  > ¿Is there a way to calculate the effectiveness of incrementing the 
> page cache buffer ?
> 
> select cast(mon$page_reads as double precision)/mon$page_fetches from 
> mon$io_stats
> where mon$stat_group=0
> If the value is getting lower - you are on the right way.
> 
> -- 
> WBR, SD.
> 
> 
> 
> 


-- 
Mark Rotteveel

Re: [firebird-support] What regular maintenance actions does Firebird need in order to function properly?

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

04.05.2018 4:55, Javier Cintron fcintr...@yahoo.com [firebird-support] wrote:
> what other regular maintenance actions does Firebird need in order to 
> function properly?

   It may be a good idea to recalculate index statistics from time to time.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] What regular maintenance actions does Firebird need in order to function properly?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

We recommend perform at least everyday sweep - with disconnect of long 
running active writeable transactions before it.


Can you please clarify what do you mean by
> measuring the effectiveness of the page cache
?

Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com




On 04.05.2018 5:55, Javier Cintron fcintr...@yahoo.com 
[firebird-support] wrote:

Firebird ver 2.5.7.27050 64 bits
Superserver
Windows Server 2012R2

Besides, backing up my databases and measuring the effectiveness of 
the page cache, what other regular maintenance actions does Firebird 
need in order to function properly?


thanks in advance