Query Performance Tips

Results 1 to 3 of 3

Thread: Query Performance Tips

  1. #1
    Join Date
    Dec 1969

    Default Query Performance Tips

    Hi all;<BR><BR>I have a search that I run against a FoxPro2.6 database with 62,000 records, 180 columns and 113+ megs worth of data. I connect to the database using ODBC (I use the Visual FoxPro driver and a free table). I can not, unfortunately, modify the structure of the database, nor can I import it into another database.<BR><BR>No matter what kind of a query I perform or no matter how many records are found, it always takes about 6-8 seconds to pop up the list of search results (results.asp). What is even more discouraging, is when I go to display an entire record (record.asp) is that it take a further 6-8 seconds to display that record.<BR><BR>What can I do to increase the performance of the search? This database does have an associated index file, but I am not sure if it is being used or not. It is located in the same folder as the database, but I did not do anything special in my SQL statement.<BR><BR>If anyone can point me to some resources that might help me out, I would greatly appreciate it. Thank you very much for your time!

  2. #2
    Join Date
    Dec 1969

    Default RE: Query Performance Tips

    I think a big clue is the fact that it takes just as long to pull up the single record as it does to get the list of relevant records. That says to me that the second query is *not* using an indexed field. And that being so, I&#039d bet that neither is the first query.<BR><BR>You say there is an "associated index file", but what field is it an index *for*? Consider this case:<BR><BR>Table: Employee<BR>Fields: EmpName, EmpID, EmpDept, ...<BR><BR>If the only indexed field is "EmpID" and both of your queries are based on "EmpName", then the presence of the index does you no good at all.<BR><BR>I might comment that 180 columns in a single table is obscene, but if you can&#039t change structure, you can&#039t.<BR><BR>Anyway, 6 to 8 seconds to literally paw through the entire file (which is what happens when the index isn&#039t being used) sounds like about right to me. I don&#039t see how you can improve things unless you can get more info on the structure of the DB. In particular, which field(s) does the index file apply to? Is the index up to date, even?<BR><BR>I would bet that your second query could be considerably speeded up. To use my example above, suppose that your initial query to get the list of records is based on EmpName, where the only index pertains to EmpId. But if you *also* "remember" the EmpId values you obtain in the first query, and if you pass one of *those* values to the second query instead of the name again, then presumably at least the second query would be index fast.<BR><BR>Finally, you say you can&#039t alter the structure of the database. Fine. But can you add an index to it? That won&#039t alter anything that exists, but if the index is tailored for the field on which you do your query(ies) then your performance should shoot way up.<BR><BR>Bill<BR><BR>p.s.: Oops...one other idea! You say you can&#039t import this DB into another one. But how about using another DB as a pseudo-index into this one? Again, to use the example above, suppose you created a "look aside" table, indexed by EmpName that had only the EmpName and EmpId fields replicated. Once you found the relevant names and collected all the needed ID values, you could then do a "SELECT * FROM FoxProTable WHERE EmpId in (37,99,101,4411)" type of statement which, if EmpId is indexed in the original table, would be quite fast. Your look aside index wouldn&#039t even have to be on the same machine or be another FoxPro DB. (Of course if the original DB is updated frequently, then keeping the lookaside DB in sync is problematical.)<BR><BR>p.p.s.: One question before I make a silly suggestion: How many records do you typically pull up for your list in the first query? A handful? Dozens? More?

  3. #3
    Rob Guest

    Default RE: Query Performance Tips

    What is this index file and what oes it do?<BR>Is this native to FoxPro?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts