Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Question on Live v canned queries
Fri, Apr 14 2017 3:08 PMPermanent Link

David

I understand that if you use a live query it will return all the fields in the row, even  if you have used select to specify only the fields you require.  If you use a canned query though, it will only return the specified fields, but the table will have to be created on disk first.

My question are thus.

1. In a Live query, is there any advantage in using select to specify the fields, would you just be just as well using select *?

2. Does using live queries have any negative impact on the client end such as taking longer to load the result set?

3.  Are there any tips for reducing the load on the network when using Live queries.

Thanks for any help.
David.
Sat, Apr 15 2017 8:54 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

David

This is from memory / DBISAM 4.26 Build 3 so may be wrong by now

>I understand that if you use a live query it will return all the fields in the row, even if you have used select to specify only the fields you require. If you use a canned query though, it will only return the specified fields, but the table will have to be created on disk first.


>1. In a Live query, is there any advantage in using select to specify the fields, would you just be just as well using select *?

Fields not selected cannot be accessed which can be an advantage if you want to limit things eg in a DBGrid

>2. Does using live queries have any negative impact on the client end such as taking longer to load the result set?

Its the other way round, live queries are essentially the same as a filter on a table using the indices so that's faster to start with. DBISAM is (may always have been) smart enough to only load/transfer enough data to fill the visible controls, or satisfy your program, at a time. This is especially useful with memo fields which will not be loaded / transfered unless you either display them or do something with them in your program.

>3. Are there any tips for reducing the load on the network when using Live queries.

Yup - the best one is don't display or process any of the data on the client. Its not much use but its definitely brilliant for reducing network load. So if you can do it just using SQL that is the best.

Next is display only what is needed. If you use one of the enhanced grids and it will work properly as a dbgrid do that rather than loading the entire dataset into it.

As far as practicable avoid displaying memo or graphic fields.

Essentially anything you show your user or that your program does something with has to be transferred. Turning compression on should help.

Probably the best advice is to look at web pages, think about how they have evolved over the years and then head quickly in the other direction Smiley

Roy Lambert
Tue, Apr 18 2017 4:38 AMPermanent Link

David

Thanks Roy Smile

Roy Lambert wrote:

David

This is from memory / DBISAM 4.26 Build 3 so may be wrong by now

>I understand that if you use a live query it will return all the fields in the row, even if you have used select to specify only the fields you require. If you use a canned query though, it will only return the specified fields, but the table will have to be created on disk first.


>1. In a Live query, is there any advantage in using select to specify the fields, would you just be just as well using select *?

Fields not selected cannot be accessed which can be an advantage if you want to limit things eg in a DBGrid

>2. Does using live queries have any negative impact on the client end such as taking longer to load the result set?

Its the other way round, live queries are essentially the same as a filter on a table using the indices so that's faster to start with. DBISAM is (may always have been) smart enough to only load/transfer enough data to fill the visible controls, or satisfy your program, at a time. This is especially useful with memo fields which will not be loaded / transfered unless you either display them or do something with them in your program.

>3. Are there any tips for reducing the load on the network when using Live queries.

Yup - the best one is don't display or process any of the data on the client. Its not much use but its definitely brilliant for reducing network load. So if you can do it just using SQL that is the best.

Next is display only what is needed. If you use one of the enhanced grids and it will work properly as a dbgrid do that rather than loading the entire dataset into it.

As far as practicable avoid displaying memo or graphic fields.

Essentially anything you show your user or that your program does something with has to be transferred. Turning compression on should help.

Probably the best advice is to look at web pages, think about how they have evolved over the years and then head quickly in the other direction Smiley

Roy Lambert
Tue, Apr 18 2017 12:16 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

Sorry for the delay in responding.

<< 1. In a Live query, is there any advantage in using select to specify the fields, would you just be just as well using select *? >>

On the client side there are some advantages, specifically less TField instances and quicker navigation because of this.

<< 2. Does using live queries have any negative impact on the client end such as taking longer to load the result set? >>

No, in fact they are the quickest way to execute a query.

<< 3.  Are there any tips for reducing the load on the network when using Live queries. >>

With remote sessions, best way to optimize navigation of both query result sets and tables is by judiciously using the RemoteReadSize property:

http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=rsdelphiwin32&version=10T&comp=TDBISAMDataSet&prop=RemoteReadSize

Internally, the TDBISAMDataSet already optimizes this usage of this property for multi-record controls such as the TDBGrid, setting it to the same number of visible records when reading records (and restoring it afterward).

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Apr 18 2017 1:59 PMPermanent Link

David

Hi Tim, thanks for your reply.

OK so basically if I have this right, DBISAM will send a complete row on a live query, but the client will only load the specified fields in the query, so this way things might be a bit quicker on the client by reducing the number of fields instead of using just Select *?

So what happens if you have a query that has 100 fields, but in the query component, you  only add 10 fields.  I assume that only the 10 fields will be loaded, but would there be any advantage in specifying the 10 fields in the query rather than selecting * and only adding the 10 fields to the query component.

Tim Young [Elevate Software] wrote:

David,

Sorry for the delay in responding.

<< 1. In a Live query, is there any advantage in using select to specify the fields, would you just be just as well using select *? >>

On the client side there are some advantages, specifically less TField instances and quicker navigation because of this.

<< 2. Does using live queries have any negative impact on the client end such as taking longer to load the result set? >>

No, in fact they are the quickest way to execute a query.

<< 3.  Are there any tips for reducing the load on the network when using Live queries. >>

With remote sessions, best way to optimize navigation of both query result sets and tables is by judiciously using the RemoteReadSize property:

http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=rsdelphiwin32&version=10T&comp=TDBISAMDataSet&prop=RemoteReadSize

Internally, the TDBISAMDataSet already optimizes this usage of this property for multi-record controls such as the TDBGrid, setting it to the same number of visible records when reading records (and restoring it afterward).

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Apr 19 2017 1:42 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

<< OK so basically if I have this right, DBISAM will send a complete row on a live query, but the client will only load the specified fields in the query, so this way things might be a bit quicker on the client by reducing the number of fields instead of using just Select *? >>

Yes, but a little nit: the TDBISAMQuery won't "load" fields, at least not in one shot.  It depends upon what data-aware controls are connected to the TDBISAMQuery, or which fields are referenced in your code after the TDBISAMQuery component is opened.  In Delphi's TDataSet architecture, fields are read from the record buffers when their various accessor properties are referenced (Value, AsString, AsInteger, etc. properties).  This is why BLOBs can be read on-demand instead of needing to be loaded ahead of time.

<< So what happens if you have a query that has 100 fields, but in the query component, you  only add 10 fields.  I assume that only the 10 fields will be loaded, but would there be any advantage in specifying the 10 fields in the query rather than selecting * and only adding the 10 fields to the query component. >.

No, there wouldn't be any advantage. The two are equivalent in terms of the TDBISAMQuery component (or any TDataSet-descendant, for that matter).

Tim Young
Elevate Software
www.elevatesoft.com
Image