Filter an Sort performances

[Fernand Vanrie]

Hallo all,

Can someone confirm the following "performance" options:
When using the API and basic to load the data in a form based on a SQL
command, I suppose that it sould be better to include the filter options
directly in the the SQL statement and not using the filter and sort
properties off the form. I suppose that OO after changing the sort or
filter property simply rebuild the SQL statement and send a new request
to the server ?

Thanks for any hints

Fernand

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Fernand,

> Can someone confirm the following "performance" options:
> When using the API and basic to load the data in a form based on a SQL
> command, I suppose that it sould be better to include the filter options
> directly in the the SQL statement and not using the filter and sort
> properties off the form. I suppose that OO after changing the sort or
> filter property simply rebuild the SQL statement and send a new request
> to the server ?

A new statement is built and sent as soon as you call the form's
load/reload (resp. execute) method. So, it doesn't really matter whether
you set the Filter/Sort at the respective properties, or directly in the
SQL command: The subsequent re/load call will compose the statement
(which you would need to do, too) and send it to the server (which you
would need to do, too).

Ciao
Frank


--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Frank Schönheit - Sun Microsystems Germany wrote:

> Hi Fernand,
>
>  
>> Can someone confirm the following "performance" options:
>> When using the API and basic to load the data in a form based on a SQL
>> command, I suppose that it sould be better to include the filter options
>> directly in the the SQL statement and not using the filter and sort
>> properties off the form. I suppose that OO after changing the sort or
>> filter property simply rebuild the SQL statement and send a new request
>> to the server ?
>>    
>
> A new statement is built and sent as soon as you call the form's
> load/reload (resp. execute) method. So, it doesn't really matter whether
> you set the Filter/Sort at the respective properties, or directly in the
> SQL command: The subsequent re/load call will compose the statement
> (which you would need to do, too) and send it to the server (which you
> would need to do, too).
>
>  

I think however - and I think that Frank's response answers it - that
the question is one of 'best practices'.

There is a form that is about to be displayed to the user, the form will
have some filter appended to the SQL command saved with the form at
design time. How do I do so in the most efficient manner?

1 - Open the form on the desktop - update the filter property - call reload

2 - Alter the SQL statement prior to opening the form.

Good question!

Which is faster (more efficient)
- 2 looks like a better answer, doesn't it. This might then lead one to
conclude that it would be better to base your form on a QueryDefinition
rather then an embedded SQL command, since doing so allows you to easily
implement the workflow expressed in line 2 above.

Truth is though that Opening, Updating and Saving a query definition has
a cost also. If Im'm not mistaken, unless that querydefinition is set to
'run sql direct' then this cost includes a trip to the server. So, when
does one out weigh the other?

Other questions come to mind in thinking about how to answer what I
think is your real question.

Do you really want to close every form when the user is done with it?
When would it be better to hide form instead?
Is using a QueryDefinition the only way to achieve the workflow in line 2?
Can you use call back procedures (my term for assigning listeners, sorry
old habit) to catch the form load event, prior to the dataform actually
loading data, and add the filter condition at that point?

Anyway - is this (these) the question here?

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Drew,

> I think however - and I think that Frank's response answers it - that
> the question is one of 'best practices'.

Actually, I am not completely sure what the original question was :)

> There is a form that is about to be displayed to the user, the form will
> have some filter appended to the SQL command saved with the form at
> design time. How do I do so in the most efficient manner?
>
> 1 - Open the form on the desktop - update the filter property - call reload
>
> 2 - Alter the SQL statement prior to opening the form.
>
> Good question!
>
> Which is faster (more efficient)
> - 2 looks like a better answer, doesn't it.

Yes.

> This might then lead one to
> conclude that it would be better to base your form on a QueryDefinition
> rather then an embedded SQL command, since doing so allows you to easily
> implement the workflow expressed in line 2 above.
>
> Truth is though that Opening, Updating and Saving a query definition has
> a cost also. If Im'm not mistaken, unless that querydefinition is set to
> 'run sql direct' then this cost includes a trip to the server.

