Board index » delphi » TQuery.SQL or TQuery.Filter: Is one better ?

TQuery.SQL or TQuery.Filter: Is one better ?

When it is possible to limit (non-live) data retrieval by using either a
filter or an SQL statement, is one more accepted ?

ie SQL:
Select * from Customer,Orders
where Customer.CustNo=Orders.CustNo
=> and Customer.Country = "US"  <==

Filter:
==> 'Country=US' <==

It seems that cbGENPROGRESS callbacks work more reliably with the
filter...

Thanks in advance,

Monte Etherton
automated concepts

 

Re:TQuery.SQL or TQuery.Filter: Is one better ?


Doing it in Sql is always cleaner.  Your dataset only contains the records
that you request.  If you get a table that starts to get a little large,
filtering can take a long time whereas if you have indexes set up correctly
on your table then the Sql statement is quicker because it doesn't have to
do a full table scan to come up with the right records.  As I understand it,
filtering has to go through every record to see if the filter matches the
record.  This can take a long time if you have, as I did in one table, over
100,000 records.

--
Michael Glatz
mgl...@briefcase.com

Quote
Monte Etherton wrote in message <35FD3E1F.CE1BC...@ibm.net>...
>When it is possible to limit (non-live) data retrieval by using either a
>filter or an SQL statement, is one more accepted ?

>ie SQL:
>Select * from Customer,Orders
>where Customer.CustNo=Orders.CustNo
>=> and Customer.Country = "US"  <==

>Filter:
>==> 'Country=US' <==

>It seems that cbGENPROGRESS callbacks work more reliably with the
>filter...

>Thanks in advance,

>Monte Etherton
>automated concepts

Re:TQuery.SQL or TQuery.Filter: Is one better ?


 Well, I just did some time trials.  My master table (Jobs) is 7,259 records.
The detail table (Repairlines) is 128,844
records.  When I query my current Jobs (only about 35 records) for certain
Repairline text using SQL (ie 'and Jobs=Current'), the query takes about 14
seconds, and I get no cbGENPROGRESS callbacks ( because of the small number of
records being searched ?).
  If I use a filter (ie Jobs=Current) instead of SQL, then the same query takes
under 7 seconds, and I get callbacks.

Make any sense ?  Anyone else want to chime in ?

Monte Etherton
automated concepts

Quote
Michael Glatz wrote:
> Doing it in Sql is always cleaner.  Your dataset only contains the records
> that you request.  If you get a table that starts to get a little large,
> filtering can take a long time whereas if you have indexes set up correctly
> on your table then the Sql statement is quicker because it doesn't have to
> do a full table scan to come up with the right records.  As I understand it,
> filtering has to go through every record to see if the filter matches the
> record.  This can take a long time if you have, as I did in one table, over
> 100,000 records.

> --
> Michael Glatz
> mgl...@briefcase.com
> Monte Etherton wrote in message <35FD3E1F.CE1BC...@ibm.net>...
> >When it is possible to limit (non-live) data retrieval by using either a
> >filter or an SQL statement, is one more accepted ?

> >ie SQL:
> >Select * from Customer,Orders
> >where Customer.CustNo=Orders.CustNo
> >=> and Customer.Country = "US"  <==

> >Filter:
> >==> 'Country=US' <==

> >It seems that cbGENPROGRESS callbacks work more reliably with the
> >filter...

> >Thanks in advance,

> >Monte Etherton
> >automated concepts

Re:TQuery.SQL or TQuery.Filter: Is one better ?


Always use SQL.  If you apply a filter to a query result your app must read
every record in the query result set from the server to find the records
that pass the filter.

--
Bill Todd
(Sorry but TeamB cannot answer questions received via email)
(Remove nospam from my email address to contact me for any other reason)

Other Threads