RE: [firebird-support] Scaling Firebird - Azure

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

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

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

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

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

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

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

RE: [firebird-support] Sequential auto incremental numbering

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

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

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

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

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]

RE: [firebird-support] QUERY QUESTION

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

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

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 r

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

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

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

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

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

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

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

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

[firebird-support] Gfix returns immediately

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

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

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

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

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 l

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

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

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

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

[firebird-support] Queries & reads with CTE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

-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] firebird-support

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

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

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

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

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

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

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

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

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

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

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

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] firebird-support@yahoogroups.com wrote: So, I now used two small SPs with row_count and joined the

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

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

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

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.

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

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

RE: [firebird-support] Re: Speed issues

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

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

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:

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

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

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

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:

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

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

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

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

[firebird-support] Equality on NULL column values

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.

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

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'

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

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

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

Why will corruption occur? Sent from my iPad On 27 Sep 2014, at 19:03, fabianoas...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Do not change to a SSD! Corruption will occur. Em 27/09/2014 11:16, Doychin Bondzhev doyc...@dsoft-bg.com [firebird-support]

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

-support] firebird-support@yahoogroups.com: 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

RE: [firebird-support] Re: SubQuery Help

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

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

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