Calling all Clarion users.
Since converting a Clarion 10 app from using Sybase to using SQL server I have a few areas that are running a fair bit slower.
For instance I have a list box which is populated by calling a table-valued function using PROP:SQL and a loop (it was a procedure in Sybase although the code is practically the same and runs really fast).
The function runs in a few hundred milliseconds however the list box takes a few seconds to populate.
Looking at SQL Profiler I see a ton of cursorfetch and cursorclose calls which seem to account for the extra time. From what I understand, this is how Clarion does things but is there a way to speed this up or reduce the amount of calls made?
If anyone has any suggestions I would appreciate it.
This could potentially be slow. Cursors and loops dont always go well together.
I dont know if you have SQL Views declared as files/tables in a dct, but this would be faster, if you want to update a SQL view, just make sure the file/table you want to update has its unique ID in the SQL view and thus in the dct.
The fastest way I’ve found is declaring a SQL stored procedure which returns data in a multi column view format, and then have that view declared in the dct where you can pass parameters in the Prop:SQL to populate the stored procedure in order to output the correct data and have stored procedure output declared in the dct file/table/view. I use the dct file/table/view to then declare local Q’s based on the dct file/table/view for speed and ease of use.
TLDR using the Prop:SQL to populate local Q’s based on a dct file/table/view declaration.