Kbase P54288: General tips to improve performance for MS SQL DataServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/11/2010 |
|
Status: Unverified
GOAL:
How to improve performance when using MS SQL DataServer?
FACT(s) (Environment):
MS SQL DataServer
MS SQL
Progress 9.x
FIX:
Here are some general, brief tips for improving performance with MS SQL DataServer.
For more information and recommendations, the Online documentation and PSDN whitepapers are a good resources. Please see solution P38892 entitled "Where can I get some documentation to performance tune MS SQL server DataServer"
TIPS FOR TUNING YOUR 4GL CODE
**************************
1) Use the MS SQL Server profiler tool to determine which part of your process is taking the most time.
2) Use FIELD LISTS as it reduces the amount of data being passed between client and Database
3) Try and replace and FIND statements (i.e. replace FIND FIRST with FOR FIRST). The DataServer allows applications that access MSS data sources to imitate Progress cursor behavior for FIND cursors. FOR EACH and OPEN QUERY statements do not retain cursor position against a FIND statement. Cursor management to emulate Progress commands and behavior like FIND can be quite 'performance expensive'.
4) Modify queries so that joins are performed on the server and not the client.
5) Recreate the 'process' as a native MSS stored procedure. If all the tuning still does not result in the expected performance it may be necessary to write Database dependant code (i.e. stored procedures).
TIPS FOR TUNING YOUR ENVIRONMENT
*****************************
1) Make sure you have the latest Progress DataServer version and associated service pack
2) Check the following start-up parameters:
a) "-Dsrv skip_schema_check" :
This parameter is good for a deployment environment where schema
holder and target database field definitions are stable. Unless
this parameter is specified, DataServer checks that the data
definitions match the schema definitions of the schema holder when
r-code is run (each time a table, view, or buffer is opened).
b) "-Dsrv PRGRS_MAX_BLOCKSIZE"
SP5 for 9.1D switch to put an upper limit in the memory accumulated
for block cursors in your session. The default is 1048576 bytes. If
your typical results set exceeds 1MB of 'space', increasing this
parameter will help for Read Only queries.
3) Upgrade the hardware of the MS SQL Server machine (CPU & Memory)
PROGRESS PROFESSIONAL SERVICES
***************************
Progress professional Services have a 4GL source-code tool which can be used to evaluate how 'DataServer' friendly your existing 4GL code is. This information can be helpful in determining the amount of work needed to adopt the DataServer technology if you are finding that the 'out of box' performance is unacceptable.
For more information on our consultancy service, please see http://www.progress.com/consulting/.