• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

speeding dbf queries results .net (cdx?)

Hi, I need to optimize a query in FoxPro 2.6 that I'm running from a dataset in visual studio 2008 the query is posted below.

I've been provided with cdx index files and been told they can help me speed up the query results, any ideas?


SELECT geprevis.contrato, geprevis.codsoc, ccsocios.sonombre, ccsocios.soapelli, ccsocios.sosexo, ccsocios.sofecnac, ccsocios.soestciv, ccsocios.sodirecc,
                          ccsocios.sodistrito, ccsocios.sotelefo, ccsocios.socelu, ccsocios.somail, ccsocios.solibele, ccsocios.soocupa, ccsocios.socentra
FROM geprevis, ccsocios
WHERE (geprevis.codsoc = ccsocios.socodsoc) AND (geprevis.contrato LIKE 'S% ') AND (ccsocios.soapelli LIKE? + '% ') OR
                          (ccsocios.sonombre =? + '% ')

Open in new window

0
infogws
Asked:
infogws
  • 6
  • 2
  • 2
  • +2
4 Solutions
 
infogwsAuthor Commented:

it takes about 10-15 minutes to search among 65000 records...
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi buddy, I think that you must check your table indexes. How many rows have those tables?
0
 
infogwsAuthor Commented:
I've never worked with cdx files before to be honest.
Rows or columns? ccsocios has more than 20 rows, but I only need around 10.
I need to make a query to two tables at the same time.(geprevis and ccsocios).

When I only use one parameter it takes no longer than 3-5 seconds. When using 2 parameters it takes between 10-15, sometimes more (up to 20).
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
infogwsAuthor Commented:
I've never worked with cdx files before to be honest.
Rows or columns? ccsocios has more than 20 columns, but I only need around 13.
I need to make a query to two tables at the same time.(geprevis and ccsocios).

When I only use one parameter it takes no longer than 3-5 seconds. When using 2 parameters it takes between 10-15, sometimes more (up to 20).
0
 
miketonnyCommented:
the slow speed is because of the where clause, if you using non-indexed fields in where clause foxpro will take a long time to return the query, can you set the fields in where clause as index fields? is it an option for you or you can't change database?
alternatively, you can bring all the data in without 'where' then sort them later on by codes.
0
 
infogwsAuthor Commented:
I cant alter the dbf/cdx files. The database is currently used by another (older) system.
0
 
miketonnyCommented:
what you can do then is get all the data from these two tables, then loop through them in code to filter out the ones you don't want.
SELECT geprevis.contrato, geprevis.codsoc FROM geprevis
SELECT ccsocios.sosexo, ccsocios.sofecnac, ccsocios.soestciv, ccsocios.sodirecc, ccsocios.sodistrito, ccsocios.sotelefo, ccsocios.socelu, ccsocios.somail, ccsocios.solibele, ccsocios.soocupa, ccsocios.socentra
FROM ccsocios

then after reading the data then you  filter out the one's you dont use, should save you some time.
0
 
pcelbaCommented:
Create indexes on following columns (as the first step):
Table geprevis - codsoc, contrato
Table ccsocios - socodsoc, soapelli, sonombre

Collation sequence of above indexes must be same as the collation sequence in connection string. (Machine is used when no collation is in the connection string.)

And please remember, expressions in your WHERE clause are just partially optimizable.

Also parenthesis in WHERE clause are not necessary in your case but they should not affect optimization in this case.
0
 
pcelbaCommented:
You should also check the OLE DB driver version. The possibility to partially optimize LIKE operator is included in the latest version only.
0
 
Olaf DoschkeSoftware DeveloperCommented:
With foxpro you could see which index expressions you have, that would help a lot to advise which where clause would work better. Without knowing about the cdx structure it's impossible to say for sure what helps.

PCelba is right, that anyway your where clause is only optimizable in vfp9 because of using LIKE.

The only part of your where clause , which might be optimized is  geprevis.codsoc = ccsocios.socodsoc.

Instead of LIKE 'something%' as in "starting with someting", in foxpro you can simply use a non ansi standard of string comparison that only compares up to the length of the searched value. That is what ANSI=OFF in your ODBC or OLEDB connecction will cause, then you can query WHERE field = 'S' instead of Where field LIKE 'S%'. and this would be optimized, if an index on the field exists.

Bye, Olaf.
0
 
infogwsAuthor Commented:
I ended up using just one parameter. I kept getting errors and the I was running out of time. Thank you all for your help!
0
 
infogwsAuthor Commented:
Thanks for your ideas
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now