Re: Large datasets

sqlexpress

Re: Large datasets

Thread Starter: Boris Borzic Started: 6/5/2006 5:15 PM UTC

Replies: 5

6/5/2006 5:15 PM UTC

Re: Large datasets

Reply Quote

Boris Borzic

"Jimmy Malan" <fst-procurement@telkomsa.net> wrote in
news:8bdefi2fqy2se0u.050620061501@ANDREA:

After reading through the newsgroup I saw a discussion where someone
suggested using SqlSelect to create a cursor on the server side, but I
don't think that is the answer in this case as everything was handled
on one machine.

Even on one machine, there there is a server (PgSQL) and client (your xbase++ app). If you really need to browse such a large result set, the solution is to use SQLSelect with either a dynamic or keyset driven cursor.

However, I do not recomend browsing such a large result set. I suggest that you redesign the UI and browse data in smaller chunks.

Best regards,
Boris Borzic
-- news://news.Xb2.NET
http://www.Xb2.NET
http://www.SQLExpress.net
industrial strength Xbase++ development tools

6/6/2006 11:46 AM UTC

Re: Large datasets

Reply Quote

Phil Ide

Jimmy,

Being new to Sql it seems as if I am in for a steep learning curve. Have so
many questions and to compound matters there seems to be a scarcity of
examples from which to work.

One thing you really have to be aware of:

With DBF files, you only ever deal with a single record at a time. As you
attempt to access a field, the record is read into memory, and further
accesses to fields are taken from the memory copy until a record movement
occurs, then the process starts again.

With SQL, you read the whole dataset into memory (we'll ignore cursors for
now and discuss them later).

This puts a tremendous strain on your application and machine when the
dataset is large, it also floods the network slowing every other machine and
application attempting to communicate on it, for as long as the data is
being transferred to you.

The good solution is to create a dataset that is stored on the server (i.e.
create a server-side cursor) and fetch records on demand, just as you would
with a DBF. DBF engines have the advantage that when they can, they'll read
bunch of records in and cache them, with SQL you can't do that (well, ODBC
for DBF actually caches lots, but let's not go there :0)

If the record size is large, then transferring a single record can take a
significant amount of time - significant being a relative term where we
determine 'signifcant' as "the user percieves the application to be slow or
unresponsive", even though they only wait 1/100th of a second for a record
to display. In this event, you need to consider performing pre-fetches of
the data using a 'window' of x many records.

In fact, once you have done the pre-fetch code for SQL, you can then apply
the same techniques to DBF files and you'll see a good performance increase
there too.

Regards,
-- Phil Ide

***************************************
* Xbase++ FAQ, Libraries and Sources: *
* goto: http://www.idep.org.uk/xbase *
***************************************

I will make the main entrance to my fortress standard-sized. While elaborate
60-foot high double-doors definitely impress the masses, they are hard to
close quickly in an emergency.
[Things I'd do as an Evil Overlord]

6/7/2006 10:12 PM UTC

Re: Large datasets

Reply Quote

Phil Ide

Mahanimann,

The table in question contains items sold in a shop, and I need to search for item text, ID, who sold this, date, time etc. With "redesign the UI", do you suggest that the user must give the search info before we do the SELECT command? No "list-all-rows-then-start-filtering" like in Clipper, but the opposite?

Yes, that's exactly the way SQL should be used. In fact, in Clipper you did
the same thing, because Clipper only reads one record at a time and filters
*never* apply to the current record - you entered a filter, and it was
applied next time you made a record movement.

Most SQL engines support 'views', which are predefined queries that you
treat as if they were tables. I have a view which selects data from one
table, performs lookups (via JOINs) into two others and filters out records
that match a SELECT into a 4th table.

I have another view (let's call it VIEW_C) which does a SELECT into the
first view, then uses another view to filter out records.

In my application, I can do a SELECT into VIEW_C to bring back a dataset
which contains only those rows that pass all the filters, and combines the
fields from several tables to create a record. Since my final SELECT
statement can also contain filters, I can limit the dataset even further.

Such a query (as the final SELECT above) runs in milliseconds, and the data
returned is usually only a dozen records or so. If I had to physically
download every record from the master table and any associated records from
the secondary tables, then manually decide whether to accept or reject the
record, it would take several seconds to determine all valid records even if
there were only a few dozen records in the primary table.

The manual method is essentially what Clipper (and Xbase++) does when
looking at DBF's. Using ODBC drivers, you can optimise many 'queries' that
are not handled by the pre-defined indexes. Using an SQL Engine gives you
far more power, but you have to drag yourself away from everything you are
used to and try to learn a new perspective on databases.

With DBF, we are almost down to the bare metal - we can see and touch (and
smell?) the database, see it's structure etc.

With SQL things ae very different. By design, SQL engines abstract the
storage (the disk file) from the logical table. There is at least one SQL
engine vendor I know of that uses manifest typing for data, which allows the
type of a data item to be associated with the data item not the column
(field) it is associate with. This means that any column can contain data of
any type, and that the same column may have different types in different
records. If at this moment you are gasping in horror at such outrageous
abuse of a table, then you are missing the point. Most SQL engines abstract
the storage, but this engine abstracts the entire concept of the 'database'
itself.

HTH

Regards,
-- Phil Ide

-------------------------------------------------------------
xbHCL (http://www.xbHCL.com) the xbase HTML command layer
PBIH (http://pbih.eu) Polar Bear International Hosting
-------------------------------------------------------------
Xbase++ FAQ, Libraries and Sources:
http://www.idep.org.uk/xbase

Just because.

6/7/2006 7:52 PM UTC

Re: Large datasets

Reply Quote

Mahanimann

Hi Boris, Phil and all other gurus,

Boris wrote:

However, I do not recomend browsing such a large result set.

Is it possible for you to explain exactly how you would do it if you had a table with 600.000 rows (and growing) and you needed to browse and search in all of them?

Boris also wrote:

I suggest that you redesign the UI and browse data in smaller chunks.

"Jimmy Malan" <fst-procurement@telkomsa.net> wrote in
news:8bdefi2fqy2se0u.050620061501@ANDREA:

> After reading through the newsgroup I saw a discussion where someone
> suggested using SqlSelect to create a cursor on the server side, but I
> don't think that is the answer in this case as everything was handled
> on one machine.

Even on one machine, there there is a server (PgSQL) and client (your
xbase++ app). If you really need to browse such a large result set, the
solution is to use SQLSelect with either a dynamic or keyset driven cursor.

However, I do not recomend browsing such a large result set. I suggest that
you redesign the UI and browse data in smaller chunks.

Best regards,
Boris Borzic
-- news://news.Xb2.NET
http://www.Xb2.NET
http://www.SQLExpress.net
industrial strength Xbase++ development tools

6/6/2006 10:44 AM UTC

Re: Large datasets

Reply Quote

Jimmy Malan

Hi Phil and Boris,

Thanks for the reply and explanation.

I am usiing the lastest PgSql Version 8.1.3. Downloaded from
www.postgresql.org

Being new to Sql it seems as if I am in for a steep learning curve. Have so
many questions and to compound matters there seems to be a scarcity of
examples from which to work.

I will scratch around in the Docs again and see what I can figure out.

Thanks again.

regards,

jimmy

6/8/2006 1:50 PM UTC

Re: Large datasets

Reply Quote

Boris Borzic

"Mahanimann" <paaldalen@gmail.com> wrote in
news:8ipl6qmnhk1qteu.070620062150@ANDREA:

The table in question contains items sold in a shop, and I need to
search for item text, ID, who sold this, date, time etc. With
"redesign the UI", do you suggest that the user must give the search
info before we do the SELECT command? No
"list-all-rows-then-start-filtering" like in Clipper, but the
opposite?

Yes exactly.

Best regards,
Boris Borzic
-- news://news.Xb2.NET
http://www.Xb2.NET
http://www.SQLExpress.net
industrial strength Xbase++ development tools