I have just been notified by a Danish collegue that I wasn't very clear in my documentation on the SQL efficiency, and that's of course complete correct. Here is the correct result: Select * From MYTABLE Where MYCOLUMN = "A given value" Into MY_SEL NoSelect Takes 1 sec. (No index: 10 secs) Select * From MYTABLE Where MYCOLUMN Like "A given value%" Into MY_SEL NoSelect Takes 12 sec. (No index: 12 secs) Select * From MYTABLE Where MYCOLUMN Like "%A given value%" Into MY_SEL NoSelect Takes 12 sec. (No index: 12 secs) Sorry for any misinformation Peter Horsb�ll M�ller GIS Developer Geographical Information & IT COWI A/S Rug�rdsvej 55 DK-5000 Odense Denmark Tel +45 6313 5013 Direct +45 6313 5008 Mob +45 5156 1045 Fax +45 6313 5090 E-mail <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] http://www.cowi.dk <http://www.cowi.dk/>
-----Original Message----- From: Lars V. Nielsen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:07 AM To: Peter Horsb�ll M�ller Subject: Re: MI-L Query Efficiency Peter, Skulle det ikke have v�ret ... MYCOLUMN LIKE "%A given value%" ... for at give et sammenligneligt resultat ?? Best regards / Med venlig hilsen Lars V. Nielsen -------------------------------------------------------- Hvenegaard & Meklenborg Rugaardsvej 55, DK-5000 Odense C Denmark <http://www.hvm.dk> http://www.hvm.dk ----- Original Message ----- From: "Peter Horsb�ll M�ller" < <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]> To: "'Eagle, David A'" < <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]>; < <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]> Sent: Tuesday, June 03, 2003 8:19 AM Subject: RE: MI-L Query Efficiency > One way to speed up the query might be to avoid the LIKE operator if > possible. Especially if you are using it like this "%" + szValue + "%". In > this case I guess indexing the column will not help, because MapInfo has to > look thru all rows. > > Just to make sure I have done some testing on a table with 590000 records. > > Select * From MYTABLE Where MYCOLUMN = "A given value" Into MY_SEL NoSelect > Takes 1 sec. (No index: 10 secs) > Select * From MYTABLE Where MYCOLUMN = "A given value%" Into MY_SEL NoSelect > Takes 12 sec. (No index: 12 secs) > Select * From MYTABLE Where MYCOLUMN = "%A given value%" Into MY_SEL > NoSelect > Takes 12 sec. (No index: 12 secs) > > As you can se the time MapInfo uses to perform an exact match with no index > is more or less the same as when performing a LIKE search. And appearently > there is no difference whether the is a "joker" at both ends or only at the > end. > > Another way to speed up the queries might be to use the NoSelect keyword. > This prevents MapInfo from highlighting the selected rows. > > Peter Horsb�ll M�ller > GIS Developer > Geographical Information & IT > > COWI A/S > Rug�rdsvej 55 > DK-5000 Odense > Denmark > > Tel +45 6313 5013 > Direct +45 6313 5008 > Mob +45 5156 1045 > Fax +45 6313 5090 > E-mail <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > <http://www.cowi.dk> http://www.cowi.dk > > > > -----Original Message----- > > From: Eagle, David A [mailto:[EMAIL PROTECTED] > > Sent: Monday, June 02, 2003 4:56 PM > > To: MapInfo-L ( <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]) > > Subject: MI-L Query Efficiency > > > > > > Listers, > > > > I have created a gazetteer query tool (in MapBasic) to query > > an MI table containing 260,000 records. > > > > The process takes a variable from my dialog and searches the > > place name field (indexed) for instances LIKE it and then > > returns a query table containing two columns and the object. > > The process then goes on to populate an array etc, > > etc...However, the query process described takes 20 seconds, > > this is always the case and does not vary if I move the > > database from its network location to a local drive. > > > > Any thoughts on how I can speed this process up would be > > greatly appreciated... > > > > ---------------------------------------------- > > David A. Eagle > > GIS Consultant > > > > Atkins Design Environment > > & Engineering > > Cornerstone House > > Stafford Park 13, Telford > > Shropshire, TF3 3AZ > > England > > Tel: +44 (0)1952 21 3268 * > > Fax: +44 (0)1952 20 0981 * > > > > Email: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] * > > Web: <http://www.atkinsglobal.com> www.atkinsglobal.com < <http://www.atkinsglobal.com> www.atkinsglobal.com> * > > > > > > > > This email and any attached files are confidential and > > copyright protected. If you are not the addressee, any > > dissemination of this communication is strictly prohibited. > > Unless otherwise expressly agreed in writing, nothing stated > > in this communication shall be legally binding. > > > > > > > > --------------------------------------------------------------------- > > List hosting provided by Directions Magazine | > > <http://www.directionsmag.com> www.directionsmag.com | To unsubscribe, e-mail: > > <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > > For additional commands, e-mail: > > <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > > Message number: 7107 > > > > --------------------------------------------------------------------- > List hosting provided by Directions Magazine | <http://www.directionsmag.com> www.directionsmag.com | > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > Message number: 7117 > > >
