![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
![]() |
Wed, Nov 18 2009 12:47 AM | Permanent Link |
Bryn Lewis | I have this query:
select * from mbs where Description_Record50 contains '*lap*' Description_Record50 is a CLOB with a text index: create text index Description_Record50X on MBS (Description_Record50) INDEXED WORD LENGTH 15 the MBS table has 8800 rows. 1. Inside ElevateDB Manager the select takes 0.5 to 0.9 secs. 2. When done as a TEDBQuery.open it takes 8-9 secs. Any clues on speeding up 1 and particularly 2. thanks. |
Wed, Nov 18 2009 3:55 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Bryn
Can you post the execution plans for both cases. Roy Lambert Roy Lambert [Team Elevate] |
Thu, Nov 19 2009 11:08 PM | Permanent Link |
Bryn Lewis | 1. As an elevateDB sql query:
SELECT ALL ..snip.. FROM "mbs" WHERE "Description_Record50" CONTAINS '*lap*' Source Tables ------------- mbs: 5760 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the mbs table: "Description_Record50" CONTAINS '*lap*' [Index scan (MBS.Description_Record50X): 84116 keys, 1265664 bytes estimated cost] Result set I/O statistics ------------------------- Total rows visited: 152 Row buffer manager Max buffer size: 329728 Buffer size: 195776 Hits: 152 Misses: 0 Hit ratio: 1 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 Index page buffer manager Max buffer size: 65536 Buffer size: 4096 Hits: 152 Misses: 0 Hit ratio: 1 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 BLOB block buffer manager Max buffer size: 32768 Buffer size: 79872 Hits: 156 Misses: 0 Hit ratio: 1 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 ================================================================================ 152 row(s) returned in 0.422 secs ================================================================================ 2. From a TEDBQuery: SELECT ALL ..snip.. FROM "mbs" WHERE "Description_Record50" CONTAINS '*lap*' Source Tables ------------- mbs: 5760 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the mbs table: "Description_Record50" CONTAINS '*lap*' [Index scan (MBS.Description_Record50X): 84116 keys, 1265664 bytes estimated cost] Result set I/O statistics ------------------------- Total rows visited: 152 Row buffer manager Max buffer size: 329728 Buffer size: 195776 Hits: 152 Misses: 0 Hit ratio: 1 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 Index page buffer manager Max buffer size: 65536 Buffer size: 4096 Hits: 152 Misses: 0 Hit ratio: 1 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 BLOB block buffer manager Max buffer size: 32768 Buffer size: 79872 Hits: 156 Misses: 0 Hit ratio: 1 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 ================================================================================ 152 row(s) returned in 8.937 secs ================================================================================ |
Fri, Nov 20 2009 4:50 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Bryn
They look pretty identical apart from the time so my guess would be external events / controls. In EDBManager there's going to be very limited interaction with any controls (only a fairly simple grid) whilst in your app there might (obviously I don't know) be lots of interaction. Try wrapping DisableControls / EnableControls around the query and/or setting any instances of it in a datasource to nil and see what happens. The only other possibility is that you're addressing a different database in each case or going via a different route (eg internet vs high speed LAN) A separate point CONTAINS '*lap*' should be CONTAINS 'lap*' unless Tim has sneaked in leading wildcards ![]() Roy Lambert [Team Elevate] |
Fri, Nov 20 2009 2:09 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Bryn,
Those execution plans are identical, but you snipped off the top that shows the version of EDB in use. Are you sure you're using the same version of EDB in both cases ? -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Nov 20 2009 2:11 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< A separate point CONTAINS '*lap*' should be CONTAINS 'lap*' unless Tim has sneaked in leading wildcards ![]() I most certainly did. ![]() I just looked, and they are not. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Nov 21 2009 3:04 AM | Permanent Link |
Bryn Lewis | I only have one version of EDB as I have only just started using it: ie, it is the same
version in both cases - the latest. The TEDBQuery.open is not connected to any other controls - it is created just before .open. |
Sat, Nov 21 2009 7:28 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
><< A separate point CONTAINS '*lap*' should be CONTAINS 'lap*' unless Tim >has sneaked in leading wildcards ![]() > >I most certainly did. ![]() >I just looked, and they are not. 1) great 2) oh bugger I have to update my textsearch code now ![]() Are you planning to build regex support into CONTAINS cos if so I'll start looking at that rather than just adjust for leading wildcard. Roy Lambert |
Sun, Nov 22 2009 10:27 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< Are you planning to build regex support into CONTAINS cos if so I'll start looking at that rather than just adjust for leading wildcard. >> Not any time soon, no. But, sometimes these things change. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Nov 22 2009 10:28 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Bryn,
<< I only have one version of EDB as I have only just started using it: ie, it is the same version in both cases - the latest. The TEDBQuery.open is not connected to any other controls - it is created just before .open. >> Can you distill this down to a sample project that replicates the slowdown ? If so, please email it to me at timyoung@elevatesoft.com Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |