 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Enquiring Mind
Guest
|
 Posted: Thu May 03, 2007 3:50 pm Post subject: Questions about TSQLDataset and transfer of Blob fields |
 |
|
Hi,
Can anyone help clarify how data transfer works with TSQLDatasets?
I have a database table Items containing an integer field ItemIndex, a
string field ItemName, and a Blob field ItemImage.
I have a TSQLDataset SQLItems defined by:
SQLItems.SQLConnection:= SQLConnection1;
SQLItems.CommandText:= 'select ItemIndex, ItemName, ItemImage from Items
where ItemIndex> :MinItemIndex'
I have a custom data structure to receive data from the table defined by:
type
TItem= class
ItemIndex: integer;
ItemName: string;
ItemImage: TMemoryStream;
constructor Create;
destructor Destroy; override;
end;
TItems= class
Items: array of TItem;
destructor Destroy; override;
procedure AddItem(Item: TItem);
procedure Clear;
end;
Question 1. In the following code, is the data of every record defined by
the SQL command downloaded from the database to the client program even
though the SQLDataset's fields are not referenced? IOW, do the methods First
and Next cause transfer of data to the client program? Or do these methods
just cause the cursor to move on the server, and data is only fetched when
SQLDataset fields are referenced?
SQLItems.Open;
SQLItems.First;
while not SQLItems.EOF do
SQLItems.Next;
Question 2. In the following code, do the TSQLDataset field objects
represent data on the client or on the server? If they represent data on the
client, are all the fields downloaded except for the Blob field each time
that the cursor moves? Are the TSQLDataset fields read-only? If not, what is
the effect of assigning data to a field?
SQLItems.Open;
SQLItems.First;
Items.Clear;
while not SQLItems.EOF do begin
Item:= TItem.Create;
Item.ItemIndex:= SQLItems.FieldByName('ItemIndex').AsInteger;
Item.ItemName:= SQLItems.FieldByName('ItemName').AsString;
{Note: no access to Blob field!}
Items.AddItem(Item);
SQLItems.Next;
end;
Question 3. Blob data can be transferred to the client using code like the
following. But is the Blob data fetched from the database only in the line
marked with an * , where the Blob field is referred to the first time?
SQLItems.Open;
SQLItems.First;
Items.Clear;
while not SQLItems.EOF do begin
Item:= TItem.Create;
Item.ItemIndex:= SQLItems.FieldByName('ItemIndex').AsInteger;
Item.ItemName:= SQLItems.FieldByName('ItemName').AsString;
SQLItems.FieldByName('ItemImage').SaveToStream(Item.ItemImage); {*}
Items.AddItem(Item);
SQLItems.Next;
end;
Question 4. How is Blob data uploaded from the client to the server? I
presume that Blob data cannot be included in a SQL Insert or Update command,
and that a specific database server method must be used.
Question 5. If I wish to download or upload the Blob field in small chunks,
how is this be programmed?
TIA,
Enquiring Mind |
|
| Back to top |
|
 |
