RE: [firebird-support] Scaling Firebird - Azure

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I think it is unfair to say Azure sucks. I have had huge performance issues 
with FB on a normal server with server grade SSDs. My desktop PC with spindle 
HDD outperforms it by an order of magnitude. To date it has not been resolved 
but I suspect it has to do with the RAID controller. The same server is very 
fast with MSSQL. 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Wednesday, 09 October, 2019 08:17
To: Nagy Szilveszter nagy_szilvesz...@yahoo.com [firebird-support] 

Subject: Re: [firebird-support] Scaling Firebird - Azure

 

  

Hello,

 

In short words, Azure sucks, its so called Premium SSD is worse than the 
cheapest consumer grade ssd.

 

Try Google Cloud or Amazon, or Hetzner.

 

Regards, 

Alexey Kovyazin 

IBSurgeon 

 

 

 

пн, 7 окт. 2019 г., 0:20 Rune Horneland rune.hornel...@kravia.net 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> >:

  

Hi,

We are running Firebird 2 in an Azure VM. It can only take so much in terms of 
concurrent connections.

What top-level advice would you give to scale this?


We are connecting to it from a .NET core Middleware using Azure VMs.

The architecture of the middleware is quite monolithic. We are considering 
rewriting it with Microservices and Azure functions or similar architecure, but 
are unsure how we could scale the Firebird DB or connections itself.

Multiple casehandlers in our company use it via a Delphi-based Windows 
application at the other end, with a vendor maintaining the Firebird DB and 
Windows app development, so we are locked into using Firebird. 

 

RUNE HORNELAND
CTO





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

RE: {Disarmed} [firebird-support] Web application with Firebird 3 and SaaS

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

This is the classic multi-tenant question. It depends on which of the isolation 
models you are using and where you will host. Each model has advantages and 
disadvantages.

 

DB per tenant

Pros: 

Your code does not have to take tenant into account. As long as you can route 
your queries to the appropriate tenant’s DB.

Load balancing across DBs are possible if you deploy to diff servers. Think of 
a few very busy tenants and some not-so-busy. Migrating tenants that grow over 
time to other servers are easy

Cons: 

With lots of tenants/DBs, when you do an app upgrade you will have to upgrade 
all DBs at the same time as you will likely update your webapi as well. If not, 
you will need to maintain different versions of the API as appropriate for the 
DB version. Truly a nightmare

 

Also no cross tenant/DB queries for your analytics

 

 

All tenants in one DB

Pros: 

When update time comes, upgrade one DB and your WebAPI and you are done

Cross-tenant queries for analytics

Load balancing can be done on groups of tenants by using several multi-tenant 
DBs on different servers

Cons: 

Your application code and table structure have to have TenantId everywhere. 
Easy for a bug to creep in and leak data across tenants. Some tenants don’t 
like this model.

You cannot migrate a tenant from one DB to another

 

 

Regarding the question re user, your end users will never log into the DB so 
you don’t need multiple users set up in FB. It is good practice for you to set 
up a single non-admin user that is used by your webapi and grant accordingly. 
Another WebAPI that is subset or different end user use cases may require one 
additional FB user, again with grants according to what they need

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Wednesday, 05 June, 2019 10:58
To: firebird-support@yahoogroups.com
Subject: Re: {Disarmed} [firebird-support] Web application with Firebird 3 and 
SaaS

 

  


> I am planning the infrastructure and security and I have several
> doubts. My initial idea would be to have a database where users can
> save their emails and create a separate database for each client.

For an Web-Application this is not really common practice as you prevent
e.g. usage of "connection pooling".

An alternative would be to use an single database and an single database
user. If the machine is dedicated toi that web application it should be
ok to use default SYSDBA/masterkey login as nobody besides the
application can connect to database.

It's than up to the application to manage user login with information
stored in an normal table.

Also the application has to manage accessing the correct data - you will
pass Company ID to most SQL statements.

As number of clients grows using an single database will consume much
less resources on your server.

> I think it is best to isolate each database with its own security*.fdb
> to prevent any client from accessing another database 

You application is the only one connecting to database. The clients are
connecting to your application.

> and in this point is what I am not clear about, how to manage users
> and security by database, permissions...

read documentation for "CREATE USER" and "GRAND" SQL-Statements.

> I would also need to be able to create a database on the fly, when a
> new client registers, for that I need to stop the Firebird service?
>
You can create databases without stopping server of course - it is up to
the server to create database. But, creating database and tables does
take some time.





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

RE: [firebird-support] Install a DB Firebird on a web site

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

My approach was to set up a Windows VM on Azure / AWS and install the app
there. Then use RDP clients to access the app in the cloud. Users can access
the app from pretty much any device that has RDP client software. Mac / Win
/ iPAD etc.

Then at least your DB access from app is local to the VM and nice and fast
as well as your DB is not open to the outside world.
 

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 27 March, 2019 17:32
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Install a DB Firebird on a web site

On 27/03/2019 12:01, 'Stellarancia.com' ni...@stellarancia.com
[firebird-support] wrote:
> I was thinking of moving the database Firebird from the computer to a 
> Web site, to allow all one connection directed by app.
> Ithink this is possible for Firebird, but I have not succeeded in 
> finding the way to install Firebird on a site or to find a site that 
> can manage the Firebird driver.

If your delphi application is designed for multiple clients to access the
same database, then it is just a matter of making the database accessible on
the internet. That does not involve a website, but it does open the database
to scrutiny by anybody else on the internet.

My own websites use Firebird as a tool to store the data that is displayed
on the website, but is only accessible by the local copy of PHP, while
another application has BuilderC clients spread across multiple locations
all accessing the one copy of Firebird one a closed network.

As Mark says, what are you actually trying to achieve?

--
Lester Caine - G8HFL
-
Contact - https://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve -
https://enquirysolve.com/ Model Engineers Digital Workshop -
https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk






++

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

RE: [firebird-support] Prevent overlaping dates in concurent environment

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

The practicality of entering each date in the range into a table depends on the 
application. For short date ranges this should be fine, but very wide ranges 
will have a bit of a performance impact on inserts. 

 

Let’s say you want to book a resource for a date range. Then you could create 
tables like this:

 

create table RESOURCE

(

  ID integer,

  NAME varchar(64)

);

alter table RESOURCE add constraint RESOURCE_PK primary key (ID) using index 
RESOURCE_PK_IDX;

 

create table RESOURCE_BOOKING

(

  ID integer,

  RESOURCE_ID integer,

  FROM_DAT DATE,

  TO_DAT DATE,

);

alter table RESOURCE_BOOKING add constraint RESOURCE_BOOKING_PK primary key 
(ID) using index RESOURCE_BOOKING_PK_IDX;

create index RESOURCE_BOOKING_RESOURCE_IDX on RESOURCE_BOOKING(RESOURCE_ID);

 

 

create table RESOURCE_BOOKING_DETAIL

(

  BOOKING_ID integer,

  RESOURCE_ID integer,

  BOOKED_DAT DATE

);

alter table RESOURCE_BOOKING_DETAIL add constraint RESOURCE_BOOKING_DETAIL_UNQ 
unique (RESOURCE_ID,BOOKED_DAT) using index RESOURCE_BOOKING_DETAIL_UNQ_IDX;

create index RESOURCE_BOOKING_DETAIL_IDX on RESOURCE_BOOKING_DETAIL(BOOKING_ID);

 

The RESOURCE_BOOKING_DETAIL_UNQ unique constraint will ensure that a RESOURCE 
cannot be booked more than once for the same day. The associated index is also 
useful for selecting the days a RESOURCE is booked for.

 

The RESOURCE_BOOKING_DETAIL_IDX is useful for when you want to delete a BOOKING 
by its ID.

 

 

 

 

 

 

 

 

 

Two ranges may overlap in 4 ways and you have to check all 4 scenarios to 
detect an overlap. There is no way to utilise standard DB constraints to 
validate this.

 

I have written some PSQL SP to validate the 4 cases, but I normally do this in 
my domain layer. This means there is no guarantee that overlap does not exist 
in a concurrent environment.

 

The range overlap cases are as follows (not sure if the email spacing will be 
preserved):

  |---R1---|

  |---R2-|

 

|---R1---|

|---R2-|

 

 

 |---R1---|

|---R2-|

 

 

 

|R1---|

  |--R2--|

 

 

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Wednesday, 12 September, 2018 14:41
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurent 
environment

 

  

Hi Tomasz! 

Tested on 2.5.8, dialect 1: 

CREATE TABLE RESERVED_DATE ( 
RES_DATE DATE NOT NULL 
); 
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY 
(RES_DATE); 

CREATE TABLE MEETINGS ( 
DATE_FROM DATE NOT NULL, 
DATE_TO DATE NOT NULL 
); 

CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS 
ACTIVE BEFORE INSERT POSITION 0 
as 
declare variable curr_date date; 
begin 
curr_date = new.date_from; 
while (curr_date <= new.date_to) do 
begin 
insert into reserved_date (res_date) values (:curr_date); 
curr_date = dateadd(1 day to curr_date); 
end 
end 

run on first transaction: 

insert into MEETINGS (DATE_FROM, DATE_TO) 
values ('2018.09.01', '2018.09.10') 

run parallel on secound transaction: 
insert into MEETINGS (DATE_FROM, DATE_TO) 
values ('2018.09.08', '2018.09.15') 

violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table 
"RESERVED_DATE". 
Problematic key value is ("RES_DATE" = '8-SEP-2018'). 
At trigger 'MEETINGS_BI' line: 9, col: 7. 

András 

From: firebird-support@yahoogroups.com  
Sent: Wednesday, September 12, 2018 1:59 PM 
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Prevent overlaping dates in concurent 
environment 



On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support] 
wrote: 
> Create a (reserved_dates) table with date field, and make that field unique.. 
> When a user inserts a date into the reservation table a trigger immadiate 
> inserts this date to the reserved_dates table too. Then the unique key will 
> stop secound insert instead of the first transaction is not commited. 

This won't work. All dates may be different and the intervals may still 
overlap. 
Karol, that's an interesting issue and I'm really curious if there's a 
clever solution. 
So far I've checked the check (pun intended): 

create table TST1 ( 
d1 timestamp, 
d2 timestamp, 

constraint no_overlap check ( 
not exists ( 
select * from TST1 t1 
where exists ( 
select * from TST1 t2 
where t1.d1 between t2.d1 and t2.d2 
or t1.d2 between t2.d1 and t2.d2 
) 
) 
) 
); 

insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A 
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B 
-- commit A (no errors) 
-- commit B (no errors) 

and it doesn't work. You can still insert two overlapping pairs and both 
transactions get committed without errors, resulting in overlapping 
intervals being inserted. So, unless you change the transaction 
isolation level (I always use read committed), I don't have more ideas 
at the mom

RE: [firebird-support] Sequential auto incremental numbering

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

As previous responders have said, generator values are guaranteed to be
unique. However when transactions fail, the generator will skip a value.

 

Only relying on 

NewNo = select max(No) + 1 from Invoice 

is also a bad idea because in concurrent insert conditions you are for sure
going to get duplicates. As far as I remember, aggregate functions also do
not use indexes and will get progressively slower over time. This might have
changed though, so I may be wrong on this.

 

The only reliable way I found is as follows:

 

* We know that the DB engine will at the very least guarantee
uniqueness of the invoice no if we put a unique constraint on the column.
Even with concurrent inserts that is at least one thing we can fully rely
on. So we create the unique constraint on the column.

 

* Our insert operation then uses the following logic:

 

BeginTransaction();

while (true)

{

  newInvoiceNo = select max(InvoiceNo) + 1 from INVOICE

 Invoice.InvoiceNo = newInvoiceNo;

 

  try

  {

InsertInvoice(); 

Commit();

  }

  catch (UniqueConstraintException exc)

  {

WaitRandomNumberOfMillisecs();

  }

  catch (Exception exc)

  {

Rollback();

throw;

  }

}

 

The effect of above pseudo-code is that it gets the next InvoiceNo as max+1.
When the record is inserted, the unique constraint will make sure we do not
have a duplicate. If we don't then the insert will succeed and the
transaction is committed.

If a duplicate exists, the insert will throw an exception that indicates a
unique constraint violation. The exception handler for that exception will
pause the thread for some (short) time and then retry as part of the next
iteration of the while loop. If the exception was not a unique constraint
violation, then the transaction is rolled back and the exception bubbles up
to the next level in the call stack.

Above code will obviously rety infinite times, but you can add a max retry
count.

 

I have only ever found, in the case of a duplicate number, one retry is
usually enough for the record to be inserted. Even with multiple clients
using above mechanism, all of them will be successful and have a unique
sequential number. We are using the integrity of the DB engine to ensure
this.

 

Hope it helps

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Monday, 03 September, 2018 13:30
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Sequential auto incremental numbering

 

  



> On Sep 3, 2018, at 6:00 AM, 'River~~' river14ap...@gmail.com
[firebird-support]  wrote:
> 
> Two ideas that should work on any SQL database so long as you never delete
invoices

Remember that a transaction can roll back, so delete is not your only
challenge. 
> 
> I amNot a guru for this dbms so if a more specific technique is posted it
is likely to be faster
> 
> Note however that while my method may be inefficient, it is more likely to
be portable. You pays your money and you takes your choice
> 
> 
> With SQL and code in another language:
> 
> Start transaction
> Select max (invoice) ...
> Calculate next invoice 
> Insert...
> Commit
> 

That just narrows the window of chance that two transactions will see the
same next value. 

> Or if you want to do it all in SQL then create a view that selects max
invoice plus one, and insert one row at a time from that view into the
table. The other columns would then be presented as calculated values 

I think that does nothing to solve the problem of seeing the same max value
from two concurrent transactions. 
> 
> Either of the above are reasonably efficient if you have an index on the
invoice number, as firebird can easily find the first and last entries from
the index and iirr if the only column retrieved is the indexed value
firebird does not even access the table. 

Firebird does access the table to insure that the last index entry is a
record version visible to the current transaction. 

Read the article in ibobjects. 

Good luck,

Ann

> 





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

[firebird-support] Not sure why join speed is slow

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I have a query as follows:

 

with A as

(

  

  

),

B as

(

  

),

C as

(

  select *

  from B

  left outer join A on A.SOME_ID = B.SOME_ID

)

select * from C

 

When I execute the sql for A without my own plan, the result takes 140ms. With 
my own plan the result takes 90ms. The resulting rowcount is 6

 

When I execute sql for B, execute time is shown as 0ms with rowcount 8

 

When I execute the entire query, the resulting rowcount is correctly 8 but the 
query result takes around 500ms. I don’t understand why joining 6 rows onto 8 
takes so much more time?

 

Would appreciate help

 

Louis

 

 

 

 

 

 

 

RE: [firebird-support] FB in Azure VM?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Yes I was in the same boat. I wanted to deploy a WebAPI on Azure that connects 
to FB.  To run a VM all the time just for this was overkill in terms of cost. 
So I just moved the app over to Azure SQL. 

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 21 June, 2017 13:18
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] FB in Azure VM?

 

  

With Azure - AFAIKnew two VM are never guaranteed to be anywhere close to each 
other. This was a total turn off for me, given that bandwidth for each server 
is based on cost and very expensive, the two servers would often suffer from 
one or both ends being thorttled back to almost nothing.
Better to co-host your own serer somewhere than use Azure. That was my decision.

Alan McDonald
0413 657 427

-Original Message-
From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 21 June 2017 8:09 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] FB in Azure VM?

Hi,

We're considering a move from dedicated server (at a hosting provider) to 
hosting "everything" in the cloud, namely Azure.

They don't provide FB as "app as a service", so we're forced to go with a VM 
where we maintain our own FB installation, which is not a problem per se, but 
means we'll have to manage it more ourselves.

What experiences do you have with deploying FB in an Azure VM or similar?

If I understand MS correctly, they can provide a locally mounted 
high-performance disk, but I'm not sure if they can provide mirroring, e.g. 
RAID 1.

On our dedicated server we have a local RAID 1 volume (mechanical 10k or 
possibly 15k rpm SCSI) for the databse, and make full nightly backups. 
Forced writes off. Transaction load isn't very high, but we do need to be able 
to execute long-running and "heavy" queries while at the same time a web app 
makes short transactions that require short response times.

I'd like to know if you think the cloud VM approach for a FB installation is 
viable, any special consideration we should be aware of, etc.

I do know that the wire protocol is not very efficient if the network latency 
is high, so you don't have to mention that aspect. I.e. we're probably better 
of having both FB and the apps hosted in the same cloud...

But... do you know if this is a problem if FB is in an Azure VM and the app is 
hosted in an Azure App Service? Network latency ok in that scenario?

Regards,
Kjell
-- 

Marknadsinformation logotyp

Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Marknadsinformation i Sverige AB
Ulvsundavägen 106C
168 67 Bromma
www.marknadsinformation.se 
08-514 905 90

Företagskontakt.se 
Personkontakt.se 

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


Posted by: Kjell Rilbe 


++

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





---
This email has been checked for viruses by AVG.
http://www.avg.com


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

RE: [firebird-support] QUERY QUESTION

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Select itemnr,count(*) as days

>From table

Group by itemnr,cast(dateused as date)

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 02 March 2017 06:37 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] QUERY QUESTION
Importance: High

 

  

Good day,

I have a question for you SQL boffins, how can I query a table to get the
count of consecutive days found ?

A Table contains rows with dates where an item(s) has been added for every
day this item was used. 

i.e.

itemnr dateused

abc 2017/02/01

abc 2017/02/02

abc 2017/02/25

abc 2017/02/25

abc 2017/02/25

abc 2017/02/26

abc 2017/02/27

abc 2017/02/28

abc 2017/03/01

abc 2017/03/02

The result from above sample should be 6 as there is a break between
2017/02/02 and 2017/02/25?

Regards

Stef

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





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

RE: {Disarmed} Re: [firebird-support] Number of concurrent user connections

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

That is why you use the conneciton pool

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 27 October 2016 04:31 PM
To: firebird-support@yahoogroups.com
Subject: Re: {Disarmed} Re: [firebird-support] Number of concurrent user
connections

27.10.2016 16:27, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support]
wrote:
> It's a bad idea to keep DB connection permanently open while your app 
> is open. In this way your connection is open for a very short period 
> of time and you can service many user requests with a relatively small 
> number of simultaneous open connections to the DB

   And you waste a lot of time and server power for endless
connect/disconnect which are quite heavy operations.


-- 
   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

RE: {Disarmed} Re: [firebird-support] Number of concurrent user connections

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

It is good practice to only have an open connection to the DB when you need to 
access or update data. So you have to design your application to, when a 
request from client is received:

-  create and open a connection to the DB (this usually comes from the 
connection pool)

-  select the data your need

-  update the data

-  close the connection and return it to the pool

 

It’s a bad idea to keep DB connection permanently open while your app is open. 
In this way your connection is open for a very short period of time and you can 
service many user requests with a relatively small number of simultaneous open 
connections to the DB

 

Louis

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 27 October 2016 04:12 PM
To: firebird-support@yahoogroups.com
Subject: Re: {Disarmed} Re: [firebird-support] Number of concurrent user 
connections

 

  


> I was looking on FaceBook 'sDATA storage and MySQL !!!
>
> For sure millions simultaneous Users connected !!!

I would guess nobody is running an database server with millions of
connections simultaneously open.

On huge Systems as Facebook there are some application servers between
users and database which do serialize database access to an lower number
of connections - it does not make sense to let DB server process
millions of queries simultaneously since the hardware has not that much
parallelity.

Elmar





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

RE: [firebird-support] how to suppress dashes in query results

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

But seriously, you should always validae user input BEFORE it going to the 
database.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 12 February 2016 03:58 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] how to suppress dashes in query results

 

  

Users can enter values with dashes (-) in random locations. I don't know where 
they might put them. I need to select a record using a where clause that 
doesn't have those dashes. For example, the user might record an insurance 
policy number as 12-345678-01 or 123-456-789-01. All I will know are the 
number, and not where they put the dashes. Is there a way to suppress the 
dashes in the Select Clause, or the Where Clause? This is being done in a 
Delphi 2007 program using IBDAC.

 

It would be nice if I could do something like ... SELECT * WHERE 
HIDE('-',UsersField) = 'MyValue'

Or the equivalent in the Select statement.





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

Re: [firebird-support] Bad performance of Firebird in Windows Server 2012

[Louis Van Alphen lo...@nucleo.co.za [firebird-support]]

What about virtual environments? If one vm on host is pdc and other vm is db 
server?

Sent from my iPhone

> On 31 Dec 2015, at 2:39, "'Carlos H. Cantu' lis...@warmboot.com.br 
> [firebird-support]"  wrote:
> 
> 
> 
> I know, but that's impossible to do on that server. So the only solution I 
> found so far is to install the application in another computer.
> 
> But anyway I would like to understand why this is necessary in Firebird. It 
> would be great if anybody knows the answers to my questions of my previous 
> message.
> 
> 
> Why do you think the problem only happens with Firebird? Use Google and you 
> will find documents from Microsoft telling to not install MSSQL Server in PDC 
> machines. As I said before, any write intensive application (like RDBMS) will 
> suffer with no cache.
> 

[firebird-support] How does LIST() work?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

The document is quite slim on this. Is it possible to, for a parent row to
retrieve child rows and 'pack' a column from the detail rows into a list
using the LIST function?

 

E.g. something like

 

Select

P.*,

LIST(C.ID)

from PARENT P

join CHILD C on C.PARENT_ID = P.ID

 

to yield

 

P_COLUMN_VALUE 1,2,3,4

 

Thanks

 

 

 

[firebird-support] Gfix returns immediately

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

I have a db that i am checking for corruption. The service stopped while i was 
building an index. When i run gfix with full validation, it runs for a few 
seconds and returns nothing. The db is 2.5gb with tables with millions of rows. 
Doesnt look right. The db does backup and restore fine, but i dont know if a 
b/r cycle is a good test?

Louis

RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I see.. but I agree with you

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 14 October 2015 11:20 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How can I see which query within a stored
procedure execution takes the longest time?

 

  

'Louis van Alphen' lo...@nucleo.co.za [firebird-support] schrieb am
14.10.2015 10:09:

> I think a tool like FBTraceManager from Thomas (Upscene) can do that for
you

Unfortunately not. The Trace API does not provide that level of granularity
for PSQL code modules.

This has been a long discussion in firebird-devel in the past, people
basically argued that the Trace API isn't the right place for that sort of
granularity, but more appropriate for a debugging/profiling feature. I still
believe in the Trace API being a good profiling tool though. ;-)

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> 
> 
> 
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] 
> Sent: 13 October 2015 05:31 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How can I see which query within a stored
> procedure execution takes the longest time?
> 
> 
> 
> 
> 
> Greetings All,
> 
> 
> 
> Firebird 2.5.4
> 
> I would like to know if there is any way that I can retrieve the execution
> time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? 
> 
> Any tool to accomplish this?
> 
> Thanks,
> 
> Mike
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: "Louis van Alphen" 
> 
> 
> ++
> 
> 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
> 
> 
> 





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

RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I think a tool like FBTraceManager from Thomas (Upscene) can do that for you

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 October 2015 05:31 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How can I see which query within a stored
procedure execution takes the longest time?

 

  

Greetings All,

 

Firebird 2.5.4

I would like to know if there is any way that I can retrieve the execution
time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? 

Any tool to accomplish this?

Thanks,

Mike

 

 

 

 





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

RE: [firebird-support] Find grid page containing record

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Granted, I had the luxury of designing my UX from scratch and then implementing 
that system-wide.

 

But yes you need to make your paging work. BTW, what front-end do you use? 
Winforms/Web?

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 October 2015 11:48 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Find grid page containing record

 

  

There is I'm afraid something of a difference between "the UX we might want" 
and "the UI we've got to which we're trying to add particular features".

On 07/10/2015 10:40, 'Louis van Alphen' lo...@nucleo.co.za 
  [firebird-support] wrote:

  

I have taken the approach that it is undesirable to present users with pages 
and pages of data and having the user have to page until he finds what he 
wants. It’s not a great UX. 

My view is to rather give the user powerful search facility to very quickly get 
to the data he wants. So I have adopted a google-like search function where, as 
the users types in a search box, the resultset is filtered according to the 
search specification. The search specification includes the following 
attributes:

- Search text (the text the user enters)

- MatchType (anywhere, exact, startswith, endswith) Exact will generate a where 
clause in the form Column=@parameter, and anywhere will generate a where clause 
like Column=%param% etc

- Case Sensitive

- MaxRows to return. This defines the max nuber of matching rows to return

- EmptySearchAction (Empty or MaxRows) this defines what is returned when the 
search field is empty. Empty means an empty resultset is returned like google. 
MaxRows means the first MaxRows are returned

- List of columns to search on

This search is done server-side by dynamically generated SQL and the result set 
is returned for display. Some searches are quite resources intensive of the 
user chooses columns and sort orders on columns without proper indexes. Most 
front-end grids incl HTML grids do support ordering client side, but if you 
want to order server side i.e. last 50 rows, then you will pay the price

But it depends on the type of UX you want for your users and the use case at 
hand. The traditional paging may be appropriate but maybe some new way as well.

From: firebird-support@yahoogroups.com 
  
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 October 2015 10:37 AM
To: firebird-support@yahoogroups.com  
Subject: Re: [firebird-support] Find grid page containing record

Thanks, hadn't thought of the COUNT(*). This still means visiting every record 
of course, but at least on a good day most of them are being done entirely 
within the database engine. On a bad day however this might not gain anything 
if the user chooses to sort by something useless and unselective (you might say 
they then deserve the delay they get, but we're trying not to build too many 
more of these into the system).

Plus autogenerating the code for the comparison could be "interesting" 
particularly in cases where some of the fields in the ORDER BY (under the 
control of the user, remember) are ASC and some DESC?

I think we'll have to continue to think about it!

On 06/10/2015 21:50, setysvar setys...@gmail.com    
  [firebird-support] 
wrote:

Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com  
    [firebird-support]:
> Given that a query needed to return data for a page of a grid is of the form
>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  plus tables as needed for other fields in the
> SELECT, WHERE and ORDER BY clauses>
> WHERE 
> ORDER BY 
>
> how can I find out what  is for a given  table>.ID?
>
> (Without using any features that are only in Firebird 3, which are
> needed for the solutions I've found so far. Surely this is not a rare
> thing to want to be able to do?)
>
> Scenario: Data is displayed in a grid in the user interface, with 25
> records per page. The user gets to specify the filters (WHERE) and
> sorting (ORDER BY) in the user interface, ie these change outside my
> control (and in consequence appropriate tables get pulled into the FROM
> clause as necessary by the query generation code).
>
> The wanted operation is that the user can say "show me the record with
> ID such-and-such" (by doing something in some other part of the UI), and
> the grid will display the correct page of data (and then scroll as
> necessary and highlight the wanted record, which obviously we'll have to
> do in the UI). In, that is, a sane amount of time - fetching hundreds of
> pages of data to the grid sequentially until the right record appears is
> not a reasonable solution! ID may or may not be the primary key, but we
> can use an indexed un

