Pass through query with odbc - specifying index field(s) (1 Viewer)

[samcoinc]

We have started using netsuite - it comes with what they call 'suite analytics' which gives you access to the underlying data. This is based on oracle. The ODBC driver when you link tables requires you to select the index(s) for the given table (I honestly have not see this since foxpro and great plains before Microsoft bought them)

The issue is that some of the tables have more than 255 fields so linking the tables isn't an option. Enter the pass through query.

SELECT item_id, NAME, DISPLAYNAME FROM ITEMS

It seems super slow and flakey - I am wondering if it is because I have not specified an index field (which is item_id)

I have tried a few things like 'with(index(item_id)) which comes up with a syntax error...

am I making sense?

thanks!
sam

ps - long time since I posted on here..

 

[samcoinc]

Well - I don't know what is different but the queries are running more consistent. It looks like if I want the most performance I need to create on pass through query to connect all the tables together... I guess that makes sense..

SELECT BIN_NUMBER_COUNTS.AVAILABLE_COUNT, BIN_NUMBER_COUNTS.BIN_ID, BIN_NUMBER_COUNTS.BIN_NUMBER, BIN_NUMBER_COUNTS.ITEM_ID, BIN_NUMBER_COUNTS.LOCATION_ID, BIN_NUMBER_COUNTS.ON_HAND_COUNT, ITEMS.NAME, ITEMS.DISPLAYNAME, ITEMS.type_name
FROM BIN_NUMBER_COUNTS INNER JOIN ITEMS ON BIN_NUMBER_COUNTS.ITEM_ID = ITEMS.ITEM_ID Where (([type_name]='Assembly') and (BIN_NUMBER_COUNTS.ON_HAND_COUNT>0))

The above runs many times faster than a pass through and table connected together in a 2nd query. The data returns makes sense too..

sam

 

[Pat Hartman]

The magic of Access is that you can join tables from different databases so you can join Oracle tables to Jet or DB2 to SQL Server. However, no one said it would be efficient To perform this magic, Access brings the entire contents of the ODBC table/tables back to memory in the computer that ran the query.

The best solution, if you can get someone to do this for you is to make views that select only the columns you need from the wide tables and also to do common joins.

Otherwise, you are stuck with pass through queries.