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

[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