Den 24.01.2016 09:04, skrev Ertan Küçükoğlu ertan.kucukoglu@...
[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