Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
query slow on some computers |
Mon, Nov 3 2008 3:26 AM | Permanent Link |
"ANdre" | 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 |
Tue, Nov 4 2008 5:51 AM | Permanent Link |
"Frans van Daalen" | "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 |
Tue, Nov 4 2008 11:03 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Fri, Nov 7 2008 5:15 AM | Permanent Link |
"ANdre" | 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 > |
Fri, Nov 7 2008 6:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Nov 10 2008 5:09 AM | Permanent Link |
"ANdre" | 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] > |
Mon, Nov 10 2008 5:41 AM | Permanent Link |
"ANdre" | 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] >> > > |
Mon, Nov 10 2008 7:50 AM | Permanent Link |
Roy Lambert NLH Associates 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] |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |