How to index afield? 1

[ProtocolPirate]

My last problem turned out to be an index problem w/ Pervasive.SQL. One table is imported from a spreadsheet while the other is linked from SQL, so I figured I could just add my own indexes to the imported spreadsheet and reverse the order of the INNER JOIN, but this query never returns too.

Original:

SELECT [field list]FROM sheet4 AS s INNER JOIN patient AS p
ON p.SSN=s.SSN OR p.InsId1=s.ID OR p.InsId2=s.ID

Modified:

SELECT [field list]FROM patient AS p INNER JOIN sheet4 AS s
ON s.SSN=p.SSN OR s.ID=p.InsId1 OR s.ID=p.InsId2

When importing the spreadsheet I specified both SSN and ID as indexed fields, but Access doesn't seem to be generating the indexes. Is there a special trick you have to pull in order to get the indexes to wake up?

 

[dhookom]

I am not aware of using OR with inner joins. What do you get if you try

SELECT [field list]FROM patient AS p, sheet4 AS s
WHERE s.SSN=p.SSN OR s.ID=p.InsId1 OR s.ID=p.InsId2;

Duane MS Access MVP

 

[ProtocolPirate]

I get an infinite wait.

 

[ProtocolPirate]

Oh wow! It took awhile but it actually came back after about 15 minutes!

 

[dhookom]

I expect your issue is caused by a join of a local Access table and a table on a server. Do you know if the table in Pervasive.SQL is index on the join/criteria fields?

How many records are contained in the tables?
I have created solutions where I imported remote server records into local, temporary tables which improved the performance 100 fold.

Duane MS Access MVP

 

[ProtocolPirate]

Some indexes on the server, but that is the problem, the files are busted such that I can't indexes, which is a whole other can of worms that I have to fix. I had just one query I had to run in a short deadline, so I was trying to temporarily get around the problem by using indexes in Access, to no avail.

I tried to import the server tables rather than linking to them, but there were null date fields and Access would die on this without giving you any option to massage the data.

The server table has about 200,000 records and the Access table had imported from a spreadsheet had 24,000 records.

 

[dhookom]

I would use a pass-through query to push the data into a temp table. You could then massage and append the data to another table for joining to your Access table.

Duane MS Access MVP