Large MSSQL data set error

[CoZiMan]

Hi all,

Hope an FM SQL guru can help.

I am accessing a large list of tables and one table has ~500,000 records ~30 fields.

Using 3 or more key fields to create a smaller subset fails to show more than a few records either in a portal or gttr.

Other smaller dataset tables ~5000 return complete sets in the ~100's or less and very quickly!

I do have a built in report on the SQL side that returns subsets quickly but is in-flexible as to key fields and output format.

I see two ways forward I haven't tried.

1. Try a scripted find then copy to a native fm table.

2. Try using the same key fields as the built-in report and see if that provides results.

Thoughts? Suggestions? Comments?

TIA.

Edited May 26, 2017 by CoZiMan

[Wim Decorte]

7 hours ago, CoZiMan said:

Using 3 or more key fields to create a smaller subset

 

Can you describe this a bit more?  What if you test with one key field, then with the second and see if the resulting record set is plausible.  If the resulting sets are ok with these two then the 3rd key may produce the logical data set but not the set that you expect...

[CoZiMan]

Ah. Good thought.

Testing now.

3 key relationship: minimal (incorrect) return.
2 key relationship: minimal but DIFFERENT (incorrect) return.
1 key relationship: minimal but DIFFERENT (incorrect) return.

Testing DIFFERENT SINGLE KEYS no joy.

Always a maximum of 3 records returned.

I am now noticing one other large record count table that has the same problem.

 

 

 

 

Edited May 26, 2017 by CoZiMan

[Wim Decorte]

Could it be that you are using the trial version of the ODBC drivers?  Those always limit the return to 3 records I believe.

[CoZiMan]

Wim, I made sure I have the paid version. Re-added the user and system dsn.

When I add that table and go to view after three records I get timeouts and no data.

Hm.

[beverly]

Set up a View in the MySQL and narrow your set first? Is that what you mean by

Quote

I do have a built in report on the SQL side that returns subsets quickly but is in-flexible as to key fields and output format.

What about this does not work?

beverly