RE: [firebird-support] Find grid page containing record

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I have taken the approach that it is undesirable to present users with pages 
and pages of data and having the user have to page until he finds what he 
wants. It’s not a great UX. 

 

My view is to rather give the user powerful search facility to very quickly get 
to the data he wants. So I have adopted a google-like search function where, as 
the users types in a search box, the resultset is filtered according to the 
search specification. The search specification includes the following 
attributes:

-  Search text (the text the user enters)

-  MatchType (anywhere, exact, startswith, endswith) Exact will 
generate a where clause in the form Column=@parameter, and anywhere will 
generate a where clause like Column=%param% etc

-  Case Sensitive

-  MaxRows to return. This defines the max nuber of matching rows to 
return

-  EmptySearchAction (Empty or MaxRows) this defines what is returned 
when the search field is empty. Empty means an empty resultset is returned like 
google. MaxRows means the first MaxRows are returned

-  List of columns to search on

 

This search is done server-side by dynamically generated SQL and the result set 
is returned for display. Some searches are quite resources intensive of the 
user chooses columns and sort orders on columns without proper indexes. Most 
front-end grids incl HTML grids do support ordering client side, but if you 
want to order server side i.e. last 50 rows, then you will pay the price

 

But it depends on the type of UX you want for your users and the use case at 
hand. The traditional paging may be appropriate but maybe some new way as well.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 October 2015 10:37 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Find grid page containing record

 

  

Thanks, hadn't thought of the COUNT(*). This still means visiting every record 
of course, but at least on a good day most of them are being done entirely 
within the database engine. On a bad day however this might not gain anything 
if the user chooses to sort by something useless and unselective (you might say 
they then deserve the delay they get, but we're trying not to build too many 
more of these into the system).

Plus autogenerating the code for the comparison could be "interesting" 
particularly in cases where some of the fields in the ORDER BY (under the 
control of the user, remember) are ASC and some DESC?

I think we'll have to continue to think about it!

On 06/10/2015 21:50, setysvar setys...@gmail.com   
[firebird-support] wrote:

  

Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com  
 [firebird-support]:
> Given that a query needed to return data for a page of a grid is of the form
>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  plus tables as needed for other fields in the
> SELECT, WHERE and ORDER BY clauses>
> WHERE 
> ORDER BY 
>
> how can I find out what  is for a given  table>.ID?
>
> (Without using any features that are only in Firebird 3, which are
> needed for the solutions I've found so far. Surely this is not a rare
> thing to want to be able to do?)
>
> Scenario: Data is displayed in a grid in the user interface, with 25
> records per page. The user gets to specify the filters (WHERE) and
> sorting (ORDER BY) in the user interface, ie these change outside my
> control (and in consequence appropriate tables get pulled into the FROM
> clause as necessary by the query generation code).
>
> The wanted operation is that the user can say "show me the record with
> ID such-and-such" (by doing something in some other part of the UI), and
> the grid will display the correct page of data (and then scroll as
> necessary and highlight the wanted record, which obviously we'll have to
> do in the UI). In, that is, a sane amount of time - fetching hundreds of
> pages of data to the grid sequentially until the right record appears is
> not a reasonable solution! ID may or may not be the primary key, but we
> can use an indexed unique field if that helps.

I think this requires several steps.

1) Fetch the ORDER BY fields of the record of interest
2) SELECT COUNT(*) where fields < ORDER BY fields of the record of interest (or 
> if ORDER BY ... DESC)
(this gets you the number of records you can safely skip)
3) Fetch one or more pages of data until the right record appears

This somewhat tedious process is required since your ORDER BY may not start 
with your ID field. 3 is still required since you have limited control of the 
ORDER BY chosen by the user and cannot guarantee how many duplicates there may 
be (ORDER BY SURNAME may not find Tim Ward on the first page of the Ward's).

Sorry for not knowing of any quicker and simpler solution. Unfortunately, the 
desire of a flexible and powerful interface often means that the develope

[firebird-support] Queries & reads with CTE

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I have a query with a couple of CTEs. Only one CTE queries from the tables. All 
other CTES query this CTE and then finally joined up. E.g.

 

with MAIN_CTE as

(

  //Query from tables

),

CTE1 as

(

  // query from MAIN_CTE

),

CTE2 as

(

  // query from MAIN_CTE

)

Etc

 

Select * from 

  CTE1

  Left outer join CTE2

  Left outer join CTE3

Etc

 

If I run MAIN_CTE on its own I get 152K indexed reads

If I run the full query, I get >300K indexed reads

 

So the question is, is the MAIN_CTE evaluated each time another CTE refers to 
it?

 

 

 

 

RE: [firebird-support] Re: RDB$Set_Context v GTT v Disk writes

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

OK great minds think alike…!  8-[]

 

Yes context variable is a way to do it as you suggest. My feeling is that the 
overhead of the context set would likely be small in comparison to all the 
other selects and updates that you would typically do within that request. So 
likely a workable solution.

 

I also write audit records, but I tend to stay away from triggers and SPs. I 
use the DB largely for storage only and of course for it’s SQL engine. So all 
my stuff happens in code in my framework classes (.NET).

 

Louis

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 23 September 2015 01:40 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: RDB$Set_Context v GTT v Disk writes

 

  

Hi Louis,
  Thanks for your reply. My application architecture is the same as yours. The 
user logs onto the middle tier. A session key is generated and stored and all 
further use from that user is associated with that session key and user id. So 
we are both in the position of knowing exactly what user is talking to our 
Middle tier.

  Now to take it a step further, lets say that a user calls a method which will 
update a record in the database. That record will fire an update trigger which 
inserts a row into an audit table. The audit table needs to know the user ID.

  The most obvious way to do this is to pass in the user id with whatever SQL 
is performing the update. That is fine but in my case this will require an 
update to a *lot* of pre existing SQL 

  So I'm looking for a 'cheap' way to perform the following process.

1.) User calls some function on middle tier.
2.) Middle tier grabs a connection from the pool
3.) Middle tier sets the user ID context on the connection with RDB$SET_CONTEXT 
(or some other method..)
4.) Middle tier performs the database update required by the user
5.) The table update trigger fires and reads the user_id from the context 
variable.
5.) Operation finished, the connection is returned to the pool.

The issue here is that the current method, whether using RDB$SET_CONTEXT or 
GTT's requires an extra transaction for every user interaction with the server. 
i.e this is an appreciable amount of overhead.

The idea I'm experimenting with at the moment is to use a UDF to effectively 
call back into the middle tier application to fetch the user id as required.





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

RE: [firebird-support] RDB$Set_Context v GTT v Disk writes

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I use something that sounds similar. But why do you store users ids for a 
connection? The connection should be closed while the user is doing nothing. 
Only opened when db operations are being executed and close when done. This 
then takes advantage of connection pooling. Unclear how you use this.

 

To illustrate, I have a UserSessionManager, but it runs outside the database in 
code in memory. When the user logs in with user/pass, it obviously requires 
some authentication. In my case read(s) from the USER table. If authenticated, 
I create a SessionKey and then put this SessionKey and connectionstring and 
UserId into a dictionary. All subsequent calls to my services layer then takes 
the SessionKey as a parameter. The services layer call then looks up the 
SessionKey in the dictionary, therefore knows the UserId and connectionstring 
and creates and opens a new db connection. This would then come from the pool. 
As soon as the work in the db is done, the connection is closed and it goes 
back to the pool. The UserSessionManager can also time out sessions, i.e. drop 
stale SessionKeys from the dictionary. Everything is outside the DB so that I 
can remain DB independent…

 

Louis

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 23 September 2015 11:43 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] RDB$Set_Context v GTT v Disk writes

 

  

Hello,
  My middle tier application uses a pool of firebird connections to do its 
work. In order to identify users for audit purposes the application currently 
fills a global temporary table with the user id for a connection before doing 
any actual work. This results in an overhead of one transaction and one stored 
procedure call per client call to my stateless server. Using process monitor I 
can see that this equals 5 disk write operations.
  I have experimented with RDB$SET_CONTEXT as well and it is more efficient. It 
needs 3 disk writes for the equivalent operation. I'm guessing that these come 
from the transaction rather then the SET_CONTEXT call.

  Out of curiosity, and in a quest to minimise disk writes, is there a better 
way to do this? Is there some way to call RDB$SET_CONTEXT without a 
transaction? or is it feasible to implement some kind of in memory callback 
mechanism using UDF's. The idea being that the database will only call the udf 
when it needs to.

Many thanks in advance.

Will.





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

Re: [firebird-support] Firebird Embedded on web hosting

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

Ok but the rather copy to a place where you can access it on a fbsrever. 
Opening up other areas of a webserver is a bad idea

Sent from my iPad

On 19 Sep 2015, at 18:41, "amigof...@gmail.com [firebird-support]" 
 wrote:

> i have to  do it,
> 
> 
> my client have a firebird database that run with a local business software .
> 
> and we want to have the same data on the web for read only access 
> so the simplest way is to upload the database to a web host and use firebird 
> embedded to access it with an asp.net web application
> 
> btw, thanks for the answer, i am investigating right now :)
> 

RE: [firebird-support] Firebird Embedded on web hosting

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Any reason why you use embedded and not server version of FB?

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 19 September 2015 10:49 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird Embedded on web hosting

 

  

---In firebird-support@yahoogroups.com,  wrote :

 

> On 18-9-2015 19:50, amigoface@...   [firebird-support] 
> wrote:

> > i have problems on using firebird embedded on my asp.net web host .
> >
> > everything work fine locally but on the web i have an exception
>
> > System.Data.Entity.Core.EntityException: The underlying provider failed
> > on Open. ---> FirebirdSql.Data.FirebirdClient.FbException: Can't
> > create directory "C:\ProgramData\firebird\". OS errno is 5
> > ---> FirebirdSql.Data.Common.IscException: Can't create directory
> > "C:\ProgramData\firebird\". OS errno is 5

...



> For reference error number 5 is access denied. Firebird writes the lock 
> files for a database into C:\ProgramData\firebird but it isn't able to 
> create the folder (you don't have enough rights).

  Correct. 

  One addition - process which hosts Firebird (web server, afaiu) should have 
privileges 
to create folder in "C:\ProgramData". Or you can create it by yourself and 
grant 
privileges to create\delete and read\write files at it 
("C:\ProgramData\firebird") to the account used for web server.

> To redirect the lock files, you need to set the environment variable 
> FIREBIRD_LOCK to the location where you can write.


  Never, never, never do it !!! At least until you 200% sure you really need it 
!

Regards,
Vlad





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

RE: [firebird-support] Reporting for Firebird in Visual Studio 2013 (C#)

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Not sure if this is what you are looking for, but I use DevExpress for all my 
data analytical/visualisation tools. DevExpress have the best reporting & data 
visualisation components by far. Very easy to integrate into C# apps and great 
support.

 

All my apps have an analytics module that allows me and end users to design and 
run pivots, reports and dashboards at runtime. I built a lot of stuff around 
DevExpress to achieve that and make it reusable in all my apps. The metadata 
definitions for reports/pivots/dashboards are stored in FB tables. This means 
that users can create a report, plug in the SQL, including parameters and then 
design the report. 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 02 September 2015 09:44 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Reporting for Firebird in Visual Studio 2013 (C#)

 

  

Hello -- I'm looking for some suggestions on a reporting tool that works well 
within the Visual Studio 2013 IDE using Firebird 2.5.4.

 

I've tried using the Crystal Reports add-in. While it works fine within the 
IDE, when I build and run the app, it keeps asking for my login credentials. 
Have not found a way around this. If you know of one, please let me know and I 
will continue to explore using Crystal Reports.

 

Thanks!

 

Kevin





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

RE: [firebird-support] Strategy to get a sequence in a strict ascending order for a log table

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

You need a persistent FIFO message queue where you insert all committed 
changes. The device consume from this queue in the same order as they were 
committed on the sending side.

Oracle has a great feature set around this…

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 01 September 2015 06:26 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Strategy to get a sequence in a strict ascending 
order for a log table

 

  

I am trying to create a log table. This table has a "version" column, it is an 
incremental number.

In each transaction (ie a purchase, sale,...) , I add a record in this log 
table. The "version" column is assigned in the insert trigger using the value 
of a sequence.

 

This version number is used to update mobile devices. They ask something like 
"send me all changes until version x".

 

 

My problem is I need that the log table shows the records in a strict ascending 
order. It is not important if there are gaps or not. 

 

Example of the problem:

 

1-Star transaction A

2-Changes

3-Insert LOG record, version=1 (get it from sequence)

4-More changes

5-Commit A

 

Now, between the 3 and 5 of A transaction, a concurrent connection do a smaller 
transaction:

 

1-Star transaction B

2-Changes

3-Insert LOG record, version=2  (get it from sequence)

4-Commit B

 

And the commit B is done before the transaction A finishes. Then, in the log 
table, for an instant, I have  a record with the "2" as value. The table will 
not have the "1" until transaction A finishes.

 

If before the A finishes, a mobile device asks for any changes, the server 
sends only "2" ("1" is not visible yet). The device updates its internal state 
and now it is updated to "2". The "1" log will never be applied.





When transaction A finished, the "1" is added in the log, but now it is too 
late because the mobile device has been updated to "2" version.









Which strategy can I use to solve this problem?

 

 

 





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

RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Ah of course yes…

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 21 August 2015 03:08 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB does table scan as soon as I use left outer 
join in view - why?

 

  

21.08.2015 14:26, 'Louis van Alphen' wrote:

> When I have a master with lookup as described; and many rows from the master 
> table reference the same lookup row, is the lookup row cached after the first 
> read or is it read for every master table row that refers to it. If I look at 
> the diagnostic tools I use, it seems to be the latter. If the case, also very 
> inefficient and expensive

Firebird never caches records. However, data pages containing lookup 
rows will be cached inside the buffer cache.

Dmitry





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

RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Yes I was worried about that, but I will check

 

Another releated question I have always had:

 

When I have a master with lookup as described; and many rows from the master 
table reference the same lookup row, is the lookup row cached after the first 
read or is it read for every master table row that refers to it. If I look at 
the diagnostic tools I use, it seems to be the latter. If the case, also very 
inefficient and expensive

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 21 August 2015 01:07 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB does table scan as soon as I use left outer 
join in view - why?

 

  

21.08.2015 13:58, 'Louis van Alphen' wrote:

> Dmitry, re your point 4, do you mean:
>
> create view SKIN as
> select
> ID,
> (select NAME from COLOUR_ C where C.ID = S.COLOUR_ID)
> from SKIN_ S;

Right. Not very good option when you need many lookup fields from the 
same table, but could still be better than a fullscan inside join plans.

Dmitry





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

RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Dmitry, re your point 4, do you mean:

 

create view SKIN as

select

  ID,

(select NAME from COLOUR_ C where C.ID = S.COLOUR_ID)

from SKIN_ S;

 

I will try this thanks

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 21 August 2015 12:25 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB does table scan as soon as I use left outer 
join in view - why?

 

  

21.08.2015 12:14, 'Louis van Alphen' wrote:

> Dmitry thanks for the insight. This actual puts FB in quite a different 
> light. I have used FB since IB 5 and is my DB of choice. However this issue 
> may prove quite problematic.

It does not cover all cases with views, only the ones when you need to 
filter the view using another table (e.g. with IDs to be retrieved).

> I have 2 options: change to another DB or have a major architectural refactor.

3) use the trick I suggested
4) use subselects instead of left joins for lookups inside views
5) sponsor optimizer improvement

