Board index » delphi » Why are sql queries created by Visual Query builder slower than same created by database desktop queries.

Why are sql queries created by Visual Query builder slower than same created by database desktop queries.

I'm not a veteran SQL programmer, so I use the Database Desktop to
construct queries
using their visual query designer. Once the query gives results to my
satisfaction I
display the resulting SQL code, cut and paster this code into the SQL
properties
of my TQuery object. It works, but instead of taking perhaps 2 seconds,
it takes
up to 40 minutes !

Why is this? They are both operating on identical tables from the same
database.
Here's my code:

Generated by Database desktop (after substitution of  "d","d1"..etc
table aliases
with more descriptive titles):

SELECT DISTINCT AL."AL NAME", FARE."FROM", FARE."TO", FARE.RULE_ID,
((FARE.OWMW + MARKUP."AGT OW $") / 0.9) AS FARE."CUS OWMW", ((FARE.OWWE
+ MARKUP."AGT OW $") / 0.9) AS FARE."CUS OWWE", ((FARE.RTMW+MARKUP."AGT
RT $") / 0.9) AS MARKUP."CUS RTMW", ((FARE.RTWE + MARKUP."AGT RT $") /
0.9) AS FARE."CUS RTWE"
FROM "C:\PDOXWIN\Pdoxdata\Markup.db" MARKUP,
"C:\PDOXWIN\Pdoxdata\Rule.db" RULE, "C:\PDOXWIN\Pdoxdata\Fare.db" FARE,
"C:\PDOXWIN\Pdoxdata\Cities.db" CITIES, "C:\PDOXWIN\Pdoxdata\Airline.db"

AIRLINE
WHERE
(RULE."MIN" <8)
 AND (RULE."MAX" >8)
 AND (FARE.DES = 'PAR')
 AND (FARE.ORG = MIA')
 AND (FARE."FROM" < '05/11/97')
 AND (FARE."TO" > '05/11/97')
 AND (FARE.RULE_ID = RULE.ID)
 AND (FARE.MARKUP_ID = MARKUP.ID)
 AND (CITIES.CITYCODE = FARE.DES)
 AND (AIRLINE.AL = FARE.AL)
ORDER BY FARE."FROM", FARE."TO", FARE.RULE_ID, AIRLINE."AL NAME"

Here's the version I put in my tQuery object.
SELECT DISTINCT AL."AL NAME", FARE."FROM", FARE."TO", FARE.RULE_ID,
((FARE.OWMW + MARKUP."AGT OW $") / 0.9) AS FARE."CUS OWMW", ((FARE.OWWE
+ MARKUP."AGT OW $") / 0.9) AS FARE."CUS OWWE", ((FARE.RTMW+MARKUP."AGT
RT $") / 0.9) AS MARKUP."CUS RTMW", ((FARE.RTWE + MARKUP."AGT RT $") /
0.9) AS FARE."CUS RTWE"
FROM "C:\PDOXWIN\Pdoxdata\Markup.db" MARKUP,
"C:\PDOXWIN\Pdoxdata\Rule.db" RULE, "C:\PDOXWIN\Pdoxdata\Fare.db" FARE,
"C:\PDOXWIN\Pdoxdata\Cities.db" CITIES, "C:\PDOXWIN\Pdoxdata\Airline.db"

AIRLINE
WHERE
(RULE."MIN" <:DURATION)
 AND (RULE."MAX" > :DURATION)
 AND (FARE.DES = :DES)
 AND (FARE.ORG = :ORG)
 AND (FARE."FROM" < :DEPDATE)
 AND (FARE."TO" > :DEPDATE)
 AND (FARE.RULE_ID = RULE.ID)
 AND (FARE.MARKUP_ID = MARKUP.ID)
 AND (CITIES.CITYCODE = FARE.DES)
 AND (AIRLINE.AL = FARE.AL)
ORDER BY FARE."FROM", FARE."TO", FARE.RULE_ID, AIRLINE."AL NAME"

As you can see only difference is substitution of run-time parameters
for
four constants. I suspect the answer to my question has something to do
with
indeces, but that's where my cluelessness begins.

I'd really appreciate any advice.

Serge d'Adesky
ser...@sprintmail.com

 

Re:Why are sql queries created by Visual Query builder slower than same created by database desktop queries.


An obvious guess is your app needs lots of memory to run this and when you
are running it from Delphi IDE under de{*word*81} it does not have it.
Indexes should work the same way in Desktop, should not they?
Sergei.

Serge d'Adesky <ser...@sprintmail.com> wrote in article
<344FCF82.48EDF...@sprintmail.com>...

Quote
> I'm not a veteran SQL programmer, so I use the Database Desktop to
> construct queries
> using their visual query designer. Once the query gives results to my
> satisfaction I
> display the resulting SQL code, cut and paster this code into the SQL
> properties
> of my TQuery object. It works, but instead of taking perhaps 2 seconds,
> it takes
> up to 40 minutes !

Re:Why are sql queries created by Visual Query builder slower than same created by database desktop queries.


Hi Sergei,

I thought they should run at same speed too. However it is not a question of
the
overload of memory from the IDE because same think happens if I compile
the Delphi program into an executable and run it.

I'm at a dead end. Any other thoughts?

Serge

Quote
Zhyzhyn S. wrote:
> An obvious guess is your app needs lots of memory to run this and when you
> are running it from Delphi IDE under de{*word*81} it does not have it.
> Indexes should work the same way in Desktop, should not they?
> Sergei.

> Serge d'Adesky <ser...@sprintmail.com> wrote in article
> <344FCF82.48EDF...@sprintmail.com>...
> > I'm not a veteran SQL programmer, so I use the Database Desktop to
> > construct queries
> > using their visual query designer. Once the query gives results to my
> > satisfaction I
> > display the resulting SQL code, cut and paster this code into the SQL
> > properties
> > of my TQuery object. It works, but instead of taking perhaps 2 seconds,
> > it takes
> > up to 40 minutes !

Other Threads