Re: [firebird-support] SQL Optimation best way

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Nov 4, 2015 at 8:41 AM, 'checkmail' check_m...@satron.de
[firebird-support]  wrote:

>
>
>  two tables
>
>
>
> A:
>
> Field1, Field2, Field3, Field4, Field5
>
>
>
> B
>
> FieldA,  FieldB, FieldC
>
>
>
> Now I would like to get the following:
>
>
>
> Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2
>
> In the same statement I would include
>
> Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5
>
>
>

Without having tried it, I suggest:

select sum (A.field3) from A where A.field1 = condition1 and A.field2 =
condition2
UNION
select sum (b.fieldc) from B inner join A
 where  A.field1 = condition1
and A.field2 = condition2
and B.fielda = A.field4
and B.fieldb = A.field5

If A.field3 and B.fieldc are of different types you may need to cast them.

Good luck,

Ann



>

Re: [firebird-support] Writing to Aliases.conf in non-admin mode

[Stefan Heymann li...@stefanheymann.de [firebird-support]]

> While that answer is correct, the better answer would be for the
> Project to modify engine and installer to be Windows Vista+
> compliant  and have all configuration files stored in All
> Users\Application Data\Firebird and not in Programs Files\Firebird

I just wanted to suggest that "\ProgramData\Firebird" was probably
better. But it seems this folder is the same as "All Users\Application
Data\Firebird".

This non-tree structure can be quite confusing ...


Regards

Stefan

Re: [firebird-support] FW: Scale-ability problems (PHP + firebird)

[map...@gmail.com [firebird-support]]

ibase_pconnect uses the same function as ibase_connect internally (the only 
diferrence is that is reusing connections) 
 So I recommend to start with it 
 http://php.net/manual/en/function.ibase-pconnect.php 
http://php.net/manual/en/function.ibase-pconnect.php  


 

Re: [firebird-support] SQL Optimation best way

[setysvar setys...@gmail.com [firebird-support]]

 >A.field1 and field2 are the intern project number, b.fielda and 
b.fieldb are the extern ordernumber year and number.
 >Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2
 >In the same statement I would include
 >Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5
 >It is possible without cte?

These seems like two completely different queries with no correlation, 
so my guess is that the answer is no, you need a cte or something 
similar (e.g. execute block).

However, if A has a 1:many correlation to B, and you only want to count 
those B's that also match condition1 and condition2, then something like

select sum(A.field3), sum((select sum(b.fieldc) from B where b.fielda = 
a.field4 and b.fieldb = a.field5)) --you need double parenthesis and sum
from A
where a.field1 = :condition1 and a.field2 = :condition2

could be a solution (make sure to test so that you don't get double or 
triple of what you want from sum(B) if some records of A had the same 
value for field1, field2, field4 and field5).

I doubt this is a noticable optimization compared to cte or execute 
block, I think of it as an alternative suitable in some cases, but I 
neither expect it to be superior nor inferior in those cases.

HTH,
Set






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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/

[firebird-support] FW: Scale-ability problems (PHP + firebird)

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Rudi,

we have a PHP & Firebird environment too. To Reduce the overhead problems we 
use some caching techniques. So the amount of connections for a new site 
request shrinks dramatically. On the other side we're trying to reduce the 
amount by rewriting / optimizing some core queries. The environment itself 
bases on the Zend Framework.

Hope this helps.

Regards
Martin