Fetching in a loop slows down in SqlServer

[FRBhote]

When I prepare once:

SELECT something FROM something INTO :something WHERE something = :something

and
SqlExecute
SqlFetchNext

in a loop, after a couple of hundred times, it becomes ultra slow. What could be the reason? TD 2.1

[FRBhote]

Funny... If there is a delay introduced then it becomes much faster.

[Jeff Luther]

"If there is a delay introduced... -- when/where in the Loop? And what type of delay? SalPause()?

the

WHERE something = :something

is what catches my eye and I recall some issue some time ago about a bind in the WHERE clause vs. something like:

"... WHERE something = "|| <concat. string version of something>

Though you'll need to call SqlExecute() each time if you concatenate.

Since CTD v2.1 was so long ago it might have been a bug that got fixed in a later TD too. You might want to search this forum to see if this has been discussed before. Maybe it a memory alloc. issue with CTD? (If so, though, why the speed-up if you add a delay?) Let us know if you find out any more.

BTW, if you have a later TD installed (like latest PTF v4.2 for a non-Unicode issue, or v5.2 for Unicode testing), and you have a test case for this (1000 rows in a test DB table, etc.) then I'd try running your test with a later TD to see if there's an improvement.