How to increase SQL record request?

[Fots]

We are doing some tests with Clarion 5.5 and MSSQL 2008 and encountered some problems with the speed of requesting records out of a table (amount of records is approximately 15000).
We are working in a client/server environment.
This is our code:

OPEN(file_name)
file_name{PROP:SQL} = 'select * from file_name where file_name.datum > x'
LOOP until EOF(file_name)
NEXT(file_name)
ADD_TO_VIEW
END
CLOSE(file_name)

This code is processed much faster with a TPS-file than with an SQL-table.

Furthermore we tried to use the following after opening the file to increase the speed:
BUFFER(file_name,10,2,2,300)
But then we get the error 'File not found'. The program also hangs after this message.

Can you tell us what the problem is?

Are there other possibilities to increase the speed?

 

[ShankarJ]

Hi!

Forget the Buffer unless you are using the ODBC driver and not the MSSQL driver.

Lose the EOF() as it affects performance and change your code to ::

OPEN(file_name)
file_name{PROP:SQL} = 'select * from file_name where file_name.datum > x'
LOOP
    NEXT(file_name)

    IF ERRORCODE() = 33
       BREAK
    ELSIF ERRORCODE()
       MESSAGE(CLIP(ERRORFILE()) & ' : ' & ERROR() & ' [' & ERRORCODE() & ']|' & 'File Error : ' & FILEERROR() & ' [' & FILEERRORCODE() & ']','E R R O R - NEXT()')
    END

    ADD_TO_VIEW
END
CLOSE(file_name)

If you are using C6 you could always use the TURBOSQL driver string to make generic queries.

Regards

 

[Fots]

Hi,

Is the buffer not working with MS SQL?

regards

 

[ShankarJ]

Hi!

You are using an older version of Clarion and I do not know for sure how the BUFFER() statement works for that version. AFAIK, C6 was the version in which SQL access matured in Clarion. In my tests a long time back, I had issues with the BUFFER() statement when used in conjunction with the MSSQL driver and although I did not have issues with the ODBC driver it did not seem to make a difference in performance for the SQL server.

I guess you will have to test it out for yourself to see if BUFFER offers benefits in the version you are using.

Regards