Board index » delphi » Rookie Question about TQuery and TDBGrid

Rookie Question about TQuery and TDBGrid

Hi Jay!

Select some fields from your table, don't load them all.
eg
with QueryGrid1 do begin
     Close;
     SQL.Clear;
     SQL.Add('Select Num, FirstName, LastName from "C:\..\myTable.dbf";');
     Open;
end;

with DataSource1 and QueryGrid1 as TDataSource and TQuery for your TDBGrid.
(Don't use order by if there is no index on it)
(but as Michael said: Query.Last takes time)

Rudy
===========================================

blacklungs schreef:

Quote

> Glatzfamly <glatzfa...@aol.comnospam>(?) ??? ?T????| > >
> > What the TQuery actually does in cases like that is it caches a certain
> number
> > of records and then, as you scroll down, asks for another cache from the
> DB
> > (you can see this take place if you start up the SQL Monitor before
> running
> > your app).  Try (and I can't test this for you right now because I don't
> have a
> > table that has that many records here at home) calling Query.Last then
> > Query.First after you open your Query to see if that will get all the
> records
> > into your Dataset quicker.

> I've tried calling Query.Last, and it indeed does bring all the data into
> the Dataset,but then the initial sequence ( i.e.  Query.Open then
> Query.Last, then Query.First ) takes a really long time.

> I don't know if I am asking too much, but I would really like a generally
> faster solution (It doesn't really have to be a Query+DBGrid combo) that is
> faster in getting all the records into the dataset, whether I do it when I
> first open the query or later when I scroll down the records.

> I just don't believe that it should take this long for me to get about 10000
> records.  (Don't ask me why I need to get all the records into a dbgrid.
> The enduser asked for it, and I couldn't convince otherwise)

> Jay. (1 AM Sunday morning... grumble)

 

Re:Rookie Question about TQuery and TDBGrid


Only 10000 records? I have tables with 180000 records, 86 fields and
I shows them all in approx. 1 a 2 seconds!
I also use TQuery, TDBGrid and call TQuery.Last. (less than 1 second)

Have you calculated fields? Do you use indexes? Is the paging good?
Do you use Prepare? Do you use order by in your SQL? ...
Paradox, dBase, InterBase, or what kind of database?
Show us your SQL (select ...)

I make weekly a backup + restore database (make them 10 times faster!)

By the startup I always search some records from a table,
after this, all the queries works very fast.

Rudy
============================================================
blacklungs schreef:

Quote

> > Select some fields from your table, don't load them all.
> But what if I need all the fields that I am selecting?

> What I am saying is that I can't change the dataset that I want to get.
> I am just wondering if there is a faster way to get the dataset.

> Maybe I should rephrase my original question:

> Task:
>     1. I need to select columns a, b, c, x, y, z from a table K.
>     2. The table has 10000 records, and I need to display ALL of them in a
> scrollable control.

> Problem:
>     1. The method of using TQuery and TDBGrid is too slow.
>     (if I execute TQuery.Last right after TQuery.Open, then it's too slow in
> the beginning, and if I don't it's slow when the user is scrolling/browsing
> the records)

> Question:
>     1. Find a solution that satisfies the Task at hand or find a way to make
> the current method faster.

Re:Rookie Question about TQuery and TDBGrid


On Sun, 2 May 1999 03:31:23 +0900, "blacklungs" <nom...@nomail.com>
wrote:

Quote
>> Select some fields from your table, don't load them all.
>But what if I need all the fields that I am selecting?

>What I am saying is that I can't change the dataset that I want to get.
>I am just wondering if there is a faster way to get the dataset.

>Maybe I should rephrase my original question:

>Task:
>    1. I need to select columns a, b, c, x, y, z from a table K.
>    2. The table has 10000 records, and I need to display ALL of them in a
>scrollable control.

>Problem:
>    1. The method of using TQuery and TDBGrid is too slow.
>    (if I execute TQuery.Last right after TQuery.Open, then it's too slow in
>the beginning, and if I don't it's slow when the user is scrolling/browsing
>the records)

>Question:
>    1. Find a solution that satisfies the Task at hand or find a way to make
>the current method faster.

There are several things you might try.  Each of these works well in
some cases, but none of them solve all problems.  The tradeoffs depend
on the database you're using and the nature of the data.

1. Use a TTable instead of a TQuery.  This often works well if you're
reading most of the columns of a table.  Not usable if you're trying to
order the results in a way that doesn't correspond to an index.

2. Use a TTable connected to a view defined on the database server.
Like method 1, but lets you limit the set of columns retrieved.  Works,
of course, only with a SQL-based database.

3. Use a TQuery which retrieves only the primary key from each record.
Add lookup or calculated fields for each field you want to display in
the grid, retrieving the data from another dataset pointing to the same
table.  The result can't be made editable in the grid, though if you use
calculated fields and position the auxiliary dataset in the OnCalcFields
event handler, you can hook single-record data-aware controls to it.  If
you need to search in the dataset, use a query or stored procedure to
retrieve a primary key for the record you're looking for, then a Locate
to reposition the keyset dataset.

I've often claimed that tables give better performance than queries with
SQL engines, but method 3 gives the best performance I've seen for
general browsing, if you're willing to deal with the extra complexity.

--
"Oh, shootings.  Yes, but that doesn't mean Americans are more {*word*268}
than other people.  We're just better shots."

Re:Rookie Question about TQuery and TDBGrid


Quote
> Select some fields from your table, don't load them all.

But what if I need all the fields that I am selecting?

What I am saying is that I can't change the dataset that I want to get.
I am just wondering if there is a faster way to get the dataset.

Maybe I should rephrase my original question:

Task:
    1. I need to select columns a, b, c, x, y, z from a table K.
    2. The table has 10000 records, and I need to display ALL of them in a
scrollable control.

Problem:
    1. The method of using TQuery and TDBGrid is too slow.
    (if I execute TQuery.Last right after TQuery.Open, then it's too slow in
the beginning, and if I don't it's slow when the user is scrolling/browsing
the records)

Question:
    1. Find a solution that satisfies the Task at hand or find a way to make
the current method faster.

Re:Rookie Question about TQuery and TDBGrid


On Sun, 2 May 1999 03:31:23 +0900, "blacklungs" <nom...@nomail.com>
wrote:

Quote

>Maybe I should rephrase my original question:

>Task:
>    1. I need to select columns a, b, c, x, y, z from a table K.
>    2. The table has 10000 records, and I need to display ALL of them in a
>scrollable control.

>Problem:
>    1. The method of using TQuery and TDBGrid is too slow.
>    (if I execute TQuery.Last right after TQuery.Open, then it's too slow in
>the beginning, and if I don't it's slow when the user is scrolling/browsing
>the records)

>Question:
>    1. Find a solution that satisfies the Task at hand or find a way to make
>the current method faster.

Maybe a silly question, but why use a TQuery at all ? Have you tried
connecting through a TTable ?  

Try connecting a DbGrid to a TTable that has a specific index
activated (i wouldn't rely on  field-name indexes) and see if the
performance is any better.

If this doesn't work you'll probably have to rethink the problem, does
the user really wants to navigate through 10k records with a
scrollbar?

NB: You haven't stated what sort of Database (Local, Interbase, ...)
you are using. Keeps us guessing a bit.

-hh-

-hh-

Re:Rookie Question about TQuery and TDBGrid


hi,

tquery is only a good way to find records not to display a great table in a
DBGrid (not SELECT * from XYZ--> that's like searching in a phone-book). If you
must give the wholy table to an DBGrid the first choice is TTable over to
TDataSource to TDBGrid.
If you must search use TQuery (if the user only gives an 'A' to search for give
him a Phone-Book), if you must display only without filtering data use TTABLE
TDATASOURCE.
Otherwise if the user wants to filter out 10000 records out of a table I think
SQL is not powerfull enough to do that. but there isn't a more powerfull tool
yet.
Or try to write the TQuery.First to Last in a seperate Table to display the
table later in the DBGrid.
Another way is to close all other functions that are using the table the dbgrid
when scrolling (like other calculating). Use index in rows you are using mostly.
when you are delete, insert, edit records index make the performance slower. for
that you can you can inactivate the index with:
Alter Index<IndexName> Inactive.

Hope I don't misunderstand the question an it helps.

Bye Dirk.

blacklungs schrieb:

Quote
> > Select some fields from your table, don't load them all.
> But what if I need all the fields that I am selecting?

> What I am saying is that I can't change the dataset that I want to get.
> I am just wondering if there is a faster way to get the dataset.

> Maybe I should rephrase my original question:

> Task:
>     1. I need to select columns a, b, c, x, y, z from a table K.
>     2. The table has 10000 records, and I need to display ALL of them in a
> scrollable control.

> Problem:
>     1. The method of using TQuery and TDBGrid is too slow.
>     (if I execute TQuery.Last right after TQuery.Open, then it's too slow in
> the beginning, and if I don't it's slow when the user is scrolling/browsing
> the records)

> Question:
>     1. Find a solution that satisfies the Task at hand or find a way to make
> the current method faster.

Re:Rookie Question about TQuery and TDBGrid


The table that I want to get is:
table Ledger
column             type               index     notnull
SERNUMH     NUMBER    YES      YES
SERNUMN     NUMBER    YES      YES
SNUM             NUMBER
I_DATE           DATE
I_TYPE           VARCHAR2
I_COUNT       NUMBER
USAGE           VARCHAR2

About 10000 records right now, expected to increase over the years in
approx. 1000 increments.

Quote
> Only 10000 records? I have tables with 180000 records, 86 fields and
> I shows them all in approx. 1 a 2 seconds!

That's my point exactly.  I am new to Delphi, and before, I used to have
apps that queried tables with record counts in the millions, not thousands,
and didn't have this kind of problem.

Quote
> Have you calculated fields? Do you use indexes? Is the paging good?
> Do you use Prepare? Do you use order by in your SQL? ...
> Paradox, dBase, InterBase, or what kind of database?
> Show us your SQL (select ...)

No calculated fields, (no lookups either), The first two columns are
indexed, and no order by's since the indexed order is what I want anyway.
(I would also like to know exactly what Prepare does.  Since Delphi tells me
that it automatically prepares and unprepares the query, and also since I
don't use this query repeatedly, I haven't used explicit Prepare/UnPrepare
calls.)

The database is Orace 7.3.2.0.0, remotely connected over a 56KBps frame
relay line, through a firewall.

Quote
> By the startup I always search some records from a table,
> after this, all the queries works very fast.

Yes, but can I make the initial search faster?

Vielen Dank,

Jay.

Re:Rookie Question about TQuery and TDBGrid


Quote
> tquery is only a good way to find records not to display a great table in
a
> DBGrid (not SELECT * from XYZ--> that's like searching in a phone-book).
If you
> must give the wholy table to an DBGrid the first choice is TTable over to
> TDataSource to TDBGrid.

The only reason why I used TQuery is because I wanted to incorporate
filtering and parameters later, when the need would surely arise, but I may
just end up using TTable if it's faster

Quote
> If you must search use TQuery (if the user only gives an 'A' to search for
give
> him a Phone-Book), if you must display only without filtering data use
TTABLE
> TDATASOURCE.

I personally think it's totally useless to be able to browse through 10000+
records, but that's what the user wants, because that's what he's doing now
with his printed ledger (flipping through pages and pages of a ledger).  I
blame myself for not being able to convince the user otherwise.

Quote
> Or try to write the TQuery.First to Last in a seperate Table to display
the
> table later in the DBGrid.

I don't understand what you're trying to say.

Quote
> Hope I don't misunderstand the question an it helps.

Don't worry, you didn't misunderstand the question.

Vielen Dank,

Jay.

Re:Rookie Question about TQuery and TDBGrid


On Mon, 3 May 1999 17:21:44 +0900, "blacklungs" <nom...@nomail.com>
wrote:

Quote
>The table that I want to get is:
>table Ledger
>column             type               index     notnull
>SERNUMH     NUMBER    YES      YES
>SERNUMN     NUMBER    YES      YES
>SNUM             NUMBER
>I_DATE           DATE
>I_TYPE           VARCHAR2
>I_COUNT       NUMBER
>USAGE           VARCHAR2

So let's optimistically say that's about 100 bytes/record.  I suppose
we should be counting some overhead for the fetch.

Quote
>About 10000 records right now, expected to increase over the years in
>approx. 1000 increments.

Ok, so now we have 100*10000 which should be about 1MB of data
travelling.

Quote
>The database is Orace 7.3.2.0.0, remotely connected over a 56KBps frame
>relay line, through a firewall.

Ok, so you were kidding after all :)  The people answering you have
good access speeds on *LAN*.  There is no way (Delphi or not) that you
will get that MB of data over your remote connection in less then a
minute or so.

I suggest you have a nice talk with your user and try to explain this
well to him/her.  Do your calculations correctly beforehand and show
your user some *facts*.

You could either provide for selection of small quantities of records
or (if they are always interested in those last records) reverse order
the query.  You should really be *glad* that BDE optimizes the data
fetch operations.

Regards,

--
Marco Rocci
MicroEra srl
Turin, Italy
-----------------
vota contro lo SPAM su: http://www.politik-digital.de/spam/

Re:Rookie Question about TQuery and TDBGrid


Quote
>The database is Orace 7.3.2.0.0, remotely connected over a 56KBps frame
>relay line, through a firewall.

See now that's much different.  You have got to convince your user of the
reality of the situation.  Have them go out on the internet and download the
equilvelent size file and see how long it takes, that might convince them to
see things your way.

********************************
Michael Glatz              
glatzfa...@aol.com
mgl...@briefcase.com
Accept that some days you're the pigeon,
some days you're the statue.

Re:Rookie Question about TQuery and TDBGrid


blacklungs schrieb:

Quote
> > tquery is only a good way to find records not to display a great table in
> a
> > DBGrid (not SELECT * from XYZ--> that's like searching in a phone-book).
> If you
> > must give the wholy table to an DBGrid the first choice is TTable over to
> > TDataSource to TDBGrid.

> The only reason why I used TQuery is because I wanted to incorporate
> filtering and parameters later, when the need would surely arise, but I may
> just end up using TTable if it's faster

> > If you must search use TQuery (if the user only gives an 'A' to search for
> give
> > him a Phone-Book), if you must display only without filtering data use
> TTABLE
> > TDATASOURCE.

> I personally think it's totally useless to be able to browse through 10000+
> records, but that's what the user wants, because that's what he's doing now
> with his printed ledger (flipping through pages and pages of a ledger).  I
> blame myself for not being able to convince the user otherwise.

> > Or try to write the TQuery.First to Last in a seperate Table to display
> the
> > table later in the DBGrid.

Hi,
OK, let's say: Save all found records in a new Table and display this table. it
needs a few seconds to save but scrolling might be faster, cause I think when
you are using the query to display the records it tries all the time to make new
searches to control if some records have changed (I don't really know). Then the
user can open this table later if he will look at the same properties or you can
TableX.Clear if you make a new search. If you don't really believe what I mean
try to scroll trough 10.000 records with TQuery and then with a TTable.

Bye Dirk.

- Show quoted text -

Quote

> I don't understand what you're trying to say.

> > Hope I don't misunderstand the question an it helps.

> Don't worry, you didn't misunderstand the question.

> Vielen Dank,

> Jay.

Re:Rookie Question about TQuery and TDBGrid


Sorry, it was late, and I kept omitting facts about my situation.  I really
shouldn't have dragged this thing along like this anyway.

What I am saying is that I know the with the current network setup the query
takes a long time.
But it's taking a lot longer than the estimated time that I calculated with
about 70 bytes a record (this being generous, actually) which is about 700K,
and calculates to around 100 seconds assuming 100% line usage.

Anyway, I finally made the user decide not to do it his way.

With a new query, the fetch operation is reduced to about 50 records!

Thanks for all the help, despite my mediocre explanation of the situation,
everybody.

regards,
Jay.

Quote
> See now that's much different.  You have got to convince your user of the
> reality of the situation.  Have them go out on the internet and download
the
> equilvelent size file and see how long it takes, that might convince them
to
> see things your way.

Re:Rookie Question about TQuery and TDBGrid


On Mon, 03 May 1999 19:16:39 +0100, "Dirk.Wendt"

Quote
<Dirk.We...@darmstadt.netsurf.de> wrote:
> OK, let's say: Save all found records in a new Table and display this table. it
> needs a few seconds to save but scrolling might be faster, cause I think when

I think this could be a good idea. In some cases I run the query
(supposed to have not too many records), make a batch move locally and
get the data for the grid with a SELECT * ... to which you can add any
ORDER BY or wathever the user wants to add at any time.

        Bye, Luca.

-----------------------------
Luca Manini                    
GIS and Database Developer    
ArcView and Delphi Programmer  
mail: man...@ies.it            
-----------------------------
Informatica e Servizi
via IV Novembre, 100
38014 Gardolo (Trento)
      Italy
tel:  +39 (461) 992 040
fax:  +39 (461) 991 666
www:  www.ies.it
-----------------------------

Other Threads