Den 24.01.2016 09:04, skrev Ertan Küçükoğlu [email protected] [firebird-support]: > Hello, > > I needed some time to understand (at least try to understand) your post. > Please, note that my SQL knowledge is very limited. I really don’t know > internals of database any system. Though, I need to develop an application > which can use Firebird, Interbase, MSSQL, MySQL, Oracle. Some of users > databases may be Firebird, others MSSQL, and an Oracle maybe. User decides > what to use. Hi again, Ertan!
I've never written a query that had to use different database backends, but one thing I'd then have been considering (and which I sometimes do in Firebird), is to put the queries in a table in the database and just have a simple query that loads the queries from the database into other objects hardcoded. That way, you can have pretty standard SQL in general, and modify it for databases needing special treatment. Moreover, I'd definitely have different indexes with different databases. As you say, Oracle have no problem only using your PK, whereas Firebird has big problems and other databases probably varies. > Saying that; > - I wonder if it would be possible to make a view and decrease one SELECT > depth in the SQL? This will make statement a little easier to read for sure. > Or, that will be a problem in the long run? Some say views are not good. They > are generated completely for all the data in TABLE not considering WHERE > clauses. Not saved as TABLE data and generated at each run in a temp disk > space/memory. I rarely use views and am no expert on them, but I think that Firebird - if possible - mixes it with the statement where you use them and that there are no big difference between having it all in the select and have some of the select in a view. But as I said, I'm no expert in this field and wouldn't be surprised if what I wrote in my previous sentence only applies to plain selects with inner joins and that things are very different once LEFT [OUTER] JOIN or GROUP BY comes into play. > - Having some indexes added query now performs very nicely. Runs & fetches > all records below one second. Cold run around 0.8 second, 2nd run right after > cold is around 0.4 seconds. Good to hear! > - I read and re-read your comments on having separate indexes is better for > SQL. However, I do need that “combined” primary index for my application, for > sure. I can understand that different databases (which often can only use one index for each table in a select) needs combined and even unique indexes, but I do not understand why they need the primary key to be made up of these fields. 98% of the time, I simply use integer fields with no meaning for primary keys (the remaining 2% are typically lookup tables (containing only a handful of fields) that are unlikely to change and not used as foreign keys in a way that makes it difficult to change them. The reason I'm on only 98% and not 100% is simply because I'm lazy). Excepting that it makes it harder to read the plan and find problems in your statement, there's nothing wrong with combined indexes in Firebird, I'm only opposed to meaningful primary keys. > Though, I wonder if it will be of any help to define separate indexes for > each field as you suggested? I don't know if that will be lots of indexes > defined in the end. Especially considering other tables I need to define as > application is still being developed and new TABLES are added. I could not > understand what maybe the problem if a/some field(s) length in "combined" > index is increased. You have a combined primary key for BELGENO, BARKOD, ADRESKODU and ISLEMTURU, whereas the WHERE part of your query only refers to BELGENO, ADRESKODU and ISLEMTURU. The way Firebird works means that it then can only utilize the BELGENO part of your primary key for this particular query and that BARKOD, ADRESKODU and ISLEMTURU parts of the key isn't used. There's no need for a separate index for BELGENO since it is the first field of your primary key, but if you have separate indexes for ADRESTURU and ISLEMTURU (and these indexes are reasonably selective), then Firebird can use these indexes. Though it don't know how it would affect the performance of your particular query (if 1% of the records with BelgeNo='REYSAS' have AdresKodu='SAYIM', then it could improve performance, if it is 80% then it would not). > - Finally, I needed to test SQL statement that you re-write in other SQL > databases running in Windows 10 x64bit In order to see if it is executing OK. > All databases I use are Free to use versions. I don't do any test on > Interbase since I don't have it installed on my development computer and > assuming it will be very much same with Firebird. My observations: > Firebird: No problem. > MSSQL: No problem. > MySQL: ERROR near UBAZ (at very beginning) > Oracle: Error at Line: 7 Column: 17, ORA-00907: missing right parenthesis > (line 7: SUM(IIF(TekParca='H', Miktar, 0)) AS Miktar) I almost exclusively use Firebird, but searching the internet indicates that MySQL doesn't support CTEs (WITH...). To change the statement into something that Oracle will accept, you can try changing IIF to CASE (Firebird and probably MSSQL also supports CASE), i.e. SUM(CASE WHEN TekParca='H' THEN Miktar ELSE 0 END)) > - One last thing to tell as an information. Oracle was able to run my first > posted statement with only Primary Keys and no additional indexes defined > less than one second. This is nothing to be said for comparing Firebird and > Oracle. It is just I cannot understand how Oracle handles things. Moreover, > it is less than a month that I ever installed and used Oracle as a database. > I always preferred Firebird as my first line of database. Oracle is a good database with vastly more resources than Firebird. Comparing Firebird to Oracle is similar to use a weight to compare a pea to a coconut. Unfortunately I've never written queries against Oracle, nor used PL_SQL, but at the same time I'm happy that I've never had to be an Oracle DBA. Set