If you update a query definition programmatically, no server traffic
should happen.
If you do it by UI, then some database meta data will be queried, but
we're constantly improving on caching those where possible :)

> Do you really want to close every form when the user is done with it?
> When would it be better to hide form instead?
> Is using a QueryDefinition the only way to achieve the workflow in line 2?
> Can you use call back procedures (my term for assigning listeners, sorry
> old habit) to catch the form load event, prior to the dataform actually
> loading data, and add the filter condition at that point?

The XRowSetApproveListener might come handy here: It's called before the
actual execution of the RowSet/Form happens, and it should be possible
to modify the statement/filter/order/other in this callback.

Ciao
Frank

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Frank Schönheit - Sun Microsystems Germany wrote:

> Hi Drew,
>
>  
>> I think however - and I think that Frank's response answers it - that
>> the question is one of 'best practices'.
>>    
>
> Actually, I am not completely sure what the original question was :)
>
>  
>> There is a form that is about to be displayed to the user, the form will
>> have some filter appended to the SQL command saved with the form at
>> design time. How do I do so in the most efficient manner?
>>
>> 1 - Open the form on the desktop - update the filter property - call reload
>>
>> 2 - Alter the SQL statement prior to opening the form.
>>
>> Good question!
>>
>> Which is faster (more efficient)
>> - 2 looks like a better answer, doesn't it.
>>    
>
> Yes.
>
>  
>> This might then lead one to
>> conclude that it would be better to base your form on a QueryDefinition
>> rather then an embedded SQL command, since doing so allows you to easily
>> implement the workflow expressed in line 2 above.
>>
>> Truth is though that Opening, Updating and Saving a query definition has
>> a cost also. If Im'm not mistaken, unless that querydefinition is set to
>> 'run sql direct' then this cost includes a trip to the server.
>>    
>
> If you update a query definition programmatically, no server traffic
> should happen.
>  

Excellent...hmmm, that old saying comes to mind...Trust but Verify.

This would be a good point to interject - Base supports, at least for
JDBC connectors, a logging mechanism that could be employed here for
profiling purposes..I think. Specifics of using this are on the wiki.
http://wiki.services.openoffice.org/wiki/Logging_JDBC_Activity

>  
>> Do you really want to close every form when the user is done with it?
>> When would it be better to hide form instead?
>> Is using a QueryDefinition the only way to achieve the workflow in line 2?
>> Can you use call back procedures (my term for assigning listeners, sorry
>> old habit) to catch the form load event, prior to the dataform actually
>> loading data, and add the filter condition at that point?
>>    
>
> The XRowSetApproveListener might come handy here: It's called before the
> actual execution of the RowSet/Form happens, and it should be possible
> to modify the statement/filter/order/other in this callback.
>
>  

Again, Excellent...now if we could do that with Sun Report Builder
generated reports that would just be supper.. or can we? (didn't see
that one coming did you...)

Drew





---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Frank Schönheit - Sun Microsystems Germany wrote:
> Hi Drew,
>
>  
>> I think however - and I think that Frank's response answers it - that
>> the question is one of 'best practices'.
>>    
>
> Actually, I am not completely sure what the original question was :)
>  
I want to understand  how  the Base application is  building  the  SQL
statements based on  the command, filter and  sort properties  an finaly
how "cost" effective is this statement on the  the server side. Behind
this question is also a concern, when developing a frontend I must
produce code who will work for more than one Driver. When using a local
network then i sould use the Native MySql-connector when going over the
web then a ODBC connector comes in place. Native MYSQL statements will
work for both drivers , will statements build by the Base application do
alsoo ? =)

thanks for your opinions

Fernand

