Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 6 of 6 total |
Question on Live v canned queries |
Fri, Apr 14 2017 3:08 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 Roy Lambert |
Tue, Apr 18 2017 4:38 AM | Permanent Link |
David | Thanks Roy
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 Roy Lambert |
Tue, Apr 18 2017 12:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Wednesday, April 17, 2024 at 10:35 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |