Board index » delphi » Results of BETWEEN

Results of BETWEEN

Following Bills suggestion - test 24000 records - result approx 500 records

recall between "X" and "Z"   with index on recall time to open 4 secs  ;  no
index on recall 1 min 27 secs
recall like "Y"                                              "          "
1min 23 secs    ;       "           1 min 22 secs

Quite a difference but it still leaves me with an extra index.
This raises an extra point - I had no idea that between would make so much
difference over the like statement - are there any other similar performance
improving/degrading statements?????

Regards

Philip L Jackson

"Philip L Jackson" <phi...@pcdata.co.uk> wrote in message
news:39ff3c3d_2@dnews...

Quote
> I am having performance problems with a customer and any advice would be
> appreciated.

> I performn an SQL Query

> select contract, jobno,recall, special_inst              //
> contract(String9), jobno(integer0), recall(string1),special_inst(blob)
> from jobsheet
> where recall like Y

> request live is set to FALSE

> The paradox table - jobsheet.db contains approx 20000 records, the
resultant
> query return approx 6 records.

> If this is run on the server then the result takes a few seconds.
> If this is run on a workstation, over a 10Tbase network, the query takes 1
> min 10 seconds.

> The workstation is a PII machine with 64MB ram.

> Any ideas  on how to speed this up?  as a holding measure I have put the
> query in a background thread but I would prefer to solve the problem.

> Regards

> Philip L Jackson

 

Re:Results of BETWEEN


There is no good answer to that question because query behavior varies from
database to database. With Paradox tables the query processor is not vary
smart. It will use the primary index and single field case sensitive
secondary indices when the comparison is an exact match or a range.

--
Bill

Re:Results of BETWEEN


In this case, the problem is not likely to be "locating the job-sheets
that have RECALL like 'Y'."  A simple index will find those instantly.
The problem is likely to be "special_inst(blob)."  Many query processors
have a lot of problems with blobs and send too much information down the
wire.  Try omitting special_inst from the query and see how the query
improves.

Quote
> "Philip L Jackson" <phi...@pcdata.co.uk> wrote:
> > I performn an SQL Query

> > select contract, jobno,recall, special_inst              //
> > contract(String9), jobno(integer0), recall(string1),special_inst(blob)
> > from jobsheet
> > where recall like Y

> > If this is run on the server then the result takes a few seconds.
> > If this is run on a workstation, over a 10Tbase network, the query takes 1
> > min 10 seconds.

------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:i...@sundialservices.com  (PGP public key available.)
Quote
> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/products/chimneysweep

Re:Results of BETWEEN


| This raises an extra point - I had no idea that between would make so much
| difference over the like statement - are there any other similar
performance
| improving/degrading statements?????

We've also seen improvements using
where rather than between in specific with date ranges.

If performance is an issue - trial and error can help you determine better
approaches. Not always initially apparent and sometimes not even
necessary logical.

Garry Kernan

Other Threads