Hello All,

When using a remote connection is it better to either:

a) select * from products

or

b) select description, price, stock_level from products

NOTE: The product table has 52 fields.

I would say that (b) would be a better performer, because less data is sent back from the
server.

Cheers Stu

I would always go for b).

1. If you have any bandwidth restriction across your network you just shouldn't throw
unnecessary data around. Note this counts x 100 if you have blob or memo fields in theDB.

2. Just as important are your WHERE & ORDER BY clauses in the query (or FILTER on a
TTable). Their efficiency will depend intimately on the quality of your indexes.

--

The only advantage of SELECT * is that it is robust if you change the structure of the DB
or rename the fields.

I regularly use SQL in the form

"SELECT * FROM TableX WHERE ID = 23454"

As a mechanism for pumping data into an object in code. The object only needs to know the
ID number of its record & the tablename. You can then return a full field set & datacast
them to Date, Integer, Float, Str, Memo etc. without knowing anything about the DB
structure at design-time.

This works super fast (however big the table, more or less!) because there is only 1
record coming back & the table is indexed on the ID field.

Adam

Stuart,

<< When using a remote connection is it better to either:

a) select * from products

or

b) select description, price, stock_level from products

NOTE: The product table has 52 fields.

I would say that (b) would be a better performer, because less data is sent
back from the server. >>

If the query result set is live, then there is no difference.   If the query
result set is not live, then b) is faster.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you very much, Tim and Adam.

I asked because some of our customers want to use our software from home, using VPN over
broadband to connect to the remote database server.

Obviously the speed of the data transmission, is limited to the upstream speed of the
broadband connection.  
At home this is around 512 kbps.

I'll pass speak to our main developer about changing the queries.

Cheers, Stu

Tim Young [Elevate Software] wrote:

>
> If the query result set is live, then there is no difference.   If
> the query result set is not live, then b) is faster.

Hi Tim. Could you expand on that?  I had understood that a live query
was faster since it did not need to create a copy of the table on the
client. I use ClientDataSets to cache the data so what I have been
doing for example on a Customer table:

qryCustList (live): perhaps just selecting Customer Name and phone
number (or whatever an end user might need to search on)

qryCustDetail(live) The user selects a record from the Customter List
and then I get the details which is usually a select * from the
customer table.

Ths seems to work fine and I thought that it was the most efficient.
Would I speed things up even more by using canned querys? I am using
this same qryList/qryDetail scenario throughout the application.



--
David Farrell-Garcia
Whidbey Island Software, LLC

David,

<< Hi Tim. Could you expand on that?  I had understood that a live query was
faster since it did not need to create a copy of the table on the client. >>

Neither a live query result set nor a canned query result set results in a
copy of the table on the client with DBISAM C/S.   A canned query result set
uses a temporary table, but it is always stored on the database server side.
A live query is faster in almost all cases because it doesn't require any
temporary tables or copying of data.  Both fetch data on demand in the chunk
size specified by the RemoteReadSize property.

However, you're getting your performance issues mixed up ( - the
question related to which is faster in terms of bandwidth consumption when
pulling rows across the wire.  Live query result sets always send over
entire rows at a time, not just the selected rows.  This doesn't mean that
live queries are slower overall, it just means that a canned query result
set with only two columns selected will be sending a lot less data (in most
cases) over the wire when data is fetched from the client, which was what
the original question pertained to.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:


> Neither a live query result set nor a canned query result set results
> in a copy of the table on the client with DBISAM C/S.

Yes, I knew that. Don't know why I said "client".

> Both fetch data on demand in the chunk size specified by the
> RemoteReadSize property.
,
Using ClientDataSets should I set the ClientDataSet.PacketRecords
property to match the RemoteReadSize?


>
> However, you're getting your performance issues mixed up ( - the
> question related to which is faster in terms of bandwidth consumption
> when pulling rows across the wire.  Live query result sets always
> send over entire rows at a time, not just the selected rows.

I assume you meant "selected columns", but yes, that seems logical.

> This
> doesn't mean that live queries are slower overall, it just means that
> a canned query result set with only two columns selected will be
> sending a lot less data (in most cases) over the wire when data is
> fetched from the client, which was what the original question
> pertained to.

In my app, i am more concerned about bandwidth then any delays on the
server, as some of the users will be using remote laptops with limited
bandwidth. As it stands, the performance is not too bad.  I was just
wanted to make sure I am doing all i can to minimize bandwidth
consumtion. It would seem that I should be using a mixture of live and
canned queries depending on how many columns I need to fetch.



--
David Farrell-Garcia
Whidbey Island Software, LLC

David,

<< Using ClientDataSets should I set the ClientDataSet.PacketRecords
property to match the RemoteReadSize? >>

It doesn't really matter since once DBISAM has the records over to the
client application in one chunk, the ClientDataSet will simply just read
them from local memory.

<< I assume you meant "selected columns", but yes, that seems logical. >>

Yes, sorry.

<< In my app, i am more concerned about bandwidth then any delays on the
server, as some of the users will be using remote laptops with limited
bandwidth. As it stands, the performance is not too bad.  I was just wanted
to make sure I am doing all i can to minimize bandwidth consumtion. It would
seem that I should be using a mixture of live and canned queries depending
on how many columns I need to fetch. >>

Yep.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Stuart,

I don't know if you have considered this but there is no need to set up a VPN connection. You can connect directly to the DBISAM server via TCP/IP only and you can also encrypt the
communication between client and server. Another option would be to use Remote Desktop or a third party remote product like WinConnect XP Server depending on how many users
required remote access and how many spare PCS were available at your office.

Tony.

Hey Tony,

>
> I don't know if you have considered this but there is no need to set up a VPN
connection.  You can connect directly to the DBISAM server via TCP/IP only
> and you can also encrypt the communication between client and server.
>

True, this is possible and something we will consider for remote users of our software.
For the EPOS software we develop, each till has its own DBISAM database.  This ensures the
till is usable even if the network/remote connection goes down.  From a shop owners point
of view, having a system that works all the time is the main concern.

We chose VPN because, we need to copy files to and from the server, using file shares.
These files include, images, text files, DBISAM dbx and DBISAM idx files.  
In the future, we many copy tables records using the remote connection of the DBISAM server.


>
> Another option would be to use Remote Desktop or a third party remote product like
WinConnect XP Server depending on how many users
> required remote access and how many spare PCS were available at your office.
>

We currently use VNC for remote support, I'll have a look at WinConnect XP Server.

Cheeers Stu