Alternative for Slow ODBC

[WilliamN]

Good day

Filemaker newbie here

I'm helping sorting out some performance issues on a vb.net application that creates reports via the filemaker odbc connector.  It is extremely slow, querying tables with up to a million records.

Are there any other options one could consider? A rewrite of the vb.net app is off the cards at the moment.

Thanks in advance,

William

[Fitch]

Are all the fields indexed that are being queried? Are there joins involved? If the first answer is no, or the second is yes, perhaps the data can be de-normalized for use in the query. Or you could look at pushing the data into a SQL database, maybe on a nightly script schedule. Or maybe you could explore the Data API.

Welcome to the forums and please update your profile with FileMaker and OS version so we can better answer your questions.

[WilliamN]

16 hours ago, Fitch said:

Are all the fields indexed that are being queried? Are there joins involved? If the first answer is no, or the second is yes, perhaps the data can be de-normalized for use in the query. Or you could look at pushing the data into a SQL database, maybe on a nightly script schedule. Or maybe you could explore the Data API.

Welcome to the forums and please update your profile with FileMaker and OS version so we can better answer your questions.

Thanks Fitch.

Getting the data to SQL is exactly what I'm looking at.  In that case I can swing the connection strings of the VB app to SQL.  

Here's what I've tried thus far:

FmPro Migrator: the app crashes when I click on the "Get field size" button for the bigger tables

Full Convert (x32 & x64) excruciatingly slow and gives an assortment or errors including arithmetic exceptions and ODBC connection timeouts

Custom C# application: similar issues to Full Convert

I've also tried MirrorSync, which seems to be an ideal solution, but it's pricey and the client would most likely not be willing to pay for the SQL license. 

So the next best thing I figure is as you say, trying the data api. 

Edit:

I was wondering if the following were practical/possible:

We're running daily reports mostly, so would I be able to migrate data from my bigger tables to daily updated tables via the built in FM scripts?  That way the vb.net app would say only have to query 4k/5k records.

Or an additional database, which would basically be a clone of the other one, but with historical data removed?

Edited July 24, 2020 by WilliamN