Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
EDB 2 : Sql optimize and CaseInsensitive |
Mon, Nov 10 2008 7:06 AM | Permanent Link |
"Mauro Botta" | Hi
In my Application ( not DB structure , my application ) all string fields are Case Insensitive. for my all string sono upper. now... in db structure... i have all indexes ( string ) set CASE-INS = True ( ASC-ANSI-CASEINS ) for active the index optimize when i execute a query like this SELECT * FROM MYCITY WHERE CITY = 'LAS VEGAS' ( INDEX : CITY;STREET ) what i must do for to have a fast query result ? |
Mon, Nov 10 2008 8:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mauro
What does the execution plan say? Roy Lambert [Team Elevate] |
Mon, Nov 10 2008 8:45 AM | Permanent Link |
"Mauro Botta" | > Roy Lambert [Team Elevate]
my table have : 82'675 records. ================================================================================ SQL Query (Executed by ElevateDB 2.02 Build 3) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "CAP"."PROVINCIA" AS "PROVINCIA", "CAP"."COMUNE" AS "COMUNE", "CAP"."CAP" AS "CAP", "CAP"."TIPO" AS "TIPO", "CAP"."VIA" AS "VIA", "CAP"."NUMERI CIVICI" AS "NUMERI CIVICI" FROM "CAP" WHERE "COMUNE" = 'LOS ANGELES' Source Tables ------------- CAP: 82675 rows Result Set ---------- The result set was sensitive The result set consisted of zero or more rows The result set was ordered using the table's natural order Filtering --------- The following filter condition was applied to the Query table: "COMUNE" = 'LOS ANGELES' [Row scan (cap): 82675 rows, 16535000 bytes estimated cost] ================================================================================ 510 row(s) returned in 8,127 secs ================================================================================ |
Mon, Nov 10 2008 8:57 AM | Permanent Link |
"Mauro Botta" | I have all indexes with Case Insensitive set on , but this is not enough for
turn on optimize engine the query scan all records. |
Mon, Nov 10 2008 9:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mauro
Two things: 1. Can you post the column definitions for the table as well please. 2. What happens if you specify the collation eg "COMUNE" collate ansi_ci = 'LOS ANGELES' Roy Lambert [Team Elevate] |
Mon, Nov 10 2008 9:45 AM | Permanent Link |
"Mauro Botta" | "Roy Lambert" <roy.lambert@skynet.co.uk> ha scritto nel messaggio news:1247EBCA-188B-4C0C-A1EC-F3A03102A459@news.elevatesoft.com... > Mauro > > Two things: > > > 1. Can you post the column definitions for the table as well please. not easy with edb... my table cap is : 6 FIELDS VARCHAR - ANSI - NULLABLE = TRUE - GENERATED = NO - COMPUTED = NO > 2. What happens if you specify the collation eg > "COMUNE" collate ansi_ci = 'LOS ANGELES' SELECT * FROM CAP WHERE COMUNE collate ansi_ci = 'LOS ANGELES' work well ! - 1 second for query PLAN : Filtering --------- The following filter condition was applied to the Query table: "COMUNE" COLLATE "ansi_ci" = 'VERBANIA' [Index scan (cap.COMUINDI):, 160 keys, 28672 bytes estimated cost] -------------------- scan only 160 records. not more all db. Question : If i convert all Fields string in : from ANSI to ANSI_CI for have fast query , are there any problems ? |
Mon, Nov 10 2008 9:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mauro
Only that the fields are case insensitive so 'Los Angeles' would be the same as 'LOS ANGELES'. I've set all my VARCHAR fields to case insensitive. Roy Lambert [Team Elevate] |
Mon, Nov 10 2008 10:18 AM | Permanent Link |
"Mauro Botta" | >I've set all my VARCHAR fields to case insensitive.
Thank Roy |
Mon, Nov 10 2008 1:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Mauro,
You should check out this technical article ("Collations and Comparisons") on this subject: http://www.elevatesoft.com/articles?action=view&articleno=2 -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |