SQL ESS Search Performance - best practices

Discussion created by tomoy on Feb 6, 2013
Latest reply on Feb 6, 2013 by tomoy

Hi - I'm recreating in FMP12 a solution I built a decade ago in FMP5. The old solution could import data from an MSSQL database and at that time the data was in FMP so it would auto-index and searching these fields became relatively quick.


In the new solution I'm trying to find a place for this awesome ESS SQL power, have created a shadow table and dropped some of those fields into a different table's layout. I can search a single field, and it takes about as much time as I'd expect to having to reach through to an un-indexed field. Try to do any fancy searches though, and it becomes uselessly frozen.


So, should I resolve myself to importing/updating locally into FMP fields for any field I want to search? It starts to diminish the power of working with shadow tables/fields. I'm guessing 75% of my fields will need to be indexed. Curious what people do in these types of cases. I'm basically using FileMaker to house 'metadata' that won't gracefully fit into our ERP's inventory control system.


Please pardon any stupidity in advance, leaping from v5 to v12 is a big one, and I'm trying to dust off my cobwebs. Not a developer - just IT mgr in a small office trying to solve productivity problems.