>  
>> There is a form that is about to be displayed to the user, the form will
>> have some filter appended to the SQL command saved with the form at
>> design time. How do I do so in the most efficient manner?
>>
>> 1 - Open the form on the desktop - update the filter property - call reload
>>
>> 2 - Alter the SQL statement prior to opening the form.
>>
>> Good question!
>>
>> Which is faster (more efficient)
>> - 2 looks like a better answer, doesn't it.
>>    
>
> Yes.
>
>  
>> This might then lead one to
>> conclude that it would be better to base your form on a QueryDefinition
>> rather then an embedded SQL command, since doing so allows you to easily
>> implement the workflow expressed in line 2 above.
>>
>> Truth is though that Opening, Updating and Saving a query definition has
>> a cost also. If Im'm not mistaken, unless that querydefinition is set to
>> 'run sql direct' then this cost includes a trip to the server.
>>    
>
> If you update a query definition programmatically, no server traffic
> should happen.
> If you do it by UI, then some database meta data will be queried, but
> we're constantly improving on caching those where possible :)
>
>  
>> Do you really want to close every form when the user is done with it?
>> When would it be better to hide form instead?
>> Is using a QueryDefinition the only way to achieve the workflow in line 2?
>> Can you use call back procedures (my term for assigning listeners, sorry
>> old habit) to catch the form load event, prior to the dataform actually
>> loading data, and add the filter condition at that point?
>>    
>
> The XRowSetApproveListener might come handy here: It's called before the
> actual execution of the RowSet/Form happens, and it should be possible
> to modify the statement/filter/order/other in this callback.
>
> Ciao
> Frank
>
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Drew,

> Excellent...hmmm, that old saying comes to mind...Trust but Verify.
>
> This would be a good point to interject - Base supports, at least for
> JDBC connectors, a logging mechanism that could be employed here for
> profiling purposes..I think. Specifics of using this are on the wiki.
> http://wiki.services.openoffice.org/wiki/Logging_JDBC_Activity

Please do ... hope I didn't promise too much :)

>> The XRowSetApproveListener might come handy here: It's called before the
>> actual execution of the RowSet/Form happens, and it should be possible
>> to modify the statement/filter/order/other in this callback.
>
> Again, Excellent...now if we could do that with Sun Report Builder
> generated reports that would just be supper.. or can we? (didn't see
> that one coming did you...)

no, the SRB doesn't allow for this ATM.

However, I am not sure that callbacks are the best way here. Depends on
the scenario, of course.
My idea with reports and forms would be to pass Filter/Order/etc. at
time of opening the document. Finally, I suppose you do not want to
programmatically open the report designer and execute the report, but
you want to open the report document, each time with a different
filter/sort. Yes?

Ciao
Frank

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Fernand,

>>> I think however - and I think that Frank's response answers it - that
>>> the question is one of 'best practices'.
>>>    
>> Actually, I am not completely sure what the original question was :)
>>  
> I want to understand  how  the Base application is  building  the  SQL
> statements based on  the command, filter and  sort properties  an finaly
> how "cost" effective is this statement on the  the server side.

Letting Base assemble the final statement form its parts - the basic
query, the filter, the sort order, the having clause, the group-by
clause - is certainly more expensive than you manually creating the
string, but I'd say to a little amount only. What happens is that Base
asks for some meta data (the quoting character, probably), and then just
concatenates some strings.

> Behind
> this question is also a concern, when developing a frontend I must
> produce code who will work for more than one Driver. When using a local
> network then i sould use the Native MySql-connector when going over the
> web then a ODBC connector comes in place. Native MYSQL statements will
> work for both drivers , will statements build by the Base application do
> alsoo ? =)

If the drivers work properly, then yes :)

Finally, that's one reason of existence for the Base API: abstract from
the underlying database/driver, and allow client code to be generic.

Of course, from a certain point on, this is illusionary, as the
abstraction level of the SDB/C/X API does not (and cannot) capture
/everything/ which would needed ...

Ciao
Frank

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...