Should it take Fox 10 minutes to query 2,289,600 records? Maybe you would need to know the data. I am struggling with a datalogger that uses ODBC to plug data into a table. You have two tables. Table one has four fields there is no index set up. The datalogger software created the tables automatically. The fields are 1. Date and Time 2. Tag # 3. Value 4. Status Table 2 just has two fields 1 Tag # 2 Tag Name. The logger will log every 60 seconds 53 tag values which gives you 2,289,600 records per month. I created a query to pull out just one days data (took 9 minutes) then I got crazy and tried to create a crosstab to plug the data and the tag names together ( so I could use it in a report) don’t know if it worked after 20 minuets I pulled the plug. What have I done wrong????????
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Fox can’t be this slow
- Thread starter DaveMac
- Start date
Hi DaveMac,
I have gotten some really strange results when perform queries against a VFP table, especially where the query was not properly constructed the tables were not indexed and there were no common fields between the table upon which to build a relationship.
You might try reorganizing your table better and setting up some indexes on the fields you are likely to query. In order to get rushmore optimization you will need to construct your SQL so it exactly matches the way the index is built per the index command. Also, be sure to include a common fields in both tables so that you can establish proper relationships. Are your tables setup in a one to one relation, one to many relation, or many to many relation?
If the relation was one to many and there was a primary key in a parent table that matched a key in the child table, and you had indexes on both tables based on this key, it should speed speed up your queries greatly.
So, if you wanted to grab transactions based on the day they occurred, you should have a date field in both tables and both tables should have an index on the date field.
Then you could set a relation from table1 into table1 based on the date field. The when you make a query that involves selecting fields from both tables you something like the following in your SQL clause:
Select table1.field1, table1.field2,table2.field1,+;
table2.field2+;
FROM table1 INNER JOIN table2 "+;
ON table1.date = table1.date+;
WHERE (table2.date = XXXXXXXXX)
order by table1.field into cursor query
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
I have gotten some really strange results when perform queries against a VFP table, especially where the query was not properly constructed the tables were not indexed and there were no common fields between the table upon which to build a relationship.
You might try reorganizing your table better and setting up some indexes on the fields you are likely to query. In order to get rushmore optimization you will need to construct your SQL so it exactly matches the way the index is built per the index command. Also, be sure to include a common fields in both tables so that you can establish proper relationships. Are your tables setup in a one to one relation, one to many relation, or many to many relation?
If the relation was one to many and there was a primary key in a parent table that matched a key in the child table, and you had indexes on both tables based on this key, it should speed speed up your queries greatly.
So, if you wanted to grab transactions based on the day they occurred, you should have a date field in both tables and both tables should have an index on the date field.
Then you could set a relation from table1 into table1 based on the date field. The when you make a query that involves selecting fields from both tables you something like the following in your SQL clause:
Select table1.field1, table1.field2,table2.field1,+;
table2.field2+;
FROM table1 INNER JOIN table2 "+;
ON table1.date = table1.date+;
WHERE (table2.date = XXXXXXXXX)
order by table1.field into cursor query
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
Hi DaveMac,
I have gotten some really strange results when performing queries against a VFP table, especially where the query was not properly constructed the tables were not indexed and there were no common fields between the table upon which to build a relationship.
You might try reorganizing your table better and setting up some indexes on the fields you are likely to query. In order to get rushmore optimization you will need to construct your SQL so it exactly matches the way the index is built per the index command. Also, be sure to include a common fields in both tables so that you can establish proper relationships. Are your tables setup in a one to one relation, one to many relation, or many to many relation?
If the relation was one to many and there was a primary key in a parent table that matched a key in the child table, and you had indexes on both tables based on this key, it should speed speed up your queries greatly.
So, if you wanted to grab transactions based on the day they occurred, you should have a date field in both tables and both tables should have an index on the date field.
Then you could set a relation from table1 into table1 based on the date field. The when you make a query that involves selecting fields from both tables you something like the following in your SQL clause:
Select table1.field1, table1.field2,table2.field1,+;
table2.field2+;
FROM table1 INNER JOIN table2 "+;
ON table1.date = table1.date+;
WHERE (table2.date = XXXXXXXXX)
order by table1.field into cursor query
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
I have gotten some really strange results when performing queries against a VFP table, especially where the query was not properly constructed the tables were not indexed and there were no common fields between the table upon which to build a relationship.
You might try reorganizing your table better and setting up some indexes on the fields you are likely to query. In order to get rushmore optimization you will need to construct your SQL so it exactly matches the way the index is built per the index command. Also, be sure to include a common fields in both tables so that you can establish proper relationships. Are your tables setup in a one to one relation, one to many relation, or many to many relation?
If the relation was one to many and there was a primary key in a parent table that matched a key in the child table, and you had indexes on both tables based on this key, it should speed speed up your queries greatly.
So, if you wanted to grab transactions based on the day they occurred, you should have a date field in both tables and both tables should have an index on the date field.
Then you could set a relation from table1 into table1 based on the date field. The when you make a query that involves selecting fields from both tables you something like the following in your SQL clause:
Select table1.field1, table1.field2,table2.field1,+;
table2.field2+;
FROM table1 INNER JOIN table2 "+;
ON table1.date = table1.date+;
WHERE (table2.date = XXXXXXXXX)
order by table1.field into cursor query
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
Hi DaveMac,
I have gotten some really strange results when performing queries against a VFP table, especially where the query was not properly constructed, the tables were not indexed, and there were no common fields between the table upon which to build a relationship.
You might try reorganizing your tables better, and setting up some indexes on the fields you are likely to query. In order to get rushmore optimization you will need to construct your SQL so it exactly matches the way the index is built per the index command. Also, be sure to include a common fields in both tables so that you can establish proper relationships. Are your tables setup in a one to one relation, one to many relation, or many to many relation?
If the relation was one to many and there was a primary key in a parent table that matched a key in the child table, and you had indexes on both tables based on this key, it should speed up your queries greatly.
So, if you wanted to grab transactions based on the day they occurred, you should have a date field in both tables and both tables should have an index on the date field.
Then you could set a relation from table1 into table2 based on the date field. Then when you make a query that involves selecting fields from both tables you could do something like the following in your SQL clause:
Select table1.field1, table1.field2,table2.field1,+;
table2.field2+;
FROM table1 INNER JOIN table2 "+;
ON table1.date = table2.date+;
WHERE (table2.date = XXXXXXXXX+;
order by table1.field1 into cursor your_query
Sorry about the multiple posts, but I'm having one of those day where I'm having difficulty with everything. I was notified by the program that it had timed out, but evidently the posts when through, but before I had a chance to check them.
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
I have gotten some really strange results when performing queries against a VFP table, especially where the query was not properly constructed, the tables were not indexed, and there were no common fields between the table upon which to build a relationship.
You might try reorganizing your tables better, and setting up some indexes on the fields you are likely to query. In order to get rushmore optimization you will need to construct your SQL so it exactly matches the way the index is built per the index command. Also, be sure to include a common fields in both tables so that you can establish proper relationships. Are your tables setup in a one to one relation, one to many relation, or many to many relation?
If the relation was one to many and there was a primary key in a parent table that matched a key in the child table, and you had indexes on both tables based on this key, it should speed up your queries greatly.
So, if you wanted to grab transactions based on the day they occurred, you should have a date field in both tables and both tables should have an index on the date field.
Then you could set a relation from table1 into table2 based on the date field. Then when you make a query that involves selecting fields from both tables you could do something like the following in your SQL clause:
Select table1.field1, table1.field2,table2.field1,+;
table2.field2+;
FROM table1 INNER JOIN table2 "+;
ON table1.date = table2.date+;
WHERE (table2.date = XXXXXXXXX+;
order by table1.field1 into cursor your_query
Sorry about the multiple posts, but I'm having one of those day where I'm having difficulty with everything. I was notified by the program that it had timed out, but evidently the posts when through, but before I had a chance to check them.
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
Hi Brian,
I'm running off of a network and I'm using the AVG anti-virus system, but mainly I think I'm just having one of those days.
Thanks Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
I'm running off of a network and I'm using the AVG anti-virus system, but mainly I think I'm just having one of those days.
Thanks Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
Leland,
My post was actually refering to DaveMac's question!
As for your issue...I too experienced slow and timed-out Tek-Tips site responses earlier today so it isn't just you. I thought it might have been my workplace's proxy though... but I guess the site may just be getting too popular at 'peak times' for it's hardware!
Brian
My post was actually refering to DaveMac's question!
As for your issue...I too experienced slow and timed-out Tek-Tips site responses earlier today so it isn't just you. I thought it might have been my workplace's proxy though... but I guess the site may just be getting too popular at 'peak times' for it's hardware!
Brian
DaveMac,
Simple questions:
1) What version and SP of VFP are you using?
2) What kind of network are you working on, how many connections, and how "busy" is it?
3) What's the hardware configuration of the file server and workstation?
Your question is too much like "Why is my car slow?". There are no meaningful answers without the details.
Rick
Simple questions:
1) What version and SP of VFP are you using?
2) What kind of network are you working on, how many connections, and how "busy" is it?
3) What's the hardware configuration of the file server and workstation?
Your question is too much like "Why is my car slow?". There are no meaningful answers without the details.
Rick
- Thread starter
- #10
DBRANDON334
Programmer
Create the indexes first-otherwise fox is having to index on the fly in the query. with 2 mil records and no index and pulling 1/30 of the records this will be very slow without the index!
- Status
Similar threads
- Locked
- Question