SQL Server OLE DB - performance tweaks

General discussion forum about all databases other than SqlBase.
User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 325
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

SQL Server OLE DB - performance tweaks

Post by Steve Leighton » 04 Jul 2019, 03:41

If you are struggling with performance reading in large result-sets via SQL Server OLE DB, and assuming your indexes are all set, I have found the following HUGELY improved things.
No doubt, not all will agree, but here's a suggestion you could try:

In your connection string try adding one or both of the following settings:
;CLIENTCURSOR=TRUE;SSPROP_INIT_PACKETSIZE=8192
I couldn't believe the difference in response times.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

RainerE
Germany
Posts: 2183
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: SQL Server OLE DB - performance tweaks

Post by RainerE » 18 Aug 2022, 16:44

Hi Steve,

"Extended Properties="CLIENTCURSOR=TRUE;" will increase performance,yes.
But I had problems with large recordsets and decided to remove this setting, which I had added because of your hint.
See thread viewtopic.php?f=6&t=73639 for details.

Rainer

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 2 guests