SQL Looping when result is a lot of lines

questions
1

Hello,

I have question about getting results from SQL in efficient way. I have created VIEW, and use PROP:SQL to send SQL Select statement.

View:Partner{PROP:SQL} = 'SELECT SUM(Amount) Amount' & |
' ,[dbo].[Debt].[Partner]' & |
' ,[dbo].[Company].[Naziv]' & |
' FROM [dbo].[Debt] INNER JOIN [dbo].[Company]' & |
' ON [dbo].[Company].IDPartner = [dbo].[Debt].Partner' & |
' WHERE Type = 1 AND [dbo].[Debt].Company = ' & Company_ID & |
' GROUP BY [dbo].[Debt].Partner, Name' & |
' ORDER BY Name'

And result is sometimes couple thousand lines. In help it is stated that I should loop thru VIEW with NEXT(), but when there is a lot lines it takes a time.

Is there any other way to LOOP faster-efficient way thru results?

Clarion APP generator creates:

BRW1 CLASS(BrowseClass)
Q &Queue:Browse:1
Init PROCEDURE(SIGNED ListBox,*STRING Posit,VIEW V,QUEUE Q,RelationManager RM,WindowManager WM)

Which is very efficient. It does not get all the results, instead it goes line by line when it is needed.

Is there any way that I can apply my VIEW{SQL:PROP} to INIT() procedure? I have tried to look at the abbrowse.clw, but I could not figured it out.

Procedure INIT() is from line 629 to 651 in abbrowse.clw, and I could not figure it out where to go from that…

2

On SQL side create a View with you view :slight_smile: In Clarion, DCT - create Table that points to your sql view. APP - use your new table in browse!

3

Also, take a look at the BUFFER() command

4

The SQL that Clarion creates is usually pretty good. Unless you have a particularly odd query, I’d just let the browse do it’s thing. It creates a Cursor and a buffer and is pretty efficient.

5

Thanks for suggestions. I will try with SQL View.

6

best to use the buffer command as noted. a view on the back end is not going make much difference. it MS SQL Serve the buffer statement will make a significant difference, if set correctly then it will make far less round trips to the server.

as far as this " Is there any way that I can apply my VIEW{SQL:PROP}"
never tried but you can overload next, previous, and a couple others and load a browse or what ever from stored procedure