Wondering how fast filemaker can be via odbc(MS SQL) when compared to MS Access

[mak]

I'v been using Filemaker for more than 5 years.

Most of the time, I was using Filemaker server with filemaker pro as clients.

 

Now, I'm facing the big problem of performance of Filemaker when using ODBC. 

 

If I make MS Access to link to external SQL Server via ODBC, it works okay

(e.g Just easy search something like A_table.a_column="1" and related_B_table_column<100)

 

However, it seems taking forever for filemaker to do the same thing above. 

 

Why does it take so long? Is filemaker supposed to be using with only SQL queries when using ODBC?

Can't I just do regular 'find' after bringing external tables to relationship(database management), and make relationship?

 

Is there any technique to make faster when dealing with ODBC source?

[Wim Decorte]

It's not entirely clear what you are doing that is slow.

 

How do you work with the ODBC external data; ESS, or the "import from ODBC source" and "Execute SQL" script steps?

What's the ODBC source?

What driver are you using?

 

I would strongly suggest that you read chapter 9 of the new FTS book as it describes at length some of things that can make working with ODBC source slow.

[mak]

@Wim

 

I work with "import from ODBC source"  and trying to do 'find' in 'find mode'

e.g. There's table A and related table B then both are accessed via ODBC.

I have a layout for table A and it has table B's related field Z.

 

If I go to 'find mode' and type "20100101...20131231", it takes forever to show the result.

 

Am I supposed to use only SQL query in this case?

[Wim Decorte]

You're mixing a few things up:

 

After an "import from ODBC source", that data then lives natively in a FM table, there is no more connection to the ODBC source.  If you do a find on that table, it's a native FM find and it does not reach out to the ODBC source at all.

So if that find is slow, it's something in your FM setup or FM data

(as in: is that an indexed field, is it perhaps an unstored calculation field,...) - how many records are we talking about here, both in the table and in your expected found set?

 

You have may choices to build a found set.

The obvious one is what you are doing and there is nothing wrong with it: a native FM search.

You can use ExecuteSQL() if you want, it just requires a few more hops to then produce the actual found set of records (vs a list of IDs for instance that the function will return).