TMSTable taking FOREVER to Open or Activiate

[gymwalker]

I'm testing the SDAC Trial version and I can connect to my MSSQL Db just fine...however when I define a TMSTable that I need to read through and set Active := True...it takes forever to come back...in fact I have yet to be able to be patient enough to wait for it to come back...the longest I've lasted is 20 minutes and still no response.

I've played with FetchAll := True and False and haven't noticed any difference. This is a very LARGE table...over 3 million rows...I really need something that is going to give me some performance...I might be able to tolerate a 30 to 60 second wait...but that's about it.

Here are some of my settings for the TMSTable:
AutoCalcFields := true
CachedUpdates := false
CursorType := ctKeySet
FetchAll := true
FetchCount := 1

No filter set

IndexFieldNames := CUSTEDP

Most of the Options are set to default values

ReadOnly := false
UniDirectional := false

The only modification I've made to the TMSConnection component is I've set MultipleActiveResultsSets := true.

I tried looking through the Forum for something related to this but couldn't find anything.

I'm kind of under the gun to get this app working...so resolving this ASAP would be greatly appreciated...I guess I need some guidance as to how to achieve optimal performance from these components when dealing with large tables?

Thanks in advance,
Jim

[Challenger]

The

Code: Select all

IndexFieldNames := CUSTEDP 

line enables local sorting that requires all data to be fetched. That's why changing the FeatchAll property will not have any effect.