[firebird-support] ORDER BY too slow
I'm building a major product using Lazarus 1.8.4, FPC 3.0.4, IBX 2.3.3 and Firebird RDBMS 3x. I have a query that runs in the range of 1 to 3 milliseconds without the ORDER BY clause. But once I add the ORDER BY clause, the query takes > 1.5 seconds. Be advised that there is an index on the MSGS.PRTY field and an index on the MSG_PSTS.MSTB field. The PLAN refuses to use either index. Here is the query SQL: [code=pascal] commit; SELECT FIRST 10 SKIP 0 msg.OBJ_GUID AS "MSG_GUID", msg.PRTY, msg.TTL, pst.OBJ_GUID AS "PST_GUID", pst.MSTB_DTS FROM MSGS msg JOIN MSG_PSTS pst ON msg.OBJ_GUID = pst.MSG_GUID JOIN MSG_USRS meu ON msg.OBJ_GUID = meu.MSG_GUID JOIN USRS usr ON msg.USR_GUID = usr.OBJ_GUID WHERE meu.USR_GUID = '12A61B0FAE3046B6AEDEEDF6B4FE0E78' ORDER BY msg.PRTY, pst.MSTB [/code] I almost wish I could create an index on multiple tables somehow. How can I speed this up? Here is the PLAN: [code=pascal] Select Expression -> First N Records -> Skip N Records -> Sort (record length: 444, key length: 20) -> Nested Loop Join (inner) -> Filter -> Table "MSG_USRS" as "MEU" Access By ID -> Bitmap -> Index "FK_MSG_USRS_1" Range Scan (full match) -> Filter -> Table "MSGS" as "MSG" Access By ID -> Bitmap -> Index "PK_MSGS" Unique Scan -> Filter -> Table "USRS" as "USR" Access By ID -> Bitmap -> Index "PK_USRS" Unique Scan -> Filter -> Table "MSG_PSTS" as "PST" Access By ID -> Bitmap -> Index "FK_MSG_PSTS_0" Range Scan (full match) [/code] The "Sort" appearing here is the problem. I need it to go away and get Firebird to use the indexes provided. Thanks for any help you can provide.
[firebird-support] New Firebird Admin tool called FireBolt. Replaces FlameRobin on Windows Server 2016
I'm a long time FlameRobin user and Firebird lover. After a server upgrade, I found that FlameRobin doesn't run on Windows Server 2016. Here's the cure: New Firebird admin tool called FireBolt. Replaces FlameRobin. Get your copy here: https://www.jetlyt.ca/ Learn more here: https://www.amazon.ca/dp/B07D42WGH9.
[firebird-support] Struggling with RDB$DEPENDENCIES table
I'm building a Firebird DB Admin tool, like FlameRobin (bc FlameRobin doesn't work on Windows Server 2016 Essentials) I'm 80% done and have everything working except I'm struggling with something that shd be quite simple. I've searched the Internet and found nothing to help me except the Firebird System Tables help that details the structure of the RDB$DEPENDENCIES table, which shd be super helpful, but actually isn't. I'm trying to recreate the " Depends On" and "Objects that Depend on " grids from Flame Robin. I want to present the user with a simpler TDBGrid, showing just two columns, Column 1) the object type (trigger, procedure, table, etc.) and Column 2) the object name. The user can then click on the row and see the associated fields involved with the dependency in a TMemo. I can't even get started because I'm unable to tease out the relationships between RDB$DEPENDENCIES.DEPENDENT_NAME and RDB$DEPENDENCIES.DEPENDED_ON_NAME ... probably bc these columns "could" reference several different tables and you have to go through more than one JOIN to get the info you want. (Hint, compare what you see in FlameRobin with what you see in the RDB$DEPENDENCIES table. Not the same. I suspect I need 4 SQL statements.. (and I need help with each) SQL 1) List of things (object type and name) that my selected object depends ON SQL 2) The fields associated with the selected row in SQL 1 above SQL 3) List of things (object type and name) that my depend on my selected object SQL 4) The fields associated with the selected row in SQL 3 above Any help will be greatly appreciated. Target Platform is both FB SQL Dia 2 and 3
[firebird-support] In Firebird 3x, can I log in as a different user within same session? How?
Background: I'm trying to use ScriptCase to build a PHP front end for an existing Firebird 2.5 DB project. Mission critical change tracking in this project is years old, is self contained, and relies on CURRENT_USER. Precursor to Problem: In ScriptCase, the entire project logs in as SYSDBA (or any "high level" user I choose). (ScriptCase has it's own user control system and change logging which I don't need and don't want) Problem: Because ScriptCase logs in as a high level user, all existing change tracking built into the Firebird DB will log all changes by all ScriptCase users as SYSDBA (or what ever high level user was used to log in) Possible Solution: If Firebird 3x has an SQL command that would allow a currently connected user to switch their login to a different user, then there is a spot in the ScriptCase login process that I could issue that SQL. Of course, I would supply the correct Login ID and password, and I would expect all security restrictions on the session to be adjusted to the new login ID. Question: Does FB3x have such an SQL command that allows a currently logged in user to log in as a different user? Many months ago I read somewhere that this is possible, but now I can't find any documentation on how to do it. Thanks in advance for any help you can provide.
[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?
[firebird-support] Maximum Number of Connections 2.5.3
There seems to be contradictory information about maximum number of simultaneous distinct user connections to Firebird. This document: How many users can connect to Firebird simultaneously? http://www.firebirdfaq.org/faq292/ says there is not limit, except for the TCP/IP in the OS. How many users can connect to Firebird simultaneously? http://www.firebirdfaq.org/faq292/ Collection of answers to questions about Firebird maximum, number, users, connections. View on www.firebirdfaq.org http://www.firebirdfaq.org/faq292/ Preview by Yahoo However, on the download page of the Firebird home, it clearly lists the max number as fixed at : 2048 Firebird: The true open source database for Windows, Linux, Mac OS X and more http://www.firebirdsql.org/en/firebird-2-5-release-description/ http://www.firebirdsql.org/en/firebird-2-5-release-description/ Firebird: The true open source database for Windows, Lin... http://www.firebirdsql.org/en/firebird-2-5-release-description/ Firebird SQL: The true open-source relational database View on www.firebirdsql.org http://www.firebirdsql.org/en/firebird-2-5-release-description/ Preview by Yahoo My app is stateful, must remain open for up to eight hours, and each user is distinct, so connection pooling is probably not the answer. (Each individual user's connection must be kept separate from each other). I need to serve 7,000 users. Should I forget about using Firebird for this app, and move to MS SQL Server?
[firebird-support] Firebird 2.5x on Windows 2012 with BitLocker Encrypted HDD, RAID 1
Does anyone do this? Are there any problems?
[firebird-support] SQL puzzle: Order based on Prerequisite
In a table called Steps, I have the following fields: ID, B4Me, Dsc, -ID field contains a unique ID for each record -B4Me contains the ID of some other record in the table that MUST appear in a result set, BEFORE this record. B4Me may be null. This is called the B4Me order. -Records will be entered in random order, so Natural order cannot be relied upon as a substitute for B4Me I need an SQL statement that will return all records with a user supplied search word (such as oil, or glycol or micron) in the Dsc field, which could return a variable number of records, but what ever the number of records returned, the *order* must be such that each record is preceded by the record who's ID matches the B4Me value of a given record. Important Notes: - The SQL may return one or more records with a blank B4Me. In which case, Natural order can be used for those records, and they are all considered First or rather, Before everything else. Once all the records with a blank B4Me are listed, the remaining must be in the B4Me order. - It is possible, though unlikely, that following the B4Me order will result in a circular reference. (Two or more records that reference each other, or the last record in a large result set, references the first (or any other in the result set) record in the B4Me field). In these cases, the SQL must either list all records until a circular reference is detected, then don't list the offending record, or display an error message, but it must not hang FB and get an out of memory error. Thanks in advance for any help you can provide.
[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.
