How to choose the right cursor type in embedded SQL calls

Flex2SQL for Microsoft SQL Server allows developers to use either client-side or server-side cursors in their embedded SQL queries. In Flex2SQL Version 15, we significantly improved these queries' performance, so your DataFlex app can conduct embedded SQL queries even more quickly.  

Choosing between server-side and client-side cursors  

In most cases, we recommend you use server-side cursors when performing embedded SQL queries within your DataFlex application(s).  That's why, even with Flex2SQL 15's enhanced client-side cursor performance, we continue to use server-side cursors as our default.

Over the years, we've found most developers use embedded SQL calls to off-load complex processing to their database server and don't need to return large result sets to their program. Because server-side cursors send data to the client only as that data's requested, they're a perfect fit for these types of queries. Server-side cursors fetch data row-by-row, allowing them to deliver small result sets quickly while also conserving your network bandwidth.

However, when you need to work with large data and result sets, such as when you're assembling data for large reports, client-side cursors provide a significant performance boost.  For example, in our own tests, we found that fetching the same 50,000 record result set was five times faster using a client-side cursor query than a server-side cursor query.

Flex2SQL allows you to dynamically set your SQL query's cursor type so that, when you're working with large data sets and want to boost your query's performance, you can use client-side cursors, and when you don't want to overwhelm your network, you can use server-side cursors.

To learn more about when you might want to take advantage of client-side cursor searches, watch the below clip from our Flex2SQL Version 15 webinar:

If you haven't already, be sure to upgrade to Flex2SQL Version 15, so you can take advantage of our improved client cursor Microsoft SQL Server queries when you need them.