Craig Stuntz [TeamB]
Guest
|
| Posted: Thu May 03, 2007 7:57 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
Enquiring Mind wrote:
| Quote: | Question 1. In the following code, is the data of every record
defined by the SQL command downloaded from the database to the client
program even though the SQLDataset's fields are not referenced?
|
Yes. Moreover, most DBs will return multiple records when you request
one, in order to maximize use of the TCP packets. The more data you can
get into a single request, the faster it comes to you. The DB client
caches these "extra" records until you fetch them, even with
unidirectional cursors. The extra memory used is trivial next to the
performance implications of one packet per record.
| Quote: | Question 2. In the following code, do the TSQLDataset field objects
represent data on the client or on the server?
|
On the client.
| Quote: | If they represent data
on the client, are all the fields downloaded except for the Blob
field each time that the cursor moves?
|
Yes. You may get blob fields, too, depending upon the component and
the DB server. SQL monitoring will show when they come across as, for
IB, anyway, it's a separate API call.
| Quote: | Are the TSQLDataset fields
read-only? If not, what is the effect of assigning data to a field?
|
Yes. I've never tried assigning to them, but I would hope such
assignments were ignored or raised an error.
| Quote: | Question 3. Blob data can be transferred to the client using code
like the following. But is the Blob data fetched from the database
only in the line marked with an * , where the Blob field is referred
to the first time?
|
With dbExpress, I'm not sure, but like I said, it will show up in the
monitor when it's fetched.
| Quote: | Question 4. How is Blob data uploaded from the client to the server?
I presume that Blob data cannot be included in a SQL Insert or Update
command, and that a specific database server method must be used.
|
Many DBs can have blob data as literal SQL, but in any event when you
use TClientDataSet the TSQLResolver will construct a parameterized
query and bind the blob data to the param.
| Quote: | Question 5. If I wish to download or upload the Blob field in small
chunks, how is this be programmed?
|
AFAIK you'd have to use the DB API directly. IB's API allows this, for
example.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
http://qc.borland.com -- Vote for important issues |
|
| Back to top |
|
|
Enquiring Mind
Guest
|
| Posted: Thu May 03, 2007 9:24 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
"Craig Stuntz [TeamB]" <craig_stuntz (AT) nospam (DOT) please [a.k.a. acm.org]> wrote
in message news:4639f84c (AT) newsgroups (DOT) borland.com...
| Quote: | Yes. Moreover, most DBs will return multiple records when you request
one, in order to maximize use of the TCP packets. The more data you can
get into a single request, the faster it comes to you. The DB client
caches these "extra" records until you fetch them, even with
unidirectional cursors. The extra memory used is trivial next to the
performance implications of one packet per record.
|
Presumably the number of records returned by the DB server depends on the
size of the data contained in each record. If I request a single record
containing a Blob of say 700 kB, I would hope that the server returns just
the one record requested.
| Quote: | Question 4. How is Blob data uploaded from the client to the server?
I presume that Blob data cannot be included in a SQL Insert or Update
command, and that a specific database server method must be used.
Many DBs can have blob data as literal SQL,
|
Do you mean that the Blob value may be included in the Insert command as if
it were a string literal? If so, what if the Blob contains bytes that
correspond to apostrophes- wouldn't that cause the reading of the string
literal to be aborted prematurely?
| Quote: | but in any event when you
use TClientDataSet the TSQLResolver will construct a parameterized
query and bind the blob data to the param.
|
How does this work if I am not using a CDS nor a dataset provider, but I
just wish to upload data from a custom data structure? Can parameters be
embedded in SQL Insert command, say like in the following code:
SQLItems.CommandText:= 'insert into Items(ItemIndex, ItemName, ItemImage)
values(:ItemIndex, :ItemName, :ItemImage)';
SQLItems.Params.ParamByName('ItemIndex'):= ItemI.ItemIndex.;
....
SQLItems.Params.ParamByName('ItemImage').AsBlob.ReadFromStream(ItemI.ItemImage);
SQLItems.ExecSQL;
| Quote: |
Question 5. If I wish to download or upload the Blob field in small
chunks, how is this be programmed?
AFAIK you'd have to use the DB API directly. IB's API allows this, for
example.
Pacheco's 'Delphi for .Net Developer's Guide' provides an example where the |
data of a Blob field is retrieved from a database in small chunks using a
method called GetBytes of the .Net SqlDataReader class. From what I can
understand, the SqlDataReader class is similar in concept to the TSQLDataset
class. So I was wondering why a similar procedure could not be achieved
using TSQLDataset?
I have heard it said that transferring Blobs in chunks of reasonable size
improves the scalability of the application, therefore I was wondering if
this has been addressed by the dbExpress framework, maybe in its internal
implementation?
BTW, if you're wondering why I am interested in transferring data from a
database into a custom data structure rather than a client dataset, the
reason is simple: I have tested the two alternatives for the same dataset in
a case when the data is to be browsed and not edited. In the first I
connected a CDS to the database, and use the data-aware TDBCtrlGrid to
display the data; in the second I read the data directly from the
SQLDataset, and use a TDrawGrid to display the data. I timed the time it
takes to get the data from the database to the visual control that displays
it. In the case of the CDS and data-aware control, the time was in the range
of 7.4-9.4 sec. In the case of the custom data structure and a non
data-aware control the time was in the range of 0.3-0.8 sec!
Enquiring Mind |
|
| Back to top |
|
|
Craig Stuntz [TeamB]
Guest
|
| Posted: Thu May 03, 2007 9:43 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
Enquiring Mind wrote:
| Quote: | Presumably the number of records returned by the DB server depends on
the size of the data contained in each record.
|
Sure.
| Quote: | Do you mean that the Blob value may be included in the Insert command
as if it were a string literal?
|
It's possible. IB allows this, for example.
| Quote: | If so, what if the Blob contains
bytes that correspond to apostrophes- wouldn't that cause the reading
of the string literal to be aborted prematurely?
|
In SQL you double an apostrophe to put it in mid-string:
SELECT * FROM FOO WHERE BAR = 'Can''t';
| Quote: | but in any event when you
use TClientDataSet the TSQLResolver will construct a parameterized
query and bind the blob data to the param.
How does this work if I am not using a CDS nor a dataset provider,
but I just wish to upload data from a custom data structure? Can
parameters be embedded in SQL Insert command, say like in the
following code:
|
Almost; just change the second-to-last line:
| Quote: | SQLItems.CommandText:= 'insert into Items(ItemIndex, ItemName,
ItemImage) values(:ItemIndex, :ItemName, :ItemImage)';
SQLItems.Params.ParamByName('ItemIndex'):= ItemI.ItemIndex.; ...
|
SQLItems.Params.ParamByName('ItemImage').LoadFromStream(
ItemI.ItemImage);
| Quote: | SQLItems.ExecSQL;
From what I can understand, the SqlDataReader class is similar
in concept to the TSQLDataset class. So I was wondering why a similar
procedure could not be achieved using TSQLDataset?
|
Conceptually, no problem. But I don't know if dbExpress has this
methods.
| Quote: | I have heard it said that transferring Blobs in chunks of reasonable
size improves the scalability of the application,
|
Depends on the app. If you routinely deal with GB-sized blobs, then
yes.
| Quote: | In the case of the CDS and
data-aware control, the time was in the range of 7.4-9.4 sec. In the
case of the custom data structure and a non data-aware control the
time was in the range of 0.3-0.8 sec!
|
It should not be this slow if you're actually doing equivalent work.
I'd suggest profiling.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
How to ask questions the smart way:
http://www.catb.org/~esr/faqs/smart-questions.html |
|
| Back to top |
|
|
Enquiring Mind
Guest
|
| Posted: Fri May 04, 2007 5:14 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
"Craig Stuntz [TeamB]" <craig_stuntz (AT) nospam (DOT) please [a.k.a. acm.org]> wrote
in message news:463a1127 (AT) newsgroups (DOT) borland.com...
| Quote: |
I have heard it said that transferring Blobs in chunks of reasonable
size improves the scalability of the application,
Depends on the app. If you routinely deal with GB-sized blobs, then
yes.
Would transferring Blobs of about 1-2 MB in a single chunk normally be |
acceptable from a scalability point of view?
| Quote: | In the case of the CDS and
data-aware control, the time was in the range of 7.4-9.4 sec. In the
case of the custom data structure and a non data-aware control the
time was in the range of 0.3-0.8 sec!
It should not be this slow if you're actually doing equivalent work.
I'd suggest profiling.
Although the end result of the two approaches is equivalent, I suspect that |
the reason why the CDS approach was so much slower is that it was causing
large Blobs to be downloaded even though they were not requested in the SQL
select statement. Why this should be the case I don't know. I also counted
the number of AfterScroll events fired by SQLDataset, and this showed that
in the case of the CDS set-up the records of SQLDataset were for some
reason visited twice, when once would have been sufficient.
Enquiring Mind |
|
| Back to top |
|
|
Craig Stuntz [TeamB]
Guest
|
| Posted: Fri May 04, 2007 5:45 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
Enquiring Mind wrote:
| Quote: | Would transferring Blobs of about 1-2 MB in a single chunk normally
be acceptable from a scalability point of view?
|
I think it's a pretty good idea, at least if you don't so many
simultaneous connections and so little RAM that it pushes your clients
or servers into VM. Transferring it in multiple chunks means more
TCP/IP chatter, which may hurt scalability more than memory use.
| Quote: | Although the end result of the two approaches is equivalent, I
suspect that the reason why the CDS approach was so much slower is
that it was causing large Blobs to be downloaded even though they
were not requested in the SQL select statement. Why this should be
the case I don't know.
|
SQL monitoring will tell you whether this is true or not with
certainty.
| Quote: | I also counted the number of AfterScroll
events fired by SQLDataset, and this showed that in the case of the
CDS set-up the records of SQLDataset were for some reason visited
twice, when once would have been sufficient.
|
Build with Debug DCUs, put a breakpoint on AfterScroll, and look at
the call stack(s) to find out.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
http://blogs.teamb.com/craigstuntz/articles/403.aspx |
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Fri May 04, 2007 7:48 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
Enquiring Mind wrote:
| Quote: | Would transferring Blobs of about 1-2 MB in a single chunk normally
be acceptable from a scalability point of view?
|
Assuming a 4k network packet size, which is common, 1 mb only requires
250 packets. That should not be a problem unless you are anticipating
really large numbers of users or a very slow network.
| Quote: |
In the case of the CDS and
data-aware control, the time was in the range of 7.4-9.4 sec. In
the case of the custom data structure and a non data-aware
control the time was in the range of 0.3-0.8 sec!
It should not be this slow if you're actually doing equivalent work.
I'd suggest profiling.
Although the end result of the two approaches is equivalent, I
suspect that the reason why the CDS approach was so much slower is
that it was causing large Blobs to be downloaded even though they
were not requested in the SQL select statement. Why this should be
the case I don't know. I also counted the number of AfterScroll
events fired by SQLDataset, and this showed that in the case of the
CDS set-up the records of SQLDataset were for some reason visited
twice, when once would have been sufficient.
|
It is impossible for the CDS to download a field that is not included
in the SELECT clause. What version of Delphi are you using? How many
rows does the SELECT return?
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
Enquiring Mind
Guest
|
| Posted: Fri May 04, 2007 8:43 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
"Bill Todd" <no (AT) no (DOT) com> wrote in message
news:463b47c5$1 (AT) newsgroups (DOT) borland.com...
| Quote: | Enquiring Mind wrote:
Assuming a 4k network packet size, which is common, 1 mb only requires
250 packets. That should not be a problem unless you are anticipating
really large numbers of users or a very slow network.
Thnaks for that. |
| Quote: |
It is impossible for the CDS to download a field that is not included
in the SELECT clause. What version of Delphi are you using? How many
rows does the SELECT return?
I am using Delphi 7, Interbase 6.5. The query returns 50 records. The |
records contain large digital photos and small thumbnail pictures in blob
fields.
I have had another look at the code of the CDS version and see that the
description I gave in my previous post is slightly inaccurate. I did not
specify only non-Blob fields in the SELECT statement - I put an * to
indicate all fields. However, I set poFetchBlobsOnDemand:= True in the
DatasetProvider options, with the expectation that the Blob fields would not
get downloaded when the CDS.Open method is executed. With this setting I
expected the Open method to only download the integer and string fields,
which account for just a very small percentage of the total record data size
when the Blob fields are taken into account. I later extract the small
thumbnail Blobs from the CDS, with the expectation that referencing this
particular Blob field will trigger the Blob download on demand. At no point
in the initial process that I timed are the main Image Blobs referenced.
Any comments on what might be going on?
Regards,
Enquiring Mind |
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Fri May 04, 2007 9:15 pm Post subject: Re: Questions about TSQLDataset and transfer of Blob fields |
|
|
Enquiring Mind wrote:
| Quote: | I am using Delphi 7, Interbase 6.5. The query returns 50 records. The
records contain large digital photos and small thumbnail pictures in
blob fields.
I have had another look at the code of the CDS version and see that
the description I gave in my previous post is slightly inaccurate. I
did not specify only non-Blob fields in the SELECT statement - I put
an * to indicate all fields. However, I set poFetchBlobsOnDemand:=
True in the DatasetProvider options, with the expectation that the
Blob fields would not get downloaded when the CDS.Open method is
executed. With this setting I expected the Open method to only
download the integer and string fields, which account for just a very
small percentage of the total record data size when the Blob fields
are taken into account. I later extract the small thumbnail Blobs
from the CDS, with the expectation that referencing this particular
Blob field will trigger the Blob download on demand. At no point in
the initial process that I timed are the main Image Blobs referenced.
|
If the blobs are included in the SELECT statement they are going to be
downloaded from the database server to your workstation one-by-one as
the CDS scrolls through the records loading the specified fields into
memory. Note that Delphi 2006 and later use a much more efficient
memory manager which makes loading data into a CDS much faster. The
larger the data the greater the performance improvement.
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
