Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 11 total |
Optimize TEXTSEARCH |
Wed, Nov 29 2006 2:41 AM | Permanent Link |
Tom | Hi,
Is it possible to optimize SQL below? Because it is prety slow on table with 7000 records, takes about 7 seconds. SELECT * FROM Topic Where TEXTSEARCH('12775' IN MyKeywords) or TEXTSEARCH('17737' IN MyKeywords) or TEXTSEARCH('10777' IN MyKeywords) or TEXTSEARCH('11362' IN MyKeywords) or TEXTSEARCH('27171' IN MyKeywords) or TEXTSEARCH ('14465' IN MyKeywords) or TEXTSEARCH('15749' IN MyKeywords) or TEXTSEARCH('23058' IN MyKeywords) or TEXTSEARCH('14013' IN MyKeywords) or TEXTSEARCH('30873' IN MyKeywords) or TEXTSEARCH('18772' IN MyKeywords) or TEXTSEARCH ('13157' IN MyKeywords) or TEXTSEARCH('10304' IN MyKeywords) or TEXTSEARCH('10277' IN MyKeywords) or TEXTSEARCH('15432' IN MyKeywords) or TEXTSEARCH('11662' IN MyKeywords) or TEXTSEARCH('17338' IN MyKeywords) or TEXTSEARCH ('30606' IN MyKeywords) or TEXTSEARCH('15229' IN MyKeywords) Regards, Tomas |
Wed, Nov 29 2006 12:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Tomas,
<< Is it possible to optimize SQL below? Because it is prety slow on table with 7000 records, takes about 7 seconds. >> Could you post the query plan for the query ? -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Nov 29 2006 3:28 PM | Permanent Link |
Tom | Plan is below, strange thing is that it shows execution time as 1,484 seconds, but it takes much longer than two seconds.
================================================================================ SQL statement (Executed with 4.22 Build 6) ================================================================================ SELECT * FROM Topic Where TEXTSEARCH('17977' IN CategoriesID) or TEXTSEARCH('28239' IN CategoriesID) or TEXTSEARCH('10995' IN CategoriesID) or TEXTSEARCH('14132' IN CategoriesID) or TEXTSEARCH('22223' IN CategoriesID) or TEXTSEARCH('19196' IN CategoriesID) or TEXTSEARCH('12812' IN CategoriesID) or TEXTSEARCH('15763' IN CategoriesID) or TEXTSEARCH('15962' IN CategoriesID) or ....<SKIP> Tables Involved --------------- Topic (Topic) table opened shared, has 1268 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows WHERE Clause Execution ---------------------- NOTE: Optimized TEXTSEARCH() conditions do not use statistics and will show zero (0) for any I/O costs The expression: TEXTSEARCH('17977',CategoriesID) = TRUE or TEXTSEARCH('28239',CategoriesID) = TRUE or TEXTSEARCH('10995',CategoriesID) = TRUE or TEXTSEARCH('14132', CategoriesID) = TRUE or TEXTSEARCH('22223',CategoriesID) = TRUE or TEXTSEARCH('19196',CategoriesID) = TRUE or ....<SKIP> is OPTIMIZED, covers 0 rows or index keys, costs 0 bytes, and will be applied to the Topic table (Topic) before any joins ================================================================================ >>>>> 1268 rows affected in 1,484 seconds ================================================================================ |
Thu, Nov 30 2006 3:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tom
Since its canned my guess would be that a large chunk of the time is involved in creating and populating the temporary table. Try setting RequestLive to True. Roy Lambert |
Thu, Nov 30 2006 4:17 AM | Permanent Link |
Tom | The difference is minimal between canned and live queries - Seven seconds on
Canned and Six on Live. |
Thu, Nov 30 2006 2:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Tom,
<< Plan is below, strange thing is that it shows execution time as 1,484 seconds, but it takes much longer than two seconds. >> In that case, the rest of the time is being spent elsewhere in your code. Do you have any AfterOpen events or anything similar defined for the TDBISAMQuery that is executing the query ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Nov 30 2006 2:37 PM | Permanent Link |
Tom | I have tested this code in DBISAM utility.
|
Thu, Nov 30 2006 2:39 PM | Permanent Link |
Tom | I have tested this code in DBISAM utility and it shows 1,484
seconds but takes much longer about 6-7 seconds. |
Fri, Dec 1 2006 1:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Tom,
<< I have tested this code in DBISAM utility and it shows 1,484 seconds but takes much longer about 6-7 seconds. >> And just to verify - it is generating a canned result set (not live) in these cases ? -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Dec 1 2006 2:49 PM | Permanent Link |
Tom | I tried both canned and live, there is no big difference in execution time.
|
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |