Hi.,

I use Delphi 6, dbisam client/server 4.24 build 1.

I have a very simpel query, like
select [Number],[CD Code] as [CD],[Local Customer Name],[Pieces],[Gross
Weight],[Volume Weight],[Volume]
from shipment
where [Main File]='248723987'

On the shipment table there is an index on the field Main File.

When I run this query on the server (4GB, 4 x xeon cpu @ 1.8Mhz) of one of
our customers, this query takes about 8 seconds. The Shipment Table is about
10.000 records.
When I run the query on the same table on my own computer this query takes
0.102 secondes.

Any ideas what is cause the delay.

Thanks
Andre

"ANdre" <andre.nospam@cds-nl.com> wrote in message
news:6077D0B9-7CB7-4197-A827-5DEFD46CF499@news.elevatesoft.com...
> Hi.,
>
> I use Delphi 6, dbisam client/server 4.24 build 1.
>
> I have a very simpel query, like
> select [Number],[CD Code] as [CD],[Local Customer Name],[Pieces],[Gross
> Weight],[Volume Weight],[Volume]
> from shipment
> where [Main File]='248723987'
>
> On the shipment table there is an index on the field Main File.
>
> When I run this query on the server (4GB, 4 x xeon cpu @ 1.8Mhz) of one of
> our customers, this query takes about 8 seconds. The Shipment Table is
> about 10.000 records.
> When I run the query on the same table on my own computer this query takes
> 0.102 secondes.
>
> Any ideas what is cause the delay.
>
Maybe comparing the execution plan will help to find the problem? I think it
might be a corrupt index, you already tried to repair the table ?

Frans

Andre,

<< When I run this query on the server (4GB, 4 x xeon cpu @ 1.8Mhz) of one
of our customers, this query takes about 8 seconds. The Shipment Table is
about 10.000 records.When I run the query on the same table on my own
computer this query takes 0.102 secondes. >>

Frans is correct.  You'll want to compare the execution plans first to
ensure that both installations are using the exact same table structures
(especially indexes).  If there aren't any differences, then verify all
involved tables (TDBISAMTable.VerifyTable or VERIFY TABLE SQL statement) to
ensure that there isn't any corruption.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim and Frans.

Thanks for you rreplies.
The database are exactly the same.
I have put the customer database on my pc. I have not done any repairs or
verifies.
So I work with the same tables (and with the same corruptions if any).

What to do you mean with execution plans.

Andre


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:6A035163-C471-46BB-A772-20B948F9949C@news.elevatesoft.com...
> Andre,
>
> << When I run this query on the server (4GB, 4 x xeon cpu @ 1.8Mhz) of one
> of our customers, this query takes about 8 seconds. The Shipment Table is
> about 10.000 records.When I run the query on the same table on my own
> computer this query takes 0.102 secondes. >>
>
> Frans is correct.  You'll want to compare the execution plans first to
> ensure that both installations are using the exact same table structures
> (especially indexes).  If there aren't any differences, then verify all
> involved tables (TDBISAMTable.VerifyTable or VERIFY TABLE SQL statement)
> to ensure that there isn't any corruption.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

ANdre


If you run the query in DBSys you'll see a checkbox "Generate Plan" tick this and you'll get an extra page showing the execution plan.

If run in the app set the query's property GeneratePlan to true then after its run the execution plan is in the TStrings property plan - you can save to file or view in a memo.

Roy Lambert [Team Elevate]

Thanks Roy,

I used the execution plan. See below.
It tells me the query is un-optimized and that is strange. There is an
secundary index on the fields "Main File;Number".
I have tried to make an index on on the field Main File. But then it still
stays on un-optimized.

I have checked other queries I use and these query are all optimized.

It is a very simple and straight forward query.

Any idea?

Thanks

Andre




10-nov-2008 11:01:14:738 --<START OF EXECUTION PLAN>--
10-nov-2008 11:01:14:738
================================================================================
10-nov-2008 11:01:14:738 SQL statement (Executed with 4.24 Build 1)
10-nov-2008 11:01:14:738
================================================================================
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 select [Number],[CD Code] as [CD],[Local Customer
Name],[Pieces],[Gross Weight],
10-nov-2008 11:01:14:738 [Volume Weight],[Volume] from shipment where [Main
File]='200810000234'
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 Tables Involved
10-nov-2008 11:01:14:738 ---------------
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 shipment (shipment) table opened shared, has 188
rows
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 Result Set Generation
10-nov-2008 11:01:14:738 ---------------------
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 Result set will be canned
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 Result set will consist of one or more rows
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 WHERE Clause Execution
10-nov-2008 11:01:14:738 ----------------------
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 The expression:
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 [Main File] = '200810000234'
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738 is UN-OPTIMIZED and will be applied to each
candidate row in the shipment table
10-nov-2008 11:01:14:738 (shipment) as the result set is generated
10-nov-2008 11:01:14:738
10-nov-2008 11:01:14:738
================================================================================
10-nov-2008 11:01:14:738 >>>>> 0 rows affected in 0.016 seconds
10-nov-2008 11:01:14:738
================================================================================
10-nov-2008 11:01:14:738 --<END OF EXECUTION PLAN>--
"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:C931AA2F-D54F-4E4A-AE46-F4EA09D5A61C@news.elevatesoft.com...
> ANdre
>
>
> If you run the query in DBSys you'll see a checkbox "Generate Plan" tick
> this and you'll get an extra page showing the execution plan.
>
> If run in the app set the query's property GeneratePlan to true then after
> its run the execution plan is in the TStrings property plan - you can save
> to file or view in a memo.
>
> Roy Lambert [Team Elevate]
>

Hi Roy,

Found it. Optimization only works on Case-Sensitive indexes. And my index
was insensitive.
Now the query is optimized and hopefully quicker.

Thanks

Andre

"ANdre" <andre.nospam@cds-nl.com> schreef in bericht
news:009FB80E-963F-4F51-B742-13BDEEE08520@news.elevatesoft.com...
> Thanks Roy,
>
> I used the execution plan. See below.
> It tells me the query is un-optimized and that is strange. There is an
> secundary index on the fields "Main File;Number".
> I have tried to make an index on on the field Main File. But then it still
> stays on un-optimized.
>
> I have checked other queries I use and these query are all optimized.
>
> It is a very simple and straight forward query.
>
> Any idea?
>
> Thanks
>
> Andre
>
>
>
>
> 10-nov-2008 11:01:14:738 --<START OF EXECUTION PLAN>--
> 10-nov-2008 11:01:14:738
> ================================================================================
> 10-nov-2008 11:01:14:738 SQL statement (Executed with 4.24 Build 1)
> 10-nov-2008 11:01:14:738
> ================================================================================
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 select [Number],[CD Code] as [CD],[Local Customer
> Name],[Pieces],[Gross Weight],
> 10-nov-2008 11:01:14:738 [Volume Weight],[Volume] from shipment where
> [Main File]='200810000234'
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 Tables Involved
> 10-nov-2008 11:01:14:738 ---------------
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 shipment (shipment) table opened shared, has 188
> rows
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 Result Set Generation
> 10-nov-2008 11:01:14:738 ---------------------
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 Result set will be canned
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 Result set will consist of one or more rows
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 WHERE Clause Execution
> 10-nov-2008 11:01:14:738 ----------------------
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 The expression:
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 [Main File] = '200810000234'
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738 is UN-OPTIMIZED and will be applied to each
> candidate row in the shipment table
> 10-nov-2008 11:01:14:738 (shipment) as the result set is generated
> 10-nov-2008 11:01:14:738
> 10-nov-2008 11:01:14:738
> ================================================================================
> 10-nov-2008 11:01:14:738 >>>>> 0 rows affected in 0.016 seconds
> 10-nov-2008 11:01:14:738
> ================================================================================
> 10-nov-2008 11:01:14:738 --<END OF EXECUTION PLAN>--
> "Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
> news:C931AA2F-D54F-4E4A-AE46-F4EA09D5A61C@news.elevatesoft.com...
>> ANdre
>>
>>
>> If you run the query in DBSys you'll see a checkbox "Generate Plan" tick
>> this and you'll get an extra page showing the execution plan.
>>
>> If run in the app set the query's property GeneratePlan to true then
>> after its run the execution plan is in the TStrings property plan - you
>> can save to file or view in a memo.
>>
>> Roy Lambert [Team Elevate]
>>
>
>

ANdre



For it to work on insensitive indices you need to wrap both sides with either UPPER or LOWER eg

UPPER([Main File]) = UPPER('200810000234')


Roy Lambert [Team Elevate]