Plex 2E

Using .NET Client & Server - SQL Server 2010

When trying to optimize some of our screens (over large record sets) we noticed that using the regular BlockFetch/BlockfetchSet that some 'queries' over SQL were taking several seconds and using SQL trace - to see where the performance hit was.

Trying to use the UseRecordsetSize option to pass in the number of Rows to fetch - increases performance - but then Prevents the paging of the screen to fetch more rows - (as we have effectively reached the end of the record set)

Has anyone been able to use this option effectively to allow an initial fast response - but hen also allow paging through the record set beyond the Recordset Size ?

Maybe I am just missing something here - but this approach seems to not fit well.

if anyone has published  a 'Using plex with SQL'  Performance Tips & techniques  -that would be very handy !

Hi Wayne, Big big big topic and just completed a month compiling "Using plex with SQL'  Performance Tips & techniques" Please contact me offline.

Your initial question can be answered by first searching plex help for "StatelessBlockFetch"

This only works if the view you are fetching has a unique key. You are implementing a stateless environment where as conventionally the client has maintained state. For StatelessBlockFetch you need to pass in the key of the last record returned in the previous record set.

• What is your index strategy (creation and maintenance)
• Missing Indexes - Coverage?
• Position GT, GE and Fetch GT, GE  generate open-ended SELECT statements which give poor performance when run over large tables. Use Select Where, Position EQ, Fetch EQ, or Exec SQL instead.
• Virtual Attributes kill performance ( Conversely, if you do need the information, virtual attributes generally provide the most efficient method of obtaining it.)
• Select only required fields
• Don’t fetch 100 columns and use only one
• “SELECT * FROM”  is a not advised in your hand coded DML
• Evaluate Server Calls in Client functions - Think Tier Development
• Fetch 2 times the # of rows needed to fill a grid page, Return 200 rows if grid displays 100 rows on a page
• (Business entity.List attributes.Get sequential/FNC variable VAR/Output) as SYS Output   Occurs NBR   12   will kill performance!

“SQL Server is not like other databases. Very few switches and knobs are available to tweak performance. There are certainly no magic silver bullets to solve performance problems simply by changing an sp_configure setting…...Your time is best spent looking at performance from a workload perspective, such as database design, application interaction, and indexing issues.” Top 10 Tips for Optimizing SQL Server Performance, Written by Kevin Kline Technical Strategy Manager for SQL Server Solutions Quest Software

Thanks George - as I said I was missing something - I was converting an existing function so didn't even think of switching to a StatelessBlockFetch as I wasn't thinking SQL was stateless -

Got it working after switching  -

Many of your items below I had already considered - just seemed to forget the obvious !