Is there a way to speed this query up?

[Guest vfpdev]

Hello,I am wondering if someone a bit more experienced than I can offer any suggestions to help improve the performance (speed) of this query.Currently, I am using this SQL select statement from within Visual Foxpro:select * from MyTable where id like '05%'There are a total of 137827 records in MyTable, and this query takes between 3-5 seconds.The number of records that match the Where clause criteria are about 1200.Thanks in advance! vfpdev

[justsomeguy]

That query is so simple that there is very little you can do to optimize it. My own experiences with FoxPro have been pretty negative, it seems plagued by bad design decisions that impose arbitrary restrictions and limit what it can do. It just might be that the storage engine is so inefficient that the best you can do is 3 seconds.One thing you might try is to add indexes to the table, index the column that you are searching on (id) and make it the primary key if you can. Also, don't select everything if you don't need everything, just get what you need.

[Yahweh]

Hello,I am wondering if someone a bit more experienced than I can offer any suggestions to help improve the performance (speed) of this query.Currently, I am using this SQL select statement from within Visual Foxpro:select * from MyTable where id like '05%'There are a total of 137827 records in MyTable, and this query takes between 3-5 seconds.The number of records that match the Where clause criteria are about 1200.Thanks in advance! vfpdev

Just wondering, but in what context would you ever need to execute a query like that?First, to speed up your query, you probably shouldn't use "Select *" unless you actually need to return all the columns in your table. If your table has 12 columns, but you only need to use 1 or 2 of thsoe columns, then you're just wasting processing time. You should directly specify which columns you intend to use, that way you return the minimal of data.In any case, I think the query could be speed up with some slighly convoluted programming work-arounds. Generally, in order of speed, the % operator is the slowest (especially on integers, because it has to cast integers as a string before they can be compared), followed by the > and < operators, followed by the BETWEEN operators, and finally the = operator. Based on your query, your best bet to improve the speed is creatively using the BETWEEN operator.If you know how many records are in your database, then you know how many digits you have to work with, then you can pad your search string with 0s until it matches the number of digits in your records. For example, because 137827 contains 6 digits, you would pad 05 with 0s until to match 6 digits resulting in 050000. Now, you can run your query in one of two ways:

-- First method, just find all records with an ID greater than some valueselect * from MyTable where id >= 050000;-- Second method, the BETWEEN operator is slightly faster than the the ">" operator,-- so you can specify finding any records between a minimum value and an-- arbitrarily large value.select * from MyTable where id BETWEEN 050000 AND 99999999;

Either of those optimizations should be significantly faster than using a "%".