union and order by

[Bart Smissaert]

Firebird 1.5 classic on Windows.
Is it possible to have a SQL with union all and an order by that
includes a field that is not in the selects?

eg:

select fieldA, fieldB from table1 where fieldA starting with 'bd'
union all
select fieldA, fieldB from table1 where fieldA starting with 'bi'
order by
fieldA asc, fieldC asc

In case you wondered why I used a union here, it is as I found it be a
lot faster than using an OR.


RBS

[Svein Erling Tysvær]

Post by Bart Smissaert
Firebird 1.5 classic on Windows.
Is it possible to have a SQL with union all and an order by that includes a field that is not in the selects?

I'd be very surprised if this was possible, logically you sort a result set and the result set that does not contain your field. If it wasn't for the union, it is at least thinkable (for people like me without knowledge of Firebird internals) that the sorting could be done during the selection of records and that it could sort on the field. Though it should be simple for you to test to find out whether it is possible or not.

Post by Bart Smissaert
In case you wondered why I used a union here, it is as I found it be a lot faster than using an OR.

That must either be coincidental or because the PLAN changes. My guess is that your selectivity indicates that using an index is marginally better than using NATURAL, but that this changes once you introduce OR (it isn't generally quicker to use UNION than OR). Since Firebird doesn't have histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or 'bi'), this may be a wrong choice.

If my guess is correct, then the following might make your query quick again:

select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc

HTH,
Set

[Bart Smissaert]

select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc

Thanks, will try that out and report back.
I started to use the union instead of the OR when we
were still on Interbase and the difference in speed then was enormous. We then
moved to Firebird and the difference was still there.
Note that we are still on Firebird 1.5 if that makes a difference.

RBS

Post by Svein Erling Tysvær

Post by Bart Smissaert
Firebird 1.5 classic on Windows.
Is it possible to have a SQL with union all and an order by that includes a
field that is not in the selects?

I'd be very surprised if this was possible, logically you sort a result set
and the result set that does not contain your field. If it wasn't for the
union, it is at least thinkable (for people like me without knowledge of
Firebird internals) that the sorting could be done during the selection of
records and that it could sort on the field. Though it should be simple for
you to test to find out whether it is possible or not.

Post by Bart Smissaert
In case you wondered why I used a union here, it is as I found it be a lot
faster than using an OR.

That must either be coincidental or because the PLAN changes. My guess is
that your selectivity indicates that using an index is marginally better
than using NATURAL, but that this changes once you introduce OR (it isn't
generally quicker to use UNION than OR). Since Firebird doesn't have
histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or
'bi'), this may be a wrong choice.
select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc
HTH,
Set

[Thomas Steinmaurer]

Post by Bart Smissaert
select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc
Thanks, will try that out and report back.
I started to use the union instead of the OR when we
were still on Interbase and the difference in speed then was enormous. We then
moved to Firebird and the difference was still there.
Note that we are still on Firebird 1.5 if that makes a difference.

Could be, because the V2 series offers a lot of improvements in the
optimizer area.

If possible, I would seriously try that with Firebird 2.5.
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Post by Bart Smissaert
RBS

Post by Svein Erling Tysvær

Post by Bart Smissaert
Firebird 1.5 classic on Windows.
Is it possible to have a SQL with union all and an order by that includes a
field that is not in the selects?

I'd be very surprised if this was possible, logically you sort a result set
and the result set that does not contain your field. If it wasn't for the
union, it is at least thinkable (for people like me without knowledge of
Firebird internals) that the sorting could be done during the selection of
records and that it could sort on the field. Though it should be simple for
you to test to find out whether it is possible or not.

Post by Bart Smissaert
In case you wondered why I used a union here, it is as I found it be a lot
faster than using an OR.

That must either be coincidental or because the PLAN changes. My guess is
that your selectivity indicates that using an index is marginally better
than using NATURAL, but that this changes once you introduce OR (it isn't
generally quicker to use UNION than OR). Since Firebird doesn't have
histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or
'bi'), this may be a wrong choice.
select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc
HTH,
Set

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

[Thomas Steinmaurer]

Post by Thomas Steinmaurer

Post by Bart Smissaert
select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc
Thanks, will try that out and report back.
I started to use the union instead of the OR when we
were still on Interbase and the difference in speed then was enormous. We then
moved to Firebird and the difference was still there.
Note that we are still on Firebird 1.5 if that makes a difference.

Could be, because the V2 series offers a lot of improvements in the
optimizer area.
If possible, I would seriously try that with Firebird 2.5.

... needless to say, after migrating the database with a backup/restore
cycle to pump the ODS to the newest version.

[Svein Erling Tysvær]

Have tested and the difference is still enormous, some factor 10.
As predicted the added between is slightly quicker than the union construction and thanks for that tip. This is in
fact quite useful for me as currently I have to convert a OR SQL in code (VB) to the UNION and as you can imagine
that can be quite tricky, particularly if there is an order by. So, I will change this code now to add the between
instead and that will be a lot simpler.

It would be interesting to see the PLANs with the OR and UNION (and some information about the chosen indexes). I simply cannot remember anything like this with Fb 1.5.

Set

[Bart Smissaert]

Well, it is simple, with the union and with the added between it will
use the index on that field, but with the simple or construction it
won't.
Looks like an omission in the planning code.

RBS

Post by Svein Erling Tysvær

Have tested and the difference is still enormous, some factor 10.
As predicted the added between is slightly quicker than the union
construction and thanks for that tip. This is in
fact quite useful for me as currently I have to convert a OR SQL in code
(VB) to the UNION and as you can imagine
that can be quite tricky, particularly if there is an order by. So, I will
change this code now to add the between
instead and that will be a lot simpler.

It would be interesting to see the PLANs with the OR and UNION (and some
information about the chosen indexes). I simply cannot remember anything
like this with Fb 1.5.
Set

[Bart Smissaert]

It looks simply replacing the like 'bd%' with starting with 'bd' will
make it use the index as well. Will be simpler to do that in code than
adding the between.

RBS

Post by Bart Smissaert
Well, it is simple, with the union and with the added between it will
use the index on that field, but with the simple or construction it
won't.
Looks like an omission in the planning code.
RBS

Post by Svein Erling Tysvær

Have tested and the difference is still enormous, some factor 10.
As predicted the added between is slightly quicker than the union
construction and thanks for that tip. This is in
fact quite useful for me as currently I have to convert a OR SQL in code
(VB) to the UNION and as you can imagine
that can be quite tricky, particularly if there is an order by. So, I will
change this code now to add the between
instead and that will be a lot simpler.

It would be interesting to see the PLANs with the OR and UNION (and some
information about the chosen indexes). I simply cannot remember anything
like this with Fb 1.5.
Set

[Svein Erling Tysvær]

It looks simply replacing the like 'bd%' with starting with 'bd' will make it use the index as well. Will be
simpler to do that in code than adding the between.

Does your statement actually use 'bd%' or a parameter? LIKE :MyParameter can never use an index since it doesn't know if the parameter starts with a constant or a wildcard. STARTING WITH on the other hand, can use an index. I'm uncertain whether old Firebird versions can use an index for LIKE <constant>, I would expect 1.5 to be able to use it, but it might be an optimization that came later (I used Fb 1.5 for many years without noticing such a problem, but I hardly ever used LIKE, and probably never when I hoped for an index to be used on that field).

HTH,
Set

[Bart Smissaert]

Post by Svein Erling Tysvær
Does your statement actually use 'bd%' or a parameter?

It uses 'bd%', so no parameters. Using the starting with will make it all
simple
as I can do that at source, so no need to programmatically alter a SQL.

RBS



On Mon, Jul 16, 2012 at 11:17 AM, Svein Erling Tysvær <

Post by Svein Erling Tysvær
**

Post by Bart Smissaert
It looks simply replacing the like 'bd%' with starting with 'bd' will

make it use the index as well. Will be

Post by Bart Smissaert
simpler to do that in code than adding the between.

Does your statement actually use 'bd%' or a parameter? LIKE :MyParameter
can never use an index since it doesn't know if the parameter starts with a
constant or a wildcard. STARTING WITH on the other hand, can use an index.
I'm uncertain whether old Firebird versions can use an index for LIKE
<constant>, I would expect 1.5 to be able to use it, but it might be an
optimization that came later (I used Fb 1.5 for many years without noticing
such a problem, but I hardly ever used LIKE, and probably never when I
hoped for an index to be used on that field).
HTH,
Set

[Non-text portions of this message have been removed]



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[Bart Smissaert]

Have tested and the difference is still enormous, some factor 10.
As predicted the added between is slightly quicker than the union
construction and
thanks for that tip. This is in fact quite useful for me as currently
I have to convert
a OR SQL in code (VB) to the UNION and as you can imagine that can be quite
tricky, particularly if there is an order by. So, I will change this
code now to add
the between instead and that will be a lot simpler.

RBS

Post by Svein Erling Tysvær

Post by Bart Smissaert
Firebird 1.5 classic on Windows.
Is it possible to have a SQL with union all and an order by that includes a
field that is not in the selects?

I'd be very surprised if this was possible, logically you sort a result set
and the result set that does not contain your field. If it wasn't for the
union, it is at least thinkable (for people like me without knowledge of
Firebird internals) that the sorting could be done during the selection of
records and that it could sort on the field. Though it should be simple for
you to test to find out whether it is possible or not.

Post by Bart Smissaert
In case you wondered why I used a union here, it is as I found it be a lot
faster than using an OR.

That must either be coincidental or because the PLAN changes. My guess is
that your selectivity indicates that using an index is marginally better
than using NATURAL, but that this changes once you introduce OR (it isn't
generally quicker to use UNION than OR). Since Firebird doesn't have
histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or
'bi'), this may be a wrong choice.
select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc
HTH,
Set