slow query 
Author Message
 slow query

Hi all,

I have a very simple query on a free table (dbf). This
table is located on another server. I'm using the ODBC
driver:
  "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;
SourceDB=\\servername\somepath;
Exclusive=No;Backgroundfetch=No"

Query: select * from article where a_number=='AX3A'
This performs fine.
But when I use some kind of string method, it last as long
as selecting and downloading the whole table.
Query: select * from article where left(a_number,4)=='AX3A'

Anyone an idea???

Kind regards,
Reinout



Sun, 04 Sep 2005 05:43:47 GMT  
 slow query

Quote:

> I have a very simple query on a free table (dbf). This
> table is located on another server. I'm using the ODBC
> driver:
>   "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;
> SourceDB=\\servername\somepath;
> Exclusive=No;Backgroundfetch=No"

> Query: select * from article where a_number=='AX3A'
> This performs fine.
> But when I use some kind of string method, it last as long
> as selecting and downloading the whole table.
> Query: select * from article where left(a_number,4)=='AX3A'

> Anyone an idea???

If you have an index tag on a_number, the first query is optimizable
while the second is not.

To make the second optimizable, you need an index on the same
expression used in the query, here LEFT(a_number,4)

Christian Desbourse (Belgium)
http://www.cdesbourse.com



Sun, 04 Sep 2005 09:16:24 GMT  
 slow query
Hi, Reinout!
You wrote  on Tue, 18 Mar 2003 13:43:47 -0800:

 R> Query: select * from article where a_number=='AX3A'
 R> This performs fine.
 R> But when I use some kind of string method, it last as long
 R> as selecting and downloading the whole table.
 R> Query: select * from article where left(a_number,4)=='AX3A'

You have to read more about Rushmore technology in help files. Your second
query is not optimizable (unless you have index on LEFT(a_number, 4))

WBR, Igor



Sun, 04 Sep 2005 16:08:33 GMT  
 slow query
On the subject of the SourceDB - Do you have to ensure that the SQL Server
has a path mapped to the specific servername, becuase I am tryining to run a
query  where the SQL Server is in a different domain to where the dbf is
stored. The server has path matched to S:\, I can run a query using say
 "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=S:\;
Exclusive=No;Backgroundfetch=No"
but
"Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=\\servername\somepath;
Exclusive=No;Backgroundfetch=No"
results in a .dbf does not exist!!

Any ideas, and sorry to latch onto this message thread

Jon King


Quote:
> Hi all,

> I have a very simple query on a free table (dbf). This
> table is located on another server. I'm using the ODBC
> driver:
>   "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;
> SourceDB=\\servername\somepath;
> Exclusive=No;Backgroundfetch=No"

> Query: select * from article where a_number=='AX3A'
> This performs fine.
> But when I use some kind of string method, it last as long
> as selecting and downloading the whole table.
> Query: select * from article where left(a_number,4)=='AX3A'

> Anyone an idea???

> Kind regards,
> Reinout



Tue, 06 Sep 2005 19:55:07 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Slow Queries

2. Slow Query - Fast PC

3. Slow Queries

4. More Slow Queries

5. Very Slow Query

6. Slow Query with ODBC

7. slow queries with visual foxpro 5.0

8. setting form's datasession to private cause slow query data

9. Foxpro Gets Slower And Slower And Slower.....

10. slow, slow, pokey slow....

11. modify remote view is slow, slow slow!

12. Slow SQL Queries

 

 
Powered by phpBB® Forum Software