Re: [firebird-support] Optimizing this select Query
To the OP: If you change the query to use fixed values instead of parameters does it change anything ? My experience says yes... ;-) -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Optimizing this select Query
Hm, this is just getting rid of using the index on sku - depending on it's selectivity, this might be a way, but than, the index might be of no use... SALEITEMS.SKU+0 = :vSKU AND You might check combined indexes... It's avoiding to use the index in this particular query, it does not mean it has no use... In a diferent query it could be a good index. And I think this index is generated by a FK constraint, and could not be removed. How he can combine the index of two distinct tables ? I don't think that's possible (I don't think you can reference other tables when using COMPUTED BY, though I've never tried), but in theory he could combine the indexes of SALEDATE and SALETYPE (which can be good for getting the last few percents of performance in critical areas, but at the cost of making it more difficult to understand and optimize the plans using this index (so I rarely use combined indexes myself)). To the OP: If you change the query to use fixed values instead of parameters does it change anything ? I cant remember if the FB optimizer take in count the range of the between, I don't think so, but you could give it a try. It would have mattered if he had been using LIKE, using BETWEEN it shouldn't matter for Firebird versions = 2.5 (It will probably matter for Firebird 3 since I think that version will include histograms). Set
[firebird-support] Install FirebirdSql on Windows 2008R2
It seems that I read here recently that its better not to install FirebirdSql in the default Program Files on Windows server 2008R2. I'm getting ready to deploy my app to the web and wanted some advice regarding this. Thanks Dixon
Re: [firebird-support] Install FirebirdSql on Windows 2008R2
On 11 Apr 2014 07:29:54 -0700, dixonepper...@yahoo.com wrote: It seems that I read here recently that its better not to install FirebirdSql in the default Program Files on Windows server 2008R2. I'm getting ready to deploy my app to the web and wanted some advice regarding this. Where did you read that and why do you think that? Installing into Program Files is the default and it works *if* Firebird is run as a user with sufficient rights (which is the default when installed as a Windows Service). Mark
Re: [firebird-support] Install FirebirdSql on Windows 2008R2
I think it had to do with permissions. It was a passing conversation and I don't remember exactly what the issue was. But seems like they were getting blocked on some of the activities because Windows was restricting it to Admin rights.
[firebird-support] 'PDOException' Operating system directive OpenProcess failed
Hello, I would greatly appreciate if I could lend a hand: My server is IIS 7.5 on Windows (64 bit) Eventually I get this message from PHP: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [335544373] operating system directive OpenProcess failed'' Pressing F5 or navigating to another page (entire site using PHP 5.4.x takes php_pdo_firebird.dll connection) until the error disappears reappears for no apparent reason. The database and directory exist and all users have full permissions on the directory. Is there any known incompatibility with Windows 64-bit systems? How I can fix this error? Thank you. A greeting.
Re: [firebird-support] procedure that calls a procedure
Em 11/4/2014 13:59, artmcc...@yahoo.com escreveu: I have a procedure that calls several other procedures. I have changed one of the embedded procedures and can run that independently and get the results I expect, I was able to compile it and it works.] My question is do I need to compile the parent procedure again, or should it just pick up the new version of the prod. And if I am calling this with an app that is connected, do I need to stop that app and restart it, or will that use the new version? Art as far as I recall, the other conections will still use the old version, you need to disconnect and reconnect so it can see the changes see you !
[firebird-support] Transactions
Please help my understand what will happen in the following cases involving transactions. System: Delphi XE2, Firebird 2,5x, Using DBX components. Isolation level: ReadCommitted. The actual statements are much more complex and involves various statements in the transaction and/or triggers that updates/insert into up to 8 tables. Case 1 App 1 starts a transaction (T1) and in an After Insert trigger for MasterTable do something like: Update TableSub set X = X + MasterTable.X where Y=Y.MasterTable Before App1 commits, App2 starts a transtaction (T2) and does the same statement. Then App1 commits(T1) and then App2 commits(T2). Will this result in a deadlock or will T1 run and then T2 or will X.old in both transaction have the same value? Case 2 Same case as above but the trigger use X = GEN_ID(Generator1,1). Will X be, for example 10, in T1 and 11 in T2, or will it result in a deadlock, or will X be 10 in both transactions? Any information will be appreciated and even more so hints as on how to handle this. I would like T1 to start and run to completion and then T2 taking into account what happen in T1. Kind Regards Cornie van Schoor InfoStar Software South Africa
RE: [firebird-support] Optimizing this select Query
Marius, Is it possible to optimize the following select query? I make use of Firebird 2.5.2.26540 in Super Server mode. The query looks like this: SELECT Sum(SALEITEMS.QUANTITY), Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX) FROM SALES SALES INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE = SALEITEMS.LINECODE WHERE SALES.POSTSTATUS = 'Posted' AND SALES.SALE_DATE BETWEEN :vStartDate AND :vEndDate AND SALEITEMS.SKU = :vSKU AND SALES.CASHCREDIT = 'Cash' The plan being utilized is as follow: PLAN JOIN (SALEITEMS INDEX (I_SALEITEMS_SKU), SALES INDEX (I_SALES_ULINECODE)) I would suspect that the current indexes are too broad for the system to be able to narrow the criteria. What is the selectivity/uniqueness of the POSTSTATUS, SALE_DATE and CASHCREDIT fields/columns? Personally, I would be inclined to create a compound index on these three fields in the Sales table, where the order to the fields/columns in the index definition would go from least unique to most specific (ie. POSTSTATUS + CASHCREDIT + SALE_DATE or CASHCREDIT + POSTSTATUS + SALE_DATE). That type of index would allow for the SALES entries to be narrowed quickly, leaving the SKU evaluation as a secondary/notional evaluation. Sean P.S. You might also want to create an compound index on SALESITEMS on SKU + LINE Code (in that order, from least to most unqiue). Depending on the number of Line Code entries per Sales that index could almost be considered a unique relationship for each SALES entry.