May be other workarounds are also possible.

Dmitry





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

RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Dmitry thanks for the insight. This actual puts FB in quite a different light. 
I have used FB since IB 5 and is my DB of choice. However this issue may prove 
quite problematic. Let me explain:

 

I have a large ERP class system ( currently ~400+ tables). An architectural 
design choice in the beginning was to abstract tables with views. So if I need 
to store a Customer object, I will have table CUSTOMER_ and then on top a view 
called CUSTOMER. Doing this, I have several benefits:

-  This allows me to control access better than granting access to 
table level. At least that is my thinking. People accessing the DB cannot 
modify data if they don’t have table access, but can access ‘rich’ views to 
extract data

-  It allows the ‘flattening out’ of an entity when selecting it from 
the DB. E.g. the CUSTOMER view will incluce the currency code from the 
CURRENCY_ table etc. So when my services layer returns the Customer object to 
the client, the CurrencyCode field is populated and is ready for presentation. 
I don’t need multiple calls to the DB to fetch the lookup values to populate 
the Customer object. One of the big tables have 115 columns with about 50 of 
them lookups.

-  Views also allow me to easily implement multi-tenancy on a DB level. 
Therefore the DB will partition the various tenants’ data.

 

This problem now causes, in some queries, very inefficient retrieval of data 
where I did not expect it to happen. So I have 2 options: change to another DB 
or have a major architectural refactor.

 

Regards

Louis

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 21 August 2015 10:19 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB does table scan as soon as I use left outer 
join in view - why?

 

  

21.08.2015 10:26, 'Louis van Alphen' wrote:

> Dmitry, if I then understand you correctly, if a view contains an outer join, 
> then FB will table scan? Is this documented somewhere so that I can read up?

It depends on a number of factors, but generally your conclusion is 
correct. The problem is that the join order always starts with a view 
with a joined table coming afterwards.

This is a known issue, but unfortunately hard to fix.

> Sure, I look at the plan, but the plan is after the fact. It does not show 
> you why?

Right, you cannot see why the optimizer does this or that choice, you 
see only the resulting plan.

> Something like:
>
> select S.*
> from COLLECTION_ COLL
> left join SKIN S on S.ID = COLL.SKIN_ID
> where S.ID is not null
>
> i.e. fake the left join to get the correct join order
> (COLLECTION_->SKIN_->COLOUR_).
>
> Not sure what you are doing here and what the where clause does. Are these 
> tricks documented somewhere?

This is a quite common trick. For inner joins, possible join orders are 
either {COLL->SKIN} or {SKIN->COLL}. We want the join order to be 
{COLL->SKIN} but the optimizer decides differently and chooses 
{SKIN->COLL}. For outer joins, however, the join order is always 
predefined and dictated by the join syntax. So we replace inner join 
with left join to guarantee the desired join order {COLL->SKIN}. But we 
need to exclude the "false" rows produced by the outer join (records 
from COLL having no matches in SKIN), so we add a WHERE clause -- S.ID 
is not null -- to remove those unnecessary rows (I assume S.ID is a 
primary key and thus it should never be NULL unless produced by the left 
join).

Dmitry





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

RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Thanks for the response Dmitry


[Louis]
> The same thing happens in many other queries where I use views and IMHO
> I find it non-sensical. I have come to the conclusion that FB does not
> choose great query plans when using views.

[Dmitry]
True, when views contain outer joins.

[Louis]
Dmitry, if I then understand you correctly, if a view contains an outer join, 
then FB will table scan? Is this documented somewhere so that I can read up?




[Louis]
> It is really hard to tune queries if the results _/seem/_ unpredictable
> and there are no visibilty in how FB executes the query.

[Dmitry]
Plan shows how the query is being executed.

[Louis]
Sure, I look at the plan, but the plan is after the fact. It does not show you 
why?



[Louis]
> Help would be appreciated

[Dmitry]
Something like:

select S.*
from COLLECTION_ COLL
left join SKIN S on S.ID = COLL.SKIN_ID
where S.ID is not null

i.e. fake the left join to get the correct join order 
(COLLECTION_->SKIN_->COLOUR_).

[Louis]
Not sure what you are doing here and what the where clause does. Are these 
tricks documented somewhere?

Thanks
Louis

[firebird-support] FB does table scan as soon as I use left outer join in view - why?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Ok so I have a ‘master table called SKIN_ loosely defined as follows:

 

Table SKIN_



ID bigint not null (primary key)

COLOUR_ID bigint (optional lookup field to COLOUR_ table)

 

For the purposes of convenience, I have created a view SKIN on SKIN_ to include 
the COLOUR name:

 

View SKIN



create or alter view SKIN

as

select

  S.ID,

COL.NAME

  from SKIN_ S

  left outer join COLOUR_ COL on COL.ID = S.SOLD_COLOUR_ID;

 

It uses left outer join COLOUR_ because COLOUR_ID is optional.

 

Then I have a table called COLLECTION_ that refers to a subset of SKIN_ items:

 

Table COLLECTION_



ID bigint not null (primary key)

SKIN_ID bigint (not null & indexed)

 

I want to return all rows in the view SKIN where the SKIN_ID is in COLLECTION_.

 

For this I tried 2 queries, both of which yield same results in terms of query 
plan:

 

select S.*

from SKIN S

where exists

(

  select * from COLLECTION_ COLL

  where S.ID = COLL.SKIN_ID

)

 

And

 

select S.*

from COLLECTION_ COLL

join SKIN S on S.ID = COLL.SKIN_ID

 

in both cases FB does a full scan (300K rows) of SKIN_ to return the 1 or 2 
required. Hardly efficient

 

If I change the view SKIN to:

 

create or alter view SKIN

as

select

  S.ID

  from SKIN_ S

  join COLOUR_ COL on COL.ID = S.SOLD_COLOUR_ID;

 

by removing the outer join, then the proper indexes are used and the results 
are returned. But removing the outer join in the view will drop the rows where 
COLOUR_ID is null and is not want I require.

 

If I put the left outer join on COLOUR_ in the query

 

select S.*

from COLLECTION_ COLL

join SKIN S on S.ID = COLL.SKIN_ID

left outer  join COLOUR_ COL on COL.ID = S.SOLD_COLOUR_ID

I get proper use of indexes and the desired result, but lose the convenience of 
a view.

 

The same thing happens in many other queries where I use views and IMHO I find 
it non-sensical. I have come to the conclusion that FB does not choose great 
query plans when using views. Any others experience the same? 

 

It is really hard to tune queries if the results _seem_  unpredictable and 
there are no visibilty in how FB executes the query.

 

Help would be appreciated

Louis

 

 

 

 

 

 

 

 

 

 

 

 

Re: [firebird-support] Annoying: CTE "CTE2" is not used in query

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

Thinking about it yes i agree. Ultimately, if the CTE is not used, FB need not 
execute the query. No CPU cycles lost. Maybe a warning in the query plan, but 
an error is too restricting
> Unlike Louis, I don't understand why this results in an error, I'd say 
> Firebird is too picky in this case.
> 
> 
> 
>   •   
> • 
> • 

[firebird-support] Annoying: CTE "CTE2" is not used in query

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

See the following SQL below.

If I select from COMBINED, the sql runs but as below I get an error. This is 
pretty annoying when constructing a large SQL with many CTEs and you want to 
test each one and combinations of them. I can get why the error is there, but 
any other suggestion?

 

with

CTE1 as

(

  select 1 as ID from rdb$database

),

CTE2 as

(

  select 2 as ID from rdb$database

),

 

COMBINED as

(

  select * from CTE1

union

  select * from CTE2

)

 

select * from CTE1

RE: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

 

[Louis]   Also, if I remove the order by, IP reports a drastic reduction is
execution time i.e. down to 6msec. Does the ORDER BY on the PK make 
such a
difference?

[Set]My guess would rather be that the ORDER BY forces Fb to fetch all rows, 
whereas without ORDER BY only the first few (random) rows are fetched, 
and that the time for fetching all rows rather than the first row are 
similar.

[Louis]   Unlikely, the table has millions of rows…



[Ann]   You seem to use left outer joins and that can be a problem because (in 
general) the order in which outer joins are declared in the query is the
order in which they have to be accessed in the query plan.

[Set]   Sure, LEFT JOIN limits the optimizer. However, the WHERE clause only 
refers to the TILD (main) table, and the three LEFT JOINs seem to have a 
function similar to subselects used to find lookup values (the last 
three fields in the view). Hence, I'm pretty certain the optimizer would 
have chosen a very similar plan if LEFT had been deleted (it could of 
course have reordered the three "lookup tuples", but they're not related 
anyway (they each join to the TILD table, not eachother), so the same 
indexes should be used.



[Louis]   Correct, the view is just a select from the main table with some 
joins to ‘flatten’ it out so that immediate lookups are pulled in. 


Set





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

RE: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Ann, thanks for your considered response. A lot to digest.

 

The records are inserted in PK order and I need to retrieve in that order as 
the rows ‘follow’ on each other. This table has millions of rows, so a scan 
would take very long so I don’t think the order by does a full scan. 

 

I use outer join because some the FK columns are optional and I need to have 
all rows. But there are in fact a FK that I can change to inner join. 

 

I have removed the order by and now its pretty quick. The sorting on ID is now 
done in the DAL as it is only a few (3) rows that need to be sorted. Pity that 
the ORDER BY of 3 rows takes 100 odd msec. On the dev box with an empty table 
it was also very quick with the order by but obviously with the different 
cardinality of the production data the query changes dramatically. Just 
testament to the fact  that tuning your queries on an empty dev platform is not 
always representative.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 11 August 2015 09:38 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager and 
IB Planalyser

 

  

 

On Aug 11, 2015, at 1:30 PM, 'Louis van Alphen' lo...@nucleo.co.za 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> > 
wrote:

Thanks Ann, so maybe the 2 tools' metric are different. So maybe FBTM
includes all (low level) reads and IP only user table reads.

 

Or maybe one runs some queries before the one it measures so all the system 
table queries are compiled, optimized, and executed, filling the cache with 
good stuff.



 

Also, if I remove the order by, IP reports a drastic reduction is

execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a
difference?



 

 

Unh, maybe.   Between the list, my mail system and yours, I've lost most of the 
formatting and can't look at the original while typing (grrr)   You seem to 
use left outer joins and that can be a problem because (in general) the order 
in which outer joins are declared in the query is the order in which they have 
to be accessed in the query plan.   

 

For example, a three way join of students, registrations, and courses with 
inner joins can be run starting with any of the three tables and get the same 
results.  

 

Select s.student_name, c.course_name 

from students s 

 join registrations r on r.student_id = s.student_id

 join courses c on c.course_id = r.course_id

 

The optimizer can choose to start with registrations and lookup students and 
courses by primary key, or students and look up a registration by student_id 
and a course by the course_id in registrations, or courses -> registrations -> 
students.  Or sort the students and registrations and merge them, looking up 
courses from the matching pairs.  Or other things, probably.

 

However, this query can be executed in only one way: students -> registrations 
-> courses

 

Select s.student_name, c.course_name 

from students s 

left outer join registrations r on r.student_id = s.student_id

 left outer join courses c on c.course_id = r.course_id

 

If that way isn't optimal, too bad.  The query must be executed that way or 
you'll miss all the students who aren't registered for courses and all the 
registrations that don't correspond to any courses.

 

It may be that adding the sort, you've convinced Firebird that it has to read 
and sort all the records in that table, then start from the sorted result.  Try 
using one of the various syntaxes that tells it to return only the first record 
found that matches the criteria.  That may cause Firebird to walk the index ... 
read records in index order ...  rather than reading the table and sorting it.

 

Now that probably sounds really dumb, but in fact, walking a table in index 
order is generally a lot slower than reading it in physical order and sorting 
the result.  Yes, sort is n(log n), but there's a big K applied that is the 
cost of a random record read.  So, if you expect a lot of results, sort first 
and ask for matches later   If not, use 'Select FIRST ' or 'LIMIT ' 
or whatever the standards committee chose to bless ... maybe  'FETCH FIRST 
'.  Any of those hints will convince Firebird to walk the table in index 
order.

 

This query is likely to read and sort the students first, then start looking 
for the rest of the data unless there's a good index on student_name.  

 

Select s.student_name, c.course_name 

from students s 

left outer join registrations r on r.student_id = s.student_id

 left outer join courses c on c.course_id = r.course_id

 where s.student_name = 'Dinah MacQuarie'

 order by s.student_id

 

This may give a faster result in the absence of an student_name index.  It may 
not, but do consider trying it when playing with query forms...

 

Select first 30 s.student_name, c.cours

RE: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Thanks Ann, so maybe the 2 tools' metric are different. So maybe FBTM
includes all (low level) reads and IP only user table reads.

My other question is why is there 'WARNING' against some of the indexes? Are
the stats out of spec?

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 11 August 2015 06:49 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager
and IB Planalyser

 

  


> On Aug 11, 2015, at 12:22 PM, 'Louis van Alphen' lo...@nucleo.co.za
[firebird-support]  wrote:
> 
> Sure I understand that, but the reads/fetches are >3000 to return 1 row.

Which involves a four way join, on top of having to find and use the system
tables to identify the user tables, columns, and indexes, then find the
appropriate index pages, pointer pages, data pages, etc. for it's internal
queries and your query. The second time, most of the heavily used stuff is
in cache.

Good luck,

Ann
> 
> 
> 
> From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
> Sent: 11 August 2015 06:15 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Reads inconsistency between FB
TraceManager and IB Planalyser
> 
> 
> 
> 
> 
> Hi Lois,
> 
> Reads value means actual reads from the disk.
> If you run query twice, necessary database pages are cached, and you will
see in stats more "fetches" - i.e., calls to cache, not record fetches.
> 
> Actually, always is better to run query twice, to get heated cache.
> 
> Regards,
> Alexey Kovyazin
> IBSurgeon
> 
> 
> 
> 
> 
> 
> 
> 
> 
> I am trying to find a performance issue in a system and I am using 2 tools
just for comparison and verification. One being FB TraceManager (FBTM) and
the other is an old util called
> 
> Interbase Planalyzer (IP)
> 
> 
> 
> It seems that the particular query from the view
TRACKED_ITEM_LOCATION_DETAIL is slow.
> 
> From FBTM,while monitoring the app, I get the following raw output:
> 
> 
> 
> --
> 
> 2015-08-11T16:35:52.4990 (6180:00F9A948) EXECUTE_STATEMENT_FINISH
> 
> D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION
DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE,
TCPv4:127.0.0.1)
> 
> (TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)
> 
> Statement 3407:
> 
> --
> 
> select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID
> 
>

^^^
> 
> PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX
INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)),
TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)),
TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX))
> 
> param0 = bigint, "2147191655"
> 
> 1 records fetched
> 
> 112 ms, 3316 read(s), 3325 fetch(es)
> 
> Table Natural Index Update Insert Delete Backout Purge Expunge
> 
>

***
> 
> USER_ 1
> 
> TRACKING_LOCATION_ 1
> 
> TRACKED_ITEM_LOCATION_DETAIL_ 1
> 
> --
> 
> 
> 
> 3325 fetches and 112ms seem way over the top. The cache hit ratio is 0%.
> 
> 
> 
> However, when I use IP and manually enter the same SQL, I get the
following:
> 
> Prepare time 1ms
> 
> Execution time 148ms
> 
> Fetch time 4ms
> 
> With a total of 7 fetches. Not sure where FBTM gets 3K fetches?
> 
> 
> 
> Also, if I remove the order by, IP reports a drastic reduction is
execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a
difference?
> 
> 
> 
> I am not understanding what is going on.
> 
> 
> 
> Either way, here are the table & view info & stats
> 
> 
> 
> Thanks
> 
> Louis van Alphen 
> 
> 
> 
> --
> 
> CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ (
> 
> UID DOM_UID /* DOM_UID = VARCHAR(36) */,
> 
> ID DOM_ID /* DOM_ID = BIGINT NOT NULL */,
> 
> IS_DELETED DOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL CHECK ((
value in ( 0,1) )) */,
> 
> CREATED_DTM DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP */,
> 
> CREATED_USER_ID DOM_FK /* DOM_FK = BIGINT */,
> 
> ROW_ORIGIN DOM_FK /* DOM_FK = BIGINT */,
> 
> ITEM_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */,
> 
> LOCATION_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */,
&g

RE: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Sure I understand that, but the reads/fetches are >3000 to return 1 row.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 11 August 2015 06:15 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager and 
IB Planalyser

 

  

Hi Lois,

Reads value means actual reads from the disk.
If you run query twice, necessary database pages are cached, and you will see 
in stats more "fetches"  - i.e., calls to cache, not record fetches.

Actually, always is better to run query twice, to get heated cache.

Regards,
Alexey Kovyazin
IBSurgeon







  

I am trying to find a performance issue in a system and I am using 2 tools just 
for comparison and verification. One being FB TraceManager (FBTM) and the other 
is an old util called

Interbase Planalyzer (IP)

 

It seems that the particular query from the view TRACKED_ITEM_LOCATION_DETAIL 
is slow.

>From FBTM,while monitoring the app, I get the following raw output:

 

--

2015-08-11T16:35:52.4990 (6180:00F9A948) EXECUTE_STATEMENT_FINISH

D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION 
DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE, 
TCPv4:127.0.0.1)

(TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)

Statement 3407:

---

select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID

^^^

PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX 
INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)), 
TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)), 
TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX))

param0 = bigint, "2147191655"

1 records fetched

112 ms, 3316 read(s), 3325 fetch(es)

Table Natural IndexUpdateInsert
Delete   Backout Purge   Expunge

***

USER_ 1

TRACKING_LOCATION_1

TRACKED_ITEM_LOCATION_DETAIL_ 1

--

 

3325 fetches and  112ms seem way over the top. The cache hit ratio is 0%.

 

However, when I use IP and manually enter the same SQL, I get the following:

Prepare time 1ms

Execution time 148ms

Fetch time 4ms

With a total of 7 fetches. Not sure where FBTM gets 3K fetches?

 

Also, if I remove the order by, IP reports a drastic reduction is execution 
time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference?

 

I am not understanding what is going on.

 

Either way, here are the table & view info & stats

 

Thanks

Louis van Alphen 

 

--

CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ (

UID   DOM_UID /* DOM_UID = VARCHAR(36) */,

IDDOM_ID /* DOM_ID = BIGINT NOT NULL */,

IS_DELETEDDOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL 
CHECK ((  value in ( 0,1) )) */,

CREATED_DTM   DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP 
*/,

CREATED_USER_ID   DOM_FK /* DOM_FK = BIGINT */,

ROW_ORIGINDOM_FK /* DOM_FK = BIGINT */,

ITEM_ID   DOM_FK NOT NULL /* DOM_FK = BIGINT */,

LOCATION_ID   DOM_FK NOT NULL /* DOM_FK = BIGINT */,

FROM_LOCATION_ID  DOM_FK /* DOM_FK = BIGINT */,

START_DTM DOM_DTM NOT NULL /* DOM_DTM = TIMESTAMP */,

END_DTM   DOM_DTM /* DOM_DTM = TIMESTAMP */,

START_DAT DOM_DAT NOT NULL /* DOM_DAT = DATE */,

END_DAT   DOM_DAT /* DOM_DAT = DATE */

);

 

ALTER TABLE TRACKED_ITEM_LOCATION_DETAIL_ ADD CONSTRAINT TILD_PK PRIMARY KEY 
(ID)

USING INDEX TILD_PK_IDX;

 

 

CREATE INDEX TILD_END_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (END_DAT);

CREATE INDEX TILD_ITEM_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (ITEM_ID);

CREATE INDEX TILD_LOCATION_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (LOCATION_ID);

CREATE INDEX TILD_START_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (START_DAT);

 

 

 

 

CREATE OR ALTER VIEW TRACKED_ITEM_LOCATION_DETAIL(

UID,

ID,

IS_DELETED,

CREATED_DTM,

CREATED_USER_ID,

ROW_ORIGIN,

ITEM_ID,

LOCATION_ID,

FROM_LOCAT

RE: [firebird-support] Firebird migration to increase perfomances

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

The common mistake many porogrammers make when coming from a DB like Paradox
is to treat FB like Paradox. Especially when using a tool like Delphi. Been
in that boat many years ago. 

 

To solve the problem, just apply one simple rule: No data goes into or comes
out of FB without an explicit SQL command. Often it means a major redesign
of your app (especially Delphi apps), but you just have to go that route.

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 22 June 2015 10:01 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird migration to increase perfomances

 

  

>> There can be lots of reasons for a database application being slow. If
>> the database have huge tables, and the application is written thinking
>> in terms of tables as opposed to datasets, then things will be slow
>
>Svein.
> An interesting comment, to me anyway, regarding tables vs datasets. I'm
>no SQL guru, I just use Firebird as the backend of an app I'm writing,
>but would you mind elaborating on what you've said. Just some
>"buzzwords"/key words I should use on a google search would be greatly
>appreciated.

Sure, Andrew, this is nothing complicated at all.

Once upon a time I used Paradox, a desktop database that I consider quite OK
for small tables and single users. However, tables with one hundred thousand
rows or so made things slow down considerably.

Using Firebird the Paradox way, i.e. loading all records and all fields
every time is a bad idea except for tiny lookup tables. Firebird is good at
finding the fields of the records you're interested in - including joining
tables or doing some minor calculations upon the fields. Using Firebird
simply as a place to store tables and doing all selection in code in your
app rather than using a WHERE clause will make things slower - you may
compare it to me prefering to take what I want to eat out of the fridge
rather than take everything out of the fridge, find what I want to eat and
put the rest back into the fridge. Similarly, I prefer to name the fields I
need, rather than use SELECT * (there are exceptions, of course).

Generally speaking, a result set should only contain the data that you
require for whatever you're doing, and the smaller the result set is, the
better.

Using Firebird the client/server way with appropriate indexing, it doesn't
normally matter all too much how big tables and databases are, although I
must admit that I'm only used to working with tables with a few million
rows, and have no experience with really big databases.

Set





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

Re: [firebird-support] What is recommended modeling tools for Firebird

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

We use enterprise architect from sparx systems. Uses UML. I do all my modelling 
in there. Db, classes, behaviour etc. the nice thing is that everything is then 
in one place. You also generate DDL scripts from there or reverse engineer. 
Also generate docs from the model as well as interactive html/js based model 
browsing

Sent from my iPad

On 16 Jun 2015, at 11:04, "Slavomir Skopalik skopa...@elektlabs.cz 
[firebird-support]"  wrote:

> Hi All,
> 
> we are looking for new modeling tool that will replace our unsupported 
> CaseStudio2.
> 
> I was made quick look at
> 
> http://www.modelsphere.org/
> and
> http://www.toad-data-modeler.com/enu/default.aspx
> 
> but I not sure that is right choice.
> 
> We needs:
> -model diagram with sub models
> -user scripting
> 
> Which tool are you using or can recommend?
> 
> Thank you Slavek
> 
> -- 
> Ing. Slavomir Skopalik
> Executive Head
> Elekt Labs s.r.o.
> Collection and evaluation of data from machines and laboratories
> by means of system MASA (http://www.elektlabs.cz/m2demo)
> --
> Address:
> Elekt Labs s.r.o.
> Chaloupky 158
> 783 72 Velky Tynec
> Czech Republic
> --
> Mobile: +420 724 207 851
> icq:199 118 333
> skype:skopaliks
> e-mail:skopa...@elektlabs.cz
> http://www.elektlabs.cz
> 
> 

RE: [firebird-support] Re: Maximum Number of Connections 2.5.3

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

But surely you would not have all 7000 connections open at the same time all 
the time? That is bad practice. Connection should only be open for the duration 
of the interaction with the DB. After a query it should be closed and disposed. 
Then it is unlikely that you would need 7000 connections for 7000 users. 
Depends on the frequency of interaction

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 02 June 2015 04:26 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Maximum Number of Connections 2.5.3

 

  

Hi Dmitry, thank you for your answer.  For may DB connection to FB, I'm using 
TIBCConnection (IBDAC) (Specifically for Firebird) by DevArt.  In the 
documentation, it talks about connection pooling, and says that a connection 
will only be re-used if all the parameters are the same between a requested new 
connection and an existing connection, including username and password.  With 
7000 distinct users, how can connection pooling help in this situation?  
Wouldn't I need 7,000 connections.  I don't see how pooling will help.

Alternatively... I could just not pool my connections, and just use Classic and 
that would solve the problem, is that correct?





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

RE: [firebird-support] Advice requested on design pattern

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Q1:  We have an application with around 400 table and we use this structure for 
all tables. I.e. apart from the payload columns, all tables have these 
housekeeping fields. However our app framework populates these tables as the 
app logs in with it’s own single user. Due to a complex security and 
permissions model, the app has it’s own user model that the DB does not know 
about. The advantage of this is that we have a base class in the app domain 
(C#) that implements these properties. All DataObjects then derive from this. 
Our app is OLTP and the advantage of having the app populate the date fields is 
that for testing, we can centrally alter the timebase the app runs at without 
having to mess with server time and the consequences of that. 

 

Yes FB is a RDBMS and not OO DB. In a previous project I used ‘inheritance’ 
where I put common fields in a ‘base’ table and other fields in a ‘derived’ 
table and then a view on top. The 2 tables are then linked via a PK with same 
value. The problem I had was when I queried the view, it only used indexes of 
one table, depending on wich one was first in the select in the view. It 
ignored any indexes from the other table. So this fell flat. This was in FB 
2.1. I would not advise doing this.

 

Q2: Our approach is to have as few triggers / SPs in the DB. All updates and 
exchanges with the DB through our generic data access layer. The only place 
where we use triggers is to manage a RowVersion column after insert and update 
and reject concurrent row updates. I.e. where the users or system updates stale 
data. In our experience, SPs and triggers become messy very quickly and is hard 
to debug. Been bitten once too many times.

 

You also simply don’t grant access to casual users to the tables. The app uses 
a user with all grants in place. Our convention is to use a table name such as 
CUSTOMER_. Then we on top of that we put a view called CUSTOMER. The view also 
brings in some columns from ‘lookup’ table such as CURRENCY, etc. Normal users 
using reporting tools etc only get granted select access on the view and not 
the underlying table.

 

Q3: You can clear the RDB$PROCEDURE_SOURCE field in the RDB$PROCEDURE system 
table containing the SP and trigger definition. But take care to keep the 
scripts somewhere for backup.

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 19 May 2015 11:35 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Advice requested on design pattern

 

  

I am creating a database that consists of 30 - 40 tables. 

Each table stores a specific set of data which has a specific set of 
attributes. There are also attributes common to all tables for example:

*   UID (from generator)
*   Name
*   Description
*   DateTime Created (audit field)
*   DateTime Modified (audit field)
*   User Created (audit field)
*   User Modified (audit field)

Most tables will contain a few thousands of records, some of them may be 
largish blobs such as photos but mostly it will be plain text and HTML.

Normally insertions and updates would be infrequent but retrieval needs to be 
as fast as possible. The data is being displayed in a relatively simple client 
written in C++ and using IBPP.

Q1: I understand that Firebird does not support table inheritance. Therefore is 
it better to create a 'COMMON_FIELDS' table and then join the 'specialised' 
tables to it or include the common fields (i.e. the list above) in each and 
every table?



The 'created' and 'modified' audit fields should be updated automatically and 
obviously I will use a trigger to do this. One advantage I can see of using a 
single COMMON_FIELDS table is that I can write one stored procedure and call it 
from the 'before' trigger of each table. Alternatively in the case where the 
common fields are repeated in each table I could pass the table name as a 
parameter to the SP.

Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure 
with a table name from a trigger and have the SP take care of the audit field 
updates (not exactly sure how to do this yet)?



It would be good if the audit fields were not easy to fiddle with (i.e. by 
someone using FlameRobin or isql). The obvious way would be for them to be 
'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in 
the application.

Q3: Is it possible to hide the SP from a casual observer or would it be better 
to write a UDF for the obfuscation?

I appreciate that the answer may be "Depends ... " but I would appreciate 
general guidance or opinions where it isn't possible to provide a definite 
answer.

Many thanks for the help!!







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

RE: [firebird-support] I need a Firebird DB "Documenter" Utility

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I do it the other way around. I use Sparx-Systems’ Enterprice Architect to 
document/capture my ERD and generate the DDL from there. The diagrams are all 
UML and makes provision for documentation as part of the table and column 
specifications. EA can also generate documentation and even an interactive 
HTML/JS html pages to drill throughout the model. My entire application’s 
design from tables to class, flows etc are captured in the model. That way 
everything is in one place and not bits & pieces in various apps. EA can also 
reverse engineer your DB

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 05 April 2015 08:43 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] I need a Firebird DB "Documenter" Utility

 

  

I've been designing FB DBs for years just using FlameRobin.  It works great for 
what I need to do, and I can use Delphi's modeler to show an ERD to fellow 
developers when necessary. However, to explain how the DB relationships work to 
a customer is still a challenge.  Customers need a more aesthetically pleasing 
output.  The output has to fit nicely on a paper analog display (.pdf, 8.5 x 11 
pages)

I need a utility that will do the following:

- Automatically read my existing Firebird 2.5x DB and create an ERD
- ERD must include Table and Field descriptions that I entered into Firebird
- Must be able to output a report suitable for presentation to a customer who 
only understands paper analog reports.  (Example, each page is for one table, 
interconnecting lines are abbreviated to arrows, nice fonts and colors)
- ERDs are a bit overwhelming for people not used to reading them.  Remember 
that there is no "scroll" capability on a piece of paper, so it's not good to 
have an ERD that is the equivalent of 50" x 100" in size, for the target 
audience.

Does anyone have any suggestions?  Commercial license or free is ok.  Thanks in 
advance for any help you can provide.

 





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

Re: [firebird-support] Re: Get/set context in derived table context

[Louis Van Alphen lo...@nucleo.co.za [firebird-support]]

Given your requirement that is the best option and the safest from a 
compatibility point of view

Sent from my iPhone

> On 20 Mar 2015, at 13:19, "masb...@za-management.com [firebird-support]" 
>  wrote:
> 
> So, I now used two small SPs with row_count and joined the output. Works fine 
> for me.
> 
> Thanka and kind regards
> Christian
> 
> 

Re: [firebird-support] Re: Get/set context in derived table context

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

To be really honest, i would not use this 'fragile hack' in a production system 
for queries. It is not std SQL and like not guaranteed to work as currently in 
future versions. I only used it to format a temporary report.

Sent from my iPad

On 19 Mar 2015, at 13:46, "masb...@za-management.com [firebird-support]" 
 wrote:

> What I initially wanted to do with this approach is join two datasets from 
> different and different context variables sources with the row numbers as 
> join criteria, which does not work at all, even when the individual queries 
> show matching row numbers.
> 
> BTW I start all processes with a set_context step, to set the desired 
> starting point for both variables to make the row number match.
> 
> Kind regards from Hamburg
> Christian
> 
> 

RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Of course, using get_context and set_context is a great way to implement true 
multi-tenancy at DB level in Firebird. Multi-tenancy can be dangerous if 
implemented in middleware or SQLs because programmers make mistakes and can 
forget a where clause. What we want to do is to have the DB abstract that for 
us.

 

Create the following table:

 

CREATE TABLE MY_TABLE (

IDBIGINT NOT NULL,

TENANT_ID BIGINT NOT NULL,

OTHER_COLUMN  VARCHAR(64)

);

 

And trigger

 

/* Trigger: MY_TABLE_BI0 */

CREATE OR ALTER TRIGGER MY_TABLE_BI0 FOR MY_TABLE

ACTIVE BEFORE INSERT POSITION 0

AS

begin

  NEW.TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from 
rdb$database);

end

^

SET TERM ; ^

 

We see that the trigger populates the TENANT_ID for us from a context variable 
that is valid for the whole session.

 

Now what we want is to have our view only return rows belonging to the 
logged-in tenant. I.e.:

 

create view VW_MY_TABLE as

select *

from MY_TABLE

where TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from 
rdb$database);

 

So all you need to do at the beginning of the session is to set the context:

select RDB$SET_CONTEXT('USER_SESSION','TENANT_ID',1) from rdb$database

 

and insert data

 

insert into MY_TABLE

(ID,OTHER_COLUMN)

values

(1,'Dome value')

 

Select * from VW_MY_TABLE will return our row

 

Change to a different tenant

select RDB$SET_CONTEXT('USER_SESSION','TENANT_ID',2) from rdb$database

 

Select * from VW_MY_TABLE will now return empty

 

Pretty cool… You just jave to watch your indexing

 

 

 

 

RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Actually not hard to understand if you break it down a little and just rewrite 
it a bit

 

Here is the original

select
cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),
< other columns>

from MY_TABLE

 

so

 

the while section

coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)

is purely there to cast the get_context to integer and then coalesce it to zero 
for the first row when the context does not yet exist. Let’s replace the 2 
instances with GET_ROW_NUM for clarity

 

therefore

 

select
GET_ROW_NUM as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
GET_ROW_NUM + 1),
< other columns>

from MY_TABLE

 

but the second GET_ROW_NUM is actual part of the set_context so we just move it

 

 

select
GET_ROW_NUM as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',GET_ROW_NUM + 1),
< other columns>

from MY_TABLE

 

and replace (for clarity) set_context(scope,name,value) with SET_ROW_NUM(value)

 

then it becomes

 

select
GET_ROW_NUM as row_number,
SET_ROW_NUM(GET_ROW_NUM + 1),
< other columns>

from MY_TABLE

 

this makes it much simpler to read. 

 

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 March 2015 06:44 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How To Generate Auto Increment Number in SQL 
Itself ?

 

  

 

On Mar 13, 2015, at 8:45 AM, Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support] mailto:firebird-support@yahoogroups.com> > wrote:

 

 

I have EmpCode column in my SQL

 

 

Say for example, my sql returns total 100 records in that it returns 10 records 
for every employee. Here I would I display Row_Number as 1 for first 10 records 
i.e. for first employee, then for next 10 records i.e. for second employee 
Row_Number as 2, and so on. So finally Row_Number value should endup with 10.

 

How about a second context variable for the EmpCode and reset the row_number 
iff the EmpCode changes?  Or, if you'd rather number each row for an employee 
differently and start again at 1 for the next employee, increment the 
row_number unless EmpCode changes then reset to 1.

 

Sigh.  SQL used to be such a simple ugly stupid language   Am I the only 
one who finds this sort of magic unreadable?

 

Cheers,

 

Ann











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

RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Thanks… I needed it to be able to structure a complex report. 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 March 2015 02:35 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How To Generate Auto Increment Number in SQL 
Itself ?

 

  

Louis,

That's some clever get/set_context usage.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as row_number,

rdb$set_context('USER_TRANSACTION', 'row#',

coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 
1),

< other columns>

from MY_TABLE





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

RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

select

 

cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as row_number,

rdb$set_context('USER_TRANSACTION', 'row#',

coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),

< other columns>

from MY_TABLE

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 March 2015 01:58 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How To Generate Auto Increment Number in SQL Itself 
?

 

  

Hi All,

 

I have one question, any SQL I write, I would like to get auto-increment number 
(separate column obviously) to every row starting with 1. It is just to 
numbering every record I get as an output of my sql. I do not want to use 
Generate which we do on table. This is just for SQL output I need.

 

Thanks In Advance.

 

With Best Regards.

 

Vishal

 

 





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

RE: [firebird-support] Distributing/deploying Stored Procedures

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Such a function is a disaster waiting to happen. No tool can recreate any
data transformations / data refactoring that is usually part of a release.

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 March 2015 11:06 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Distributing/deploying Stored Procedures

 

  

On 13/03/2015 00:37, 'Andrew Zenz' and...@aimsoftware.com.au
  [firebird-support] wrote:

  

Or must I ship ISQL.exe with my application and INPUT scripts? 


Even if you do that the dependency issues will be a right pain, it'll be a
lot of hard manual work to put the scripts together and test them.

What's missing from most (all?) database engines is a version control system
integration and a single command which says "update all the various bits of
this database to match what's changed in the version control system" without
the user having to worry about the dependencies, which really are a database
internal gubbins issue that the database should look after.



-- 
Tim Ward





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

RE: [firebird-support] Distributing/deploying Stored Procedures

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Just in terms of DDL upgrades and releases, I take the following approach:

-  All DDL updates are scripted. Absolutely no click-click in a DB 
tool. This way I can also put my scripts in version control

-  I have only one stored proc in my DB. It is called 
GET_DATABASE_VERSION. It simply returns a string that indicates the DDL version 
of the DB. E.g. ‘2.3’ where 2 is the DDL version and 3 is the customer specific 
DDL version for e.g. reports and data fixes that have been run, etc.

-  With an update, say V2, I have one script file that runs all the 
others in correct sequence. It also updates the version SP to indicate the new 
DB DDL version

 

An automated upgrade tool can then determine the actual DDL version of the DB 
(it could be different at various customers) and then run the appropriate 
updates to bring the DB to the correct version

I would recommend against dropping / creating SPs to update because you will 
likely have dependencies. Just use CREATE OR ALTER PROCEDURE

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 12 March 2015 04:30 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Distributing/deploying Stored Procedures

 

  

I’m a bit of a FB noob and am after some advice about Stored Procedures and the 
best way to distribute them to client sites.

 

We are in the process of redeveloping our software using FB as the back end (it 
was previously ISAM).  We have a reasonably large client base and our 
application is installed on many sites which run independently.

 

We intend to make use of Stored Procedures, Triggers etc and between releases 
(during development) these objects will be created, dropped, modified etc.  I 
have been tasked with finding a solution to distribute the update/upgrade SP.

 

At the client end they will access the data via our application (via ODBC).  
They will have no direct access to the FB server and no ISQL command.

 

I can pump SQL statements through the application to make certain changes the 
first time a new release is run (alter table etc) but am concerned at how I am 
supposed to update SPs (potentially a large number) without access to the FB 
server.  I considered extracting the metadata(FlameRobin), deleting superfluous 
statements and pumping what is left through the app but that seems like A LOT 
of work.

 

Also, do I bother with ‘alter procedure …..’ or do I simply drop them and 
(re)create them (after all, at release time, the SP is the SP)  

 

I am trying to automate the task as much as possible, I don’t want our support 
staff to have to intervene in an upgrade.

 

Surely I’m not the only one that has needed to do such a thing.  Does such a 
mechanism exist? Perhaps some suitable suggestions?

 

Cheers for now,

 

Andrew

 

I hope I explained my predicament sufficiently, basically I want to be able to 
duplicate SP etc from a development system to a production system easily with 
as much automation as possible.

 

 





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

RE: [firebird-support] Newbie question on how to hold an unusual data type

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

For storage purposes I would use normal column types, ints, whatever. For 
duplicate checking, I would in the app, calculate a unique hash from all the 
necessary fields taking part in the duplicate check. That hash goes into a 
varchar column that has a unique constraint. The db engine then enforces 
uniqueness..

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 06 March 2015 03:59 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Newbie question on how to hold an unusual data type

 

  

Hi all, 

I have volunteered to write a statistics-type program in
Lazarus/FreePascal under Linux and I need an embedded database, that
means it has to be Firebird. I've used a lot of database software
before, but never Firebird (nor Interbase, for that matter). I do NOT
want to force users to install a full-blown database server in order
to use the software. 

My problem is that I must avoid duplicated records in the database,
the unique key is a complicated structure containing four 16-bit words
plus a 108-bit set of flags. The combination of the whole lot must be
unique. I don't need to retrieve this data other than to check for
duplicate records, so I can massage it in FreePascal so that it can go
into the database in any form that's desirable. 

If I were using PostgreSQL, I'd store the whole lot as a single
172-bit bitstring, but I can't find any mention of an equivalent data
type in the Firebird documentation that I've been able to find. 

So, how would you store this data for greatest speed/efficiency in
checking for duplicates, please? I'm looking at a few million records
in the database, and there's a record size of around 350-400 bytes. 

Thanks, 

Brian. 





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

RE: [firebird-support] Re: Speed issues

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

With “issues” I mean rather I am in sort of the same situation. Firstly, I am 
not always 100% sure how to read and interpret the query plan. I basically try 
to eliminate NATURAL (full table scan) in any query. I also try to check the 
number of indexed and non- indexed reads. But when there is something wrong I 
am not always sure where to look and how to use the query plan to remedy the 
situation.  Also, when checking the query execution, it seems that my indexes 
stats are not good and I am not sure how to remedy this. 

 

I don’t want to hijack Zoran’s thread and will post my own in due course.

 

Thanks for the response Set

 

Louis

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 09 February 2015 12:00 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Speed issues

 

  

>I am having similar issues. Are there any resources to read up on this? 

What do you mean by "similar issues", Louis? Zoran first showed us his SQL and 
plan, so that we more or less could see that his SQL, plan and indexes seemed 
good (that's arguably the most common reason for speed problems). Unfortunately 
I don't know of any generally good resources for fixing speed issues with 
Firebird (excepting this list and sometimes firebird-devel, possibly The 
Firebird book by Helen Borrie can have some tips as well, I don't know), and I 
doubt there exist any good resources focusing particularly on speed problems 
with big tables (whereas I cannot rule out there could exist some good 
resources for more general speed problems). 

I never answered Zorans question since I've no experience with tables with more 
than 100 million rows and Seans answers seemed sensible. I noticed two things 
that they haven't discussed (but that I don't know how relevant would be) and 
that is that we haven't seen the DDL of his tables (wondering whether any of 
the tables involved have lots of fields or large CHAR and VARCHAR fields that 
could affect performance) and that we don't know how many rows would be 
returned from the same query if DISTINCT was removed. 

Set 





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

RE: [firebird-support] Re: Speed issues

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I am having similar issues. Are there any resources to read up on this?

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 08 February 2015 12:09 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Speed issues

 

  

I am doing restore now with 8192 page value, but I will not know before 
tomorrow. It will not be finishen in next 90 minutes, and I all be sleeping 
until tehn, probably.

Thanks for your advices - I will advice tomorow about changes. 

Also, what do you think - when I put it on working server - should I use 
SuperServe or Super Classic ?
I will have about 40-50 connections to the database.

Until recently, I was doing it with different version (i mean diferent 
organized data) of database, it was superserver, and performance was ok. Only 
the first queries in the morning where slow, but after that I believe the 
shared cache on the server side was doing job well, i think.

Regards,

Zoran





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

RE: [firebird-support] Re: Optimising High Latency Database Connections

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

OK then I stand corrected. But I still prefer a services layer approach

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 21 January 2015 03:09 PM
To: 'Louis van Alphen' lo...@nucleo.co.za [firebird-support]
Subject: Re: [firebird-support] Re: Optimising High Latency Database Connections

 

  

LvAlnczfs> Personally I won’t connect directly over a high latency
LvAlnczfs> connection. You risk inconsistent updates and corruption.

You are wrong. You cannot corrupted database because you are using a
connection over a high latency network. The same applies for
inconsistent updates (that's why transaction control exists).

Btw, FB 3 has a much better protocol performance in such networks, as
seem in my published test results:
http://www.firebirdnews.org/testing-the-firebird-3-protocol-enhancements/

[]s
Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br





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

RE: [firebird-support] Re: Optimising High Latency Database Connections

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Personally I won’t connect directly over a high latency connection. You risk 
inconsistent updates and corruption. You should rather use a services approach

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 21 January 2015 01:08 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Optimising High Latency Database Connections

 

  

Hi Gareth,

check this out: Devrace FIBPlus: Optimization of network traffic in Delphi and 
C++ Builder applications  



  

  Devrace FIBPlus: 
Optimization of network traff... 

Devrace FIBPlus: Optimization of network traffic in Delphi and C++ Builder 
applications 




  View on www.devrace.com 

Preview by Yahoo 




 

I know that you are using different components but maybe it will help you 
anyway (some geberal rules that might apply to you case).





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

RE: [firebird-support] Re: Firebird & Android

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Assuming you know how to develop webservices 

 

http://www.codeproject.com/Articles/304302/Calling-Asp-Net-Webservice-ASMX-From-an-Android-Ap

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 16 January 2015 04:10 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Firebird & Android

 

  

I am facing the exact same issue with an app I want to develop.  Is there a 
place you can point me to to learn more about the options and how to implement 
them?  I recognize the issues presented, just not sure what the best approach 
is to solving them .





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

RE: [firebird-support] Re: Firebird 2.0 Support Windows Server 2012?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Even though I am running 2012 in VM and tuned all the cache parameters, I could 
not get the performance to what I expected

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 November 2014 01:41 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Firebird 2.0 Support Windows Server 2012?

 

  

> But you likely changed from 32bit Win2003, so you are experiencing the 

> expected problem of Window Cache/Pages management with 64bit OS. 

> Please google this subject, there is plenty written about it. 

 

Shouldn't this important information be part of the FAQ or in other prominent 
place?

 

Sven - try googling "Firebird and windows cache" it gives useful results.



Tom

 





 

 





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

RE: Odp: [firebird-support] FB server installation a nd concurrent users limits on Windows Server

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Quite honestly if you have a user base of 3 to 5 clients then it is unlikely 
that you will have major performance issues anyway. Except if they move large 
amounts of data in the database. With all due respect, my feeling is that you, 
being new to databases (that is what I understand you said) , will have bigger 
performance issues as a result of suboptimal database design, app design & 
queries not optimised. I think the server is the least of your worries at this 
stage. 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 November 2014 01:43 PM
To: firebird-support@yahoogroups.com
Subject: Re: Odp: [firebird-support] FB server installation a nd concurrent 
users limits on Windows Server

 

  


Hi Mark

thank you very much! your answer is very detailed 

 

so :

 

>Based on your link below, you are confusing the specific problems of 
>running on an active directory server with a problem with the windows 
>>filesystem cache on 64 bit windows. An issue that has been addressed in 
Firebird 2.5.2.

that problem is about Firebird , and with that newer versions from 2.5.x to 3 
is solved?!

but the "solution" was made just by modifing the code of FB 

or becouse it's needed a specific configuration?

 

>I have no idea what you mean with this sentence. I think you misread the 
>sentence "DO NOT set up the postgres database server to also act as 
>ACTIVE DIRECTORY or as a DOMAIN CONTROLLER." in your third link. It 
>means don't run active directory on the same server as postgresql. The 
>same advice applies to firebird or any other database server.

 

ok

 

>If you have no other choice, 
>then at least make sure you are running on a different *physical* disk 
>then the datastore of active directory (although that is still no 
>guarantee for good performance).

 

yes that would be my case , my small customers could not make that effort to 
have 2 servers

we are speaking of lan of 3/5 customers.

 

anyway, using a different physical you know that the performance will be the 
"standard ones" ?

 

phisical disk cold be also a "partition" from the same HDD ?

 

 


 





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

RE: [firebird-support] FB server installation and concurrent users limits on Windows Server

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

No you don’t have to worry about using FB.  I have been using it since it was 
Interbase 25 years ago. And it has improved dramatically since it was 
open-sourced. We migrated one of my customers from IB5.6 to FB 2.5 in July this 
year. IB 5.6 (FB’s predecessor) was running rock solid for 25 years. The main 
reason we migrated was obsolescence of dev tools (Delphi 5) compatible with IB 
5.6. 

 

I have been running factory production systems on FB for the last 15 years. Our 
current ERP (exotic leather traceability system) system also runs on FB. It has 
around 400 tables but is by no means the largest FB database out there. The 
alternatives are Postgress/M$/Oracle of which only Postgress is free I believe.

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 04 November 2014 01:06 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] FB server installation and concurrent users 
limits on Windows Server

 

  

Hi

thank for the suggestion. i am very suprised of this occurrence.

 

i've read some post's and "Fb ticket support" about it 

sound so strange

possible that noone use FB on a domain controller or maybe is not so common to 
have users that reach DB size more then 1 GB or when FB is installed on a 
domain controller 

rightnow i can't imagine how much my application will need but now i am 
"worried" about working with FB

 

so i am starting to think that FB is used only for small projects and/or in 
"low profile network/companies"

 

 



---In firebird-support@yahoogroups.com,  wrote :

There is no Windows imposed limit on the number of users. Even if you run on a 
server. Except of course if you deploy a client app on the server and users 
access it using RDP. Then each user would need a CAL, but otherwise , no 
restrictions.



Do not run FB server on a serer that is a domain controller. There is 
sufficient empirical proof that this is not good…





From: firebird-support@yahoogroups.com 
  
[mailto:firebird-support@yahoogroups.com 
 ] 
Sent: 30 October 2014 06:08 PM
To: firebird-support@yahoogroups.com  
Subject: [firebird-support] FB server installation and concurrent users limits 
on Windows Server





Hi

I know that FB as software doesnt have any limit to the users that can access 
the engine.

I am wondering if is there any “access limit” imposed from a windows system 
server (like the latest 2008/2012)

that will require to buy extra licenses 



I suppose that If I will install FB server on a window “client machine” for a 
small network group I will have no limitation about users that can connet to 
the machine and then to FB engine



What if I will install the engine on a Windows 2012 RX server? 

I already know that the license comes with possibility to validate 5 users 
simultaneously accessing the server and you have to buy extra CAL 

If I will not use a DOMAIN and the windows system control about users accessing 
FB engine would I have no limitation about the number of CLIENTS that will 
access the FB server engine?



Thank you for the reply

Alex







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





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

RE: [firebird-support] Re: Performance of Firebird (Superserver vs Superclassic, etc.)

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Interesting. I have a server using SSD with the same I/O stats, but the server 
does not perform very well. All efforts to optimise have yielded result below 
expectation..

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 03 November 2014 01:02 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Performance of Firebird (Superserver vs 
Superclassic, etc.)

 

  

I also like to share my experience with FB 2.5 on Win 64.

I took a different path to optimazed FB performance.

>From the very beginning I have choosed SC architecture for its multiple thread 
>capability.

And these are the main setting in my db
- Page Buffers = 1024
- DefaultDbCachePages = 0

I set these parameters to its min value because Firebird database is placed on 
a RAM disk. 
I predict my database size won't exceed 64GB and also I can upgrade CPU RAM up 
to 128GB.

With those setting, you have SC architecture with share cached like in SS.

CPU has a UPS and backup runs 3x times a days. It has been runs about 3 years 
without serious problems.

I don't know how its speed compared to database on a disk, but it is very fast. 

Just for raw info, on SSD(sata 2), random read 4K +/- 18 MB/s, random write 4K 
+/- 52 MB/s. 
On RAM, random read 4K +/- 1061 MB/s, random write 4K +/- 770 MB/s.





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

RE: [firebird-support] Re: Equality on NULL column values

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Thanks! Never too old to learn

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 31 October 2014 09:13 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Equality on NULL column values

 

  

31.10.2014 09:03, 'Louis van Alphen' wrote:

> and coalesce(R.FINISH,-1) = coalesce(STOCK.FINISH,-1)

and R.FINISH is not distinct from STOCK.FINISH

Dmitry





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

[firebird-support] Equality on NULL column values

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Hello all, I have a table that contains columns that describe an item. E.g.

 

ITEM table

  ID bigint not null

  COLOUR bigint  not null  (indexed)

  FINISH bigint (indexed)

 

Then another table that contains a qty of items that is reserved. I.e. the 
specification and qty of items reserved. Colour is mandatory and Finish is 
optional

 

RESERVATION table

  ID bigint not null

  COLOUR bigint  not null  (indexed)

  FINISH bigint (indexed)

  QTY integer not null

 

 

Now to determine available stock (item count - reservations) I issue the 
following query:

 

select

  STOCK.COLOUR,

  STOCK.FINISH,

  STOCK. STOCK_QTY,

  R.QTY as RESERVED_QTY,

  STOCK. STOCK_QTY - R.QTY as RESERVED_QTY as AVAILABLE_QTY

  from

   (

/* Get summary of item stock */

   select COLOUR,FINISH,count(*) as STOCK_QTY

  from ITEM

 group by COLOUR,FINISH

) STOCK

 

left outer join RESERVATION R /* Get Reservations */

  on R.COLOUR = STOCK.COLOUR  --  Match mandatory field

  and coalesce(R.FINISH,-1) = coalesce(STOCK.FINISH,-1)  --  Match optional 
field

 

The reason for using the coalesce in the join is to force NULL Finishes to 
match. The problem is that the query plan does not use the indices on FINISH 
because of the coalesce. This results in HUGE number of reads on the 
RESERVATION table. Think it table scans the RESERVATION table for each ITEM 
row. I tried a computed index, but the query did not use that index. The only 
(non FB) way to optimise the query is to actually refactor the tables and 
extract a SPECIFICATION table. In my case this will mean a HUGE amount of work 
and not feasible at this stage.

 

So my question is: How to optimise this query given that I use FB 2.5.3…?

 

Thanks

 

 

 

 

 

 

 

RE: [firebird-support] FB server installation and concurrent users limits on Windows Server

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

There is no Windows imposed limit on the number of users. Even if you run on a 
server. Except of course if you deploy a client app on the server and users 
access it using RDP. Then each user would need a CAL, but otherwise , no 
restrictions.

 

Do not run FB server on a serer that is a domain controller. There is 
sufficient empirical proof that this is not good…

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 30 October 2014 06:08 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] FB server installation and concurrent users limits 
on Windows Server

 

  

Hi

I know that FB as software doesnt have any limit to the users that can access 
the engine.

I am wondering if is there any “access limit” imposed from a windows system 
server (like the latest 2008/2012)

that will require to buy extra licenses  

 

I suppose that If I will install FB server on a window “client machine” for a 
small network group I will have no limitation about users that can connet to 
the machine and then to FB engine

 

What if I will install the engine on a Windows 2012 RX server? 

I already know that the license comes with possibility to validate 5 users 
simultaneously accessing the server and you have to buy extra CAL 

If I will not use a DOMAIN and the windows system control about users accessing 
FB engine would I have no limitation about the number of CLIENTS that will 
access the FB server engine?

 

Thank you for the reply

Alex

 





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

Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

We just deployed a new server RAID10 with 8 SSDs and want to make sure we dont 
run into issues. I have scheduled a gbak every night, but that needs to be to 
copied to other storage

Sent from my iPad

On 28 Sep 2014, at 12:57, "Ivan Arabadzhiev intelru...@yahoo.com 
[firebird-support]"  wrote:

> I`ve been using SSDs for quite some time - had major issues with a Kingston 
> model a while back, so I`ve kept away from them. Most my servers are with 
> Corsair Force 3 drives and doing fine (they are UPS protected, so power 
> outages don`t happen all that often) - haven`t had a corruption during normal 
> operation (meaning no firebird errors at runtime and no improper shutdowns 
> during heavy I/O). I also have a couple of Samsung 830 drives and Intel 330 - 
> yes, they aren`t really the freshest of models but they are proof you can be 
> reasonably safe with SSDs. Even when I do get a corrupted page or two, I 
> haven`t had data loss - a b/r cycle and everything goes back to normal.
> 
> PS Of course, regular backups on a classic HDD are something you should never 
> ever consider skipping :)
> 
> 2014-09-28 13:49 GMT+03:00 fabianoas...@gmail.com [firebird-support] 
> :
>  
> Number of guaranteed writes is much lower on SSD. when FB tries to write some 
> write operations will fail and database will be corrupted.
> Flash disks as pen drives and memory cards also.
> 
> Em 28/09/2014 04:53, "Louis van Alphen lo...@nucleo.co.za [firebird-support]" 
>  escreveu:
> 
>  
> Why will corruption occur?
> 
> Sent from my iPad
> 
> On 27 Sep 2014, at 19:03, "fabianoas...@gmail.com [firebird-support]" 
>  wrote:
> 
>>  
>> Do not change to a SSD! Corruption will occur.
>> 
>> Em 27/09/2014 11:16, "Doychin Bondzhev doyc...@dsoft-bg.com 
>> [firebird-support]"  escreveu:
>>  
>> Hi Costantino,
>> 
>> I did some experimenting before one year and I found that Firebird is 
>> much faster when you use page size = cluster size on the file system.
>> 
>> So if your file system is with 4K cluster I suggest to use page size of 4K.
>> 
>> This is very helpful when you have Forced Write = ON.
>> 
>> Performance gain with insert only scenario is more then 10-15% from 16K 
>> page on Windows 7 with RAID 10.
>> 
>> another thing to look for is to try to minimize the number of 
>> transactions you create.
>> 
>> Try to put as many as possible statements into single transaction. So 
>> for this check do you use autocommit on every statement or you wrap all 
>> statements executed while processing single file in one transaction.
>> 
>> Also when you process your lines in the input file try to group as many 
>> as possible selects into single select.
>> 
>> for example:
>> 
>> select field1, filed2, filed3, field4 from table1 where field1 = ? and 
>> field2 = ?
>> 
>> into :
>> 
>> select field1, filed2, filed3, field4 from table1 where (field1 = ? and 
>> field2 = ?) or (field1 = ? and field2 = ?) or (field1 = ? and field2 = 
>> ?) ..
>> 
>> this way you will check for multiple values at once and that means less 
>> selects to execute on the database.
>> 
>> If you do your query on single field then you can use IN instead of =
>> 
>> Check also you have proper index setup on the tables.
>> 
>> Usually execution that is IO heavy does not get much better performance 
>> by just changing the hardware. If you move from HDD to SSD this can 
>> speed up much more but HDD performance is not very different in the last 
>> 10 years.
>> 
>> Also another thing to note is that for DB scenarios I prefer to use Read 
>> Caching and no Write caching. This gives me better guarantee that I will 
>> not end with broken database in case of power failure.
>> 
>> Have a nice day.
>> 
>> -- 
>> Doychin Bondzhev
>> dSoft-Bulgaria Ltd.
>> PowerPro - billing & provisioning solution for Service providers
>> PowerStor - Warehouse & POS
>> http://www.dsoft-bg.com/
>> Mobile: +359888243116
>> 
>> [Non-text portions of this message have been removed]
>> 
> 
> 
> 

Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2

[Louis van Alphen lo...@nucleo.co.za [firebird-support]]

Why will corruption occur?

Sent from my iPad

On 27 Sep 2014, at 19:03, "fabianoas...@gmail.com [firebird-support]" 
 wrote:

> Do not change to a SSD! Corruption will occur.
> 
> Em 27/09/2014 11:16, "Doychin Bondzhev doyc...@dsoft-bg.com 
> [firebird-support]"  escreveu:
>  
> Hi Costantino,
> 
> I did some experimenting before one year and I found that Firebird is 
> much faster when you use page size = cluster size on the file system.
> 
> So if your file system is with 4K cluster I suggest to use page size of 4K.
> 
> This is very helpful when you have Forced Write = ON.
> 
> Performance gain with insert only scenario is more then 10-15% from 16K 
> page on Windows 7 with RAID 10.
> 
> another thing to look for is to try to minimize the number of 
> transactions you create.
> 
> Try to put as many as possible statements into single transaction. So 
> for this check do you use autocommit on every statement or you wrap all 
> statements executed while processing single file in one transaction.
> 
> Also when you process your lines in the input file try to group as many 
> as possible selects into single select.
> 
> for example:
> 
> select field1, filed2, filed3, field4 from table1 where field1 = ? and 
> field2 = ?
> 
> into :
> 
> select field1, filed2, filed3, field4 from table1 where (field1 = ? and 
> field2 = ?) or (field1 = ? and field2 = ?) or (field1 = ? and field2 = 
> ?) ..
> 
> this way you will check for multiple values at once and that means less 
> selects to execute on the database.
> 
> If you do your query on single field then you can use IN instead of =
> 
> Check also you have proper index setup on the tables.
> 
> Usually execution that is IO heavy does not get much better performance 
> by just changing the hardware. If you move from HDD to SSD this can 
> speed up much more but HDD performance is not very different in the last 
> 10 years.
> 
> Also another thing to note is that for DB scenarios I prefer to use Read 
> Caching and no Write caching. This gives me better guarantee that I will 
> not end with broken database in case of power failure.
> 
> Have a nice day.
> 
> -- 
> Doychin Bondzhev
> dSoft-Bulgaria Ltd.
> PowerPro - billing & provisioning solution for Service providers
> PowerStor - Warehouse & POS
> http://www.dsoft-bg.com/
> Mobile: +359888243116
> 
> [Non-text portions of this message have been removed]
> 
> 

RE: [firebird-support] Re: SubQuery Help

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

select

 a.Project_PK,

PC.b_cost AS Sum_of_Cost,

PC.b_estimate_cost AS Sum_of_Estimated_Cost,

PQ.c_qty AS Sum_of_Quantity,

PQ.c_estimate_quantity AS Sum_of_Estimated_Quantity

 

FROM Projects a

 

LEFT JOIN 

(

  select 

  b.project_pk,

  sum(b.cost) as b_cost,

  sum(b.estimate_cost) as b_estimate_cost

  from cost b

  group by b.project_pk,

) ProjectCost PC ON PC. project_pk = a.project_pk

 

LEFT JOIN 

(

  select 

  c.project_pk,

  sum(c.qty) as c_qty,

  sum(c.estimate_qty) as c_estimate_qty

  from Quantity c

  group by c.project_pk,

) ProjectQty PQ ON PQ.project_pk = a.project_pk

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 16 September 2014 08:45 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: SubQuery Help

 

  

>Select
>  a.Project_PK

>, SUM(b.cost) AS Sum_of_Cost

>, SUM(b.estimate_cost) AS Sum_of_Estimated_Cost

>, SUM(c.quantity) AS Sum_of_Quantity

>, SUM(c.estimate_quantity) AS Sum_of_Estimated_Quantity

>FROM Projects a

>LEFT JOIN Cost b ON a.project_pk = b.project_pk

>LEFT JOIN Quantity c ON a.project_pk = c.project_pk

>GROUP BY a.Project_PK

 

Unfortunately, this gives the wrong result if there are multiple costs or 
quantities for any project. Assume the following (all for the same project)

 

COST

1

2

 

Quantity

1

2

3

 

The sums that ought to be given is 3 and 6, whereas I'm pretty certain the 
above statement would yield 9 and 12.

 

Rather than this solution, you should either use one or two CTEs or a corrected 
version of the original solution. 

 

So either something like

 

with SumCost(Project_PK, Sum_of_Cost, Sum_of_Estimated_Cost) as

(select p.Project_PK, sum(c.cost), sum(c.estimate_cost)

 from Projects p

 join Costs c on p.project_pk=c.project_pk

 group by 1)

 

select p.Project_PK, c.Sum_of_Cost, c.Sum_of_Estimated_Cost, 

 SUM(q.quantity) AS Sum_of_Quantity, 

 SUM(q.estimate_quantity) AS Sum_of_Estimated_Quantity

from Projects p

left join SumCost c on p.project_pk=c.project_pk

left join Quantity q on p.project_pk=q.project_pk

group by 1, 2, 3

 

or

 

Select
  p.Project_PK,
  (select sum(c.cost) from Costs c where p.project_pk=c.project_pk) as 
Sum_of_Cost,
  (select sum(c.estimate_cost)from Costs c where p.project_pk=c.project_pk) as 
Sum_of_Estimated_Cost,
  (select sum(q.quantity) from Quanties q where p.project_pk=q.project_pk) as 
Sum_of_Quantity,
  (select sum(q.estimate_Quantity) from Quantity q where 
p.project_pk=q.project_pk) as Sum_of_Estimated_Quantity
from Projects p

 

Looking at the alternatives, I actually think the latter (i.e. the original and 
not the CTE), seems to be the simplest solution.

 

HTH,

Set





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

[firebird-support] Issue with performance of 2.5.2 on server 2012

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I have a production system that is performing badly on server 2012. I used
the regression test app that basically executes all the functions in
sequence to run on my dev PC and timed how long it took. Then ran it on
server 2012 and measured the duration.


 


Initially my PC performed better by 4 times than the server, but after much
tweaking the host server is performing much better.


The total time taken by my PC is 450 sec to complete. The server is
currently on 650 sec.


 


My PC runs FB2.5.2 superclassic


Spec is i7 3GHz 16GB with SATA6 drive


 


The host server is dual Xeon with 128GB RAM and 8x250GB SSD volume spanned
into one drive by SMC2108 RAID controller in RAID 10 config. Currently no
battery backup (BBU) on RAID controller


The idea is to run on virtual server but we are having performance issues so
we testing on host first.


 


The tweaks we did was to:


-  optimise the firebird.config. 


-  Enable write cacheing on the RAID controller (even though we
should not due to no BBU)


-  Disabled firewall


 


The server's hardware performance is very fast. >800MB/sec on Crystal Disk
Mark. But with FB, the server is much slower than I expected. Anyone having
same issues on W2012/ FB2.52?


 


Louis