FM and SQL optimization

[lsmall]

Hi Folks.

I've been working with filemaker with SQL databases for awhile and need some tips. Basically I use FM 12 to pull in and process data from a large SQL data repository that I have no control over. I run FM 12.3 on OS X Lion on a late 2011 mac mini and use Actual ODBC. The SQL database is huge and is esentially a warehosue for hospital data. I mine a very small percentage of the available data. I am not the administrator of the SQL database and I have very little knowledge of its inner workings.

 

Working with the SQL database has always been slow, but now I am looking to do more SQL queries with more complex data and I find that the queries are just too slow to the point of precluding any utility. I'm not sure if this is an SQL issue, Filemaker issue, Actual ODBC issue, Network issue, all of the above etc. I'm sorry I can't give you exact information about the SQL database, but I'm just looking for a bit of direction as to the possbility of optimizing all of this. If it would help to have the exact details of our SQL server I can try to get some info, but the ODBC manager shows Microsoft SQL Server 09.00.5057 for what its worth.

 

Thanks, any advise is appreciated.

[Wim Decorte]

One of the things you can try is to ask the SQL administrator to build you a "View" with just the dataset that you want to mine. That way your queries will just hit the View instead of the whole big table.

[lsmall]

You are right. That is probably the best way to speed it up. I'll probably end up doing that once I am sufficiently frustrated.

 

Thanks