If I use an Microsoft SQL Server as System Database and access it with the jTDS driver. Then I have many temporary files (jdtsXYZ.tmp) stored with clob and blob data in the temp folder. How can I avoid that they are created? Because it is obvious that the performance of storing such files is not as good as if the data is only held in memory.

asked 07.09.2016 at 09:25

SupportIvyTeam's gravatar image

SupportIvyTeam ♦♦
1.4k102118122
accept rate: 77%

edited 09.09.2016 at 09:24


You can configure the limit if a big clob or blob data is stored in a temporary file with the additional connection property lobBuffer which is set to "32768" (32kb) by default. Means by default every clob or blob data which is bigger than 32kb is stored as an temorary file. Our recommendation is to increase lobBuffer to 524288 (512kb) or even more, but not too big if you have not enough memory. Fore a good value which suits your needs you can look at the file size of the jdtsXYZ.tmp files already created.

For other performance improvements you could also increase the follwoing properties:

  • bufferMaxMemory default value 1024 in kb (1mb): Controls the global buffer memory limit for all connections (in kilobytes).
  • packetSize default 4096 or 512 in bytes: The network packet size (a multiple of 512).
  • bufferMinPackets default 8 packets (8 * packed size): Controls the minimum number of packets per statement to buffer to memory.

For more detailed description of the properties see jTDS driver options

Be careful to not increase this values too much, because you then need a lot of memory! Be aware that you have per default a maximum of 50 connections to the system database!

You can configure this additional connection properties with the "Engine Configuration" tool: Engine Configuration or directly in the serverconfig.xml:

<serverConfiguration>
    <systemDatabase>
        <driverName>net.sourceforge.jtds.jdbc.Driver</driverName>
        <connectionUrl>jdbc:jtds:sqlserver://...</connectionUrl>
        <username>...</username>
        <password>...</password>
        <property>
            <name>bufferMaxMemory</name>
            <value>102400</value>
        </property>
        <property>
            <name>lobBuffer</name>
            <value>524288</value>
        </property>
        <property>
            <name>packetSize</name>
            <value>4096</value>
        </property>
        <property>
            <name>bufferMinPackets</name>
            <value>32</value>
        </property>
    </systemDatabase>
</serverConfiguration>
link

answered 07.09.2016 at 09:29

Christian%20Strebel's gravatar image

Christian St... ♦
3.2k31338
accept rate: 88%

edited 07.09.2016 at 10:53

Hi Christian I think that you should include one more setting here: UseCursors.

TTYL, Peter

(18.09.2016 at 18:15) phochstr phochstr's gravatar image

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×14
×5

Asked: 07.09.2016 at 09:25

Seen: 14,025 times

Last updated: 19.09.2016 at 09:04