Board index » delphi » Please help with using ORDER BY in TQuery SQL

jlow...@ucs.usc.edu (Fritz Lowrey)

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Please help with using ORDER BY in TQuery SQL

        Folks,
        I really want to be either able to specify the desired index or
use an ORDER BY clause in my SQL statements of a TQuery.  The problem
is that I am accessing Paradox files, and one of the rules for these
files is that you can't do a RequestLive retrieval if you are using
ORDER BY, despite it being valid Local SQL..  Is there anything I can
do short of going to the less-portable TTable option?

                                                        Fritz

--
Fritz Lowrey                          jlow...@ucs.usc.edu
USC Computer Services                 Phone: 213 740-5160

 
Joseph P DeCello I

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Please help with using ORDER BY in TQuery SQL


Quote
In article <3v7sra$...@usc.edu>, jlow...@ucs.usc.edu (Fritz Lowrey) writes:
>    Folks,
>    I really want to be either able to specify the desired index or
>use an ORDER BY clause in my SQL statements of a TQuery.  The problem
>is that I am accessing Paradox files, and one of the rules for these
>files is that you can't do a RequestLive retrieval if you are using
>ORDER BY, despite it being valid Local SQL..  Is there anything I can
>do short of going to the less-portable TTable option?

>                                                    Fritz

>--
>Fritz Lowrey                          jlow...@ucs.usc.edu
>USC Computer Services                 Phone: 213 740-5160

I have the EXACT same need, how do I set the index from SQL or how
to edit when using ordered by, but I'm using dbase tables.

HELP!!!

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Joseph P. DeCello III      | Opinions expressed here are mine alone and
Computer Coordinator       | do not reflect those of any individual or
Michigan State University  | department at Michigan State University.

Steve Koters

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Please help with using ORDER BY in TQuery SQL


Quote
Fritz Lowrey (jlow...@ucs.usc.edu) wrote:

:       Folks,
:       I really want to be either able to specify the desired index or
: use an ORDER BY clause in my SQL statements of a TQuery.  The problem
: is that I am accessing Paradox files, and one of the rules for these
: files is that you can't do a RequestLive retrieval if you are using
: ORDER BY, despite it being valid Local SQL..  Is there anything I can
: do short of going to the less-portable TTable option?

I am afraid that you cannot use indexes in conjunction with SQL applied
against local tables (dBASE or Paradox). Further, an SQL statement that
includes an ORDER BY clause will result in a read-only data set (the Can-
Modify property will be False).

Indexes can be used in SQL queries against Local InterBase Server (LIBS)
tables or remote SQL tables. And using an ORDER BY clause in an SQL
statement with LIBS or remote tables will not result in a read-only
data set.

--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski               _/   The opinions expressed here are    _/
_/ koter...@borland.com         _/         exclusively my own           _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Gabriel Beccar-Varel

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Please help with using ORDER BY in TQuery SQL


dece...@cpp.msu.edu (Joseph P DeCello III) wrote:

Quote
>In article <3v7sra$...@usc.edu>, jlow...@ucs.usc.edu (Fritz Lowrey) writes:
>>        Folks,
>>        I really want to be either able to specify the desired index or
>>use an ORDER BY clause in my SQL statements of a TQuery.  The problem
>>is that I am accessing Paradox files, and one of the rules for these
>>files is that you can't do a RequestLive retrieval if you are using
>>ORDER BY, despite it being valid Local SQL..  Is there anything I can
>>do short of going to the less-portable TTable option?

>I have the EXACT same need, how do I set the index from SQL or how
>to edit when using ordered by, but I'm using dbase tables.

I hope Steve K. answers this one because we are many having the same problem. One idea I
had, although I have not try it, is to get the key fields of the record you want to update
and then use a separate TQuery with an UPDATE statement.

--
Gabriel Beccar-Varela
E-Mail gabr...@emf.net

---

Hallvard Vassbo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Please help with using ORDER BY in TQuery SQL


In <3v9ofp$...@emf.emf.net> Gabriel Beccar-Varela <gabr...@emf.net> writes:

Quote
>dece...@cpp.msu.edu (Joseph P DeCello III) wrote:
>>In article <3v7sra$...@usc.edu>, jlow...@ucs.usc.edu (Fritz Lowrey) writes:
>>>    Folks,
>>>    I really want to be either able to specify the desired index or
>>>use an ORDER BY clause in my SQL statements of a TQuery.  The problem
>>>is that I am accessing Paradox files, and one of the rules for these
>>>files is that you can't do a RequestLive retrieval if you are using
>>>ORDER BY, despite it being valid Local SQL..  Is there anything I can
>>>do short of going to the less-portable TTable option?

>>I have the EXACT same need, how do I set the index from SQL or how
>>to edit when using ordered by, but I'm using dbase tables.

>I hope Steve K. answers this one because we are many having the same problem. One idea I
>had, although I have not try it, is to get the key fields of the record you want to update
>and then use a separate TQuery with an UPDATE statement.

Yes, that would probably work, but you would have to refresh the original
query - something that will take quite a bit of time (depending on the size
of the table).

Unfortunately, the time it takes to process a query increases dramatically
as the size of the table increases. Often the query could have been optimized
by limiting it to a range within an index, but the SQL engine doesn't use
any indexes at all - always processing the entire table.

Borland, here are some wishes for improvements that would speed up queries:
  1. Make the Local SQL Engine smarter. Let it analyse the SQL query and
     find out if it can use any of the existing indexes to set a range
     and thus speed up the query.

  2. If the above is not possible, add properties to the TQuery component
     to get the records to be queried from a TDataSource. This
     datasource could be connected to a TTable that had a range already
     set up.

  3. If the above is not possible (because it might not be supported on
     SQL databases), write a techniqual document describing how you can
     accompish the same thing with direct calls to BDE.

In my program, I'm dealing with tables of 30-40 Mb. A sample query only
needs to access about 10000 records (one date), but the Local SQL Engine
always access the entire table (20 dates, 200000 records). Instead of
taking 20 seconds, it is taking 10-15 minutes!

I have been thinking about copying the range I need to a temporary table
and then perform the query on that table, but it shouldn't really be
neccesarry to do this (and I don't know if I will gain much speed from it).

--
 Hallvard Vassbotn  | Falcon AS (a Reuters company) | Without programmers,
 hallv...@falcon.no | Stranden 1, 0250 OSLO, Norway | the world would stop!

Other Threads

1. Refresh problem using TQuery with SQL Server - SOS PLEASE HELP ME

2. !! Urgent Help Please Using Tquery

3. SQL Help, using the Params value of an TQuery Dynamically - D3

4. TQUERY-SQL-Order by...

5. Delphi TQuery and Order By sql command

6. Local SQL cant edit in DBGRID after ORDER BY in a TQuery

7. Delphi TQuery and Order By sql command

8. PLEASE HELP --- Using ActiveX within D3 ---- PLEASE HELP

9. HELP please, problems using Delphi 7 E with SQL server

10. TTcpServer please help me oh, PLEASE PLEASE PLEASE HELP ME