| Subject | [firebird-support] Re: Block size exceeds implementation restriction |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2011-10-27T06:20:09Z |
I observed something similar many years ago, though I don't remember the exact error message. I think I solved it by simply using shorter aliases and removing redundant spaces and words
(e.g. I would change from
from
mv$pdv1 pedidovend0_
left outer join
crt1 carteira1_
on pedidovend0_.numcrt1=carteira1_.numcrt1
to
from mv$pdv1 p0
left join crt1 c1
on p0.numcrt1=c1.numcrt1
)
The reason for my problem in the first place was me trying to flatten a result set in Firebird rather than some statistical package (16 left joins or so since there could be up to 16 rows per person, if each of these 16 rows could have up to four records in another table, then you end up with 64 potential fields for each 'real' field - needless to say, the vast majority of the result fields would be <null>). I think the max length of the SQL statement might have been 16 or 32Kb and without removing your spaces, your query is 18Kb. Though I don't know whether this was a Firebird restriction or had to do with the tool that I used.
One thing is the length of the query itself, another thing is the row size of your result set (here the max size at least was 64Kb, don't know whether it has increased or not). Normally, this can be avoided by defining your fields as BLOB rather than long (VAR)CHAR fields.
I'd also like to point out that it is normally better to have all your (inner) JOINs before any LEFT JOINs, if possible. The reason is that Firebirds optimizer freely rearranges all tables until it encounters a LEFT JOIN, but after that, the order is fixed (at least on Firebird 1.5 and probably 2.1, don't know about Firebird 2.5). So, in your case, Firebird cannot choose the order of the tables in the PLAN at all, if you'd put 'inner join cad1 conta2_' above 'left outer join crt1 carteira1_', then the optimizer could have chosen whether it should have conta2_ or pedidovend0_ as the first table in the plan. However, in your case it is very unlikely that you get any better plan by following my advice, your [LEFT] JOIN and WHERE clauses indicate that there's only one sensible plan that could be created (provided of course, that pedidovend0_.nummv$pdv1 and the fields to the right in you JOIN fields are indexed).
HTH,
Set
(e.g. I would change from
from
mv$pdv1 pedidovend0_
left outer join
crt1 carteira1_
on pedidovend0_.numcrt1=carteira1_.numcrt1
to
from mv$pdv1 p0
left join crt1 c1
on p0.numcrt1=c1.numcrt1
)
The reason for my problem in the first place was me trying to flatten a result set in Firebird rather than some statistical package (16 left joins or so since there could be up to 16 rows per person, if each of these 16 rows could have up to four records in another table, then you end up with 64 potential fields for each 'real' field - needless to say, the vast majority of the result fields would be <null>). I think the max length of the SQL statement might have been 16 or 32Kb and without removing your spaces, your query is 18Kb. Though I don't know whether this was a Firebird restriction or had to do with the tool that I used.
One thing is the length of the query itself, another thing is the row size of your result set (here the max size at least was 64Kb, don't know whether it has increased or not). Normally, this can be avoided by defining your fields as BLOB rather than long (VAR)CHAR fields.
I'd also like to point out that it is normally better to have all your (inner) JOINs before any LEFT JOINs, if possible. The reason is that Firebirds optimizer freely rearranges all tables until it encounters a LEFT JOIN, but after that, the order is fixed (at least on Firebird 1.5 and probably 2.1, don't know about Firebird 2.5). So, in your case, Firebird cannot choose the order of the tables in the PLAN at all, if you'd put 'inner join cad1 conta2_' above 'left outer join crt1 carteira1_', then the optimizer could have chosen whether it should have conta2_ or pedidovend0_ as the first table in the plan. However, in your case it is very unlikely that you get any better plan by following my advice, your [LEFT] JOIN and WHERE clauses indicate that there's only one sensible plan that could be created (provided of course, that pedidovend0_.nummv$pdv1 and the fields to the right in you JOIN fields are indexed).
HTH,
Set