Board index » delphi » TQuery question..

TQuery question..

I have a large paradox file (50,000+ records) which have an associated site
ID. The user can select any number of sites to retrieve this data on. I need
a method to quickly determine if data exists for the site. Obviously doing a
'select * from site...' is inefficient and takes a lot of time. I have tried
using 'TOP 1' or 'SET ROWCOUNT' but I get told they are invalid keywords.

I am using Delphi 4. Are these keywords supported? If so, can someone post
an example on where the statement should be made. If not, any ideas on how
to accomplish this?

Thanks.

 

Re:TQuery question..


Use a dataset's Locate method.

Quote
Barkley <no_s...@yahoo.com> wrote in message

news:889gkg$i8h7@bornews.borland.com...
Quote
> I have a large paradox file (50,000+ records) which have an associated
site
> ID. The user can select any number of sites to retrieve this data on. I
need
> a method to quickly determine if data exists for the site. Obviously doing
a
> 'select * from site...' is inefficient and takes a lot of time. I have
tried
> using 'TOP 1' or 'SET ROWCOUNT' but I get told they are invalid keywords.

> I am using Delphi 4. Are these keywords supported? If so, can someone post
> an example on where the statement should be made. If not, any ideas on how
> to accomplish this?

> Thanks.

Re:TQuery question..


If you want fast performance use a TTable and FindKey or SetRange to find
the records. You will have to create an index on the field you are selecting
on but once you have the search will be much faster than a query.

--
Bill

Bill Todd (TeamB)
(TeamB cannot respond to questions received via email)

Re:TQuery question..


For archive reasons, I have seperated the data into seperate files based on
the month (ie. OH012000.db, OH022000.db, etc). To scan between these files,
I use a TSession to find all appropriate files and then use a SELECT and
UNION statements for my query. So to use a table, I would require placing
all the files into one large table and then doing this. Would it end up
being faster just to use a query?

Bill Todd (TeamB) <b...@dbginc.com> wrote in message
news:88ab2k$irq14@bornews.borland.com...

Quote
> If you want fast performance use a TTable and FindKey or SetRange to find
> the records. You will have to create an index on the field you are
selecting
> on but once you have the search will be much faster than a query.

> --
> Bill

> Bill Todd (TeamB)
> (TeamB cannot respond to questions received via email)

Re:TQuery question..


This worked perfectly and speeded things up a lot.

Thanks.

Jerry Bloomfield (TeamB) <JersW...@wwa.com> wrote in message
news:ojdhaso39n67a1bnq4tk4i7oao8st1l8jf@4ax.com...

Quote
> On Mon, 14 Feb 2000 13:21:09 -0500, "Barkley" <no_s...@yahoo.com> wrote:

> >I am using Delphi 4. Are these keywords supported? If so, can someone
post
> >an example on where the statement should be made. If not, any ideas on
how
> >to accomplish this?

> I like to use "SELECT COUNT(*) FROM tablename WHERE ..." to determine how
> many rows will be impacted by my statement...

> Depending upon the database and drivers you are using,  many drivers
> support a specific configuration setting to limit the number of rows
> returned...

> Jerry Bloomfield (TeamB)
> --
> Jerry Bloomfield                                     JersW...@wwa.com
> Proud Member of Borland's TeamB
> "We'll do anything if you don't pay us."

Re:TQuery question..


Quote
On Mon, 14 Feb 2000 13:21:09 -0500, "Barkley" <no_s...@yahoo.com> wrote:
>I have a large paradox file (50,000+ records) which have an associated site
>ID. The user can select any number of sites to retrieve this data on. I need
>a method to quickly determine if data exists for the site. Obviously doing a
>'select * from site...' is inefficient and takes a lot of time. I have tried
>using 'TOP 1' or 'SET ROWCOUNT' but I get told they are invalid keywords.

>I am using Delphi 4. Are these keywords supported? If so, can someone post
>an example on where the statement should be made. If not, any ideas on how
>to accomplish this?

No. Neither SQL-92 nor local SQL include such keywords. They must be
nonstandard additions to the SQL language specific to some SQL
implementation you worked with in the past.

Local SQL (the SQL implementation the BDE uses for dBASE, Paradox, and
FoxPro tables) is documented in the online help file LOCALSQL.HLP, found in
the main BDE directory. This help file is a language reference of the local
SQL implementation (subset) of SQL-92. The copy of this file that came with
BDE 4.x (and earlier versions) was seriously out of date and contained
little useful information. It has since been rewritten. It has been updated
again concurrent with C++Builder 5. Updated copies will have the topic
"VIEWs" in the index (and lack of this topic indicates a pre-update copy).
I can e-mail you a copy of the updated file if you need and desire it.

As others have pointed out, you will get far better performance results
doing that look-up using a TTable component and one of its index-based
search method.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Other Threads