RazorSQL Performance Tuning Guide
The four main areas that affect RazorSQL performance tuning are available memory, network speed, the amount of network calls, and the speed of the machine on which RazorSQL is running. Listed below is information about how to adjust memory and network settings to get the maximum performance out of RazorSQL.
Memory Settings
RazorSQL ships with specific minimum and maximum memory settings suitable for a wide range of machines. However, for machines with large amounts of memory, these settings may be too low if working with large data sets. For example, if running queries that return hundreds of thousands of rows, the default RazorSQL memory settings may not be large enough to be able to retrieve and display that amount of data.
For Windows and Linux machines, RazorSQL ships with "himem" files that can be used to launch RazorSQL with increased minimum and maximum memory settings. These files are located in the base RazorSQL installation directory. To launch RazorSQL using the "himem" files, the "himem" files can be renamed to the name of the normal RazorSQL launch file. Listed below is information for how to do this on the operating systems supported by RazorSQL.
Windows
Launch File: <RazorSQL Install Dir>/razorsql.exe
Himem File: <RazorSQL Install Dir>/razorsql_himem.exe
Instructions: Rename razorsql.exe to razorqlexe.orig. Rename razorsql_himem.exe to razorsql.exe
Example: Rename C:\Program Files(x86)\RazorSQL\razorsql_himem.exe to razorsql.exe
macOS / Mac OS X
NOTE: Mac memory settings can no longer be easily adjusted. The RazorSQL application on Macs is digitally
signed and has Apple Gatekeeper security enabled. Modifying anything in RazorSQL.app will invalidate
the signature / Gatekeeper security and may prevent RazorSQL from launching.
NOTE: For older verisons of RazorSQL, memory settings can be adjusted via the Help -> Adjust Memory Settings menu option.
Linux / Unix
Launch File: <razorsql install dir>/razorsql.sh
Himem File: <razorsql install dir>/razorsql_himem.sh
Instructions: Rename the existing razorsql.sh file and rename the razorsql_himem.sh file
to razorsql.sh. Launch RazorSQL using a terminal window (sh razorsql.sh) or if supported
by your distribution, double-click the razorsql.sh file to launch.
Network Settings
There are two main buttons / options for executing queries with RazorSQL. These are the Execute SQL option, and the Execute SQL Fetch All Rows option. The Execute SQL option retrieves rows from the database in batches. As the user scrolls down through the results, more rows are retrieved. The Execute SQL Fetch All Rows option retrieves and displays all rows from the query, however, behind the scenes it may need to make multiple network calls to get all of the data.
The main factor that impacts performance of the Execute SQL and Execute SQL Fetch All Rows option is the size of the batch the database uses to return rows to RazorSQL. The higher the size of the batch, the fewer network calls RazorSQL needs to make. RazorSQL uses the default setting for the particular database for the batch size for the Execute SQL option. This is typically 100. If using the Execute SQL option to retrieve large result sets, increasing the size of this value can drastically improve performance.
To increase the batch size for the Execute SQL option, in the Preferences window on the Database / SQL tab, enter a value for the field Execute SQL Fetch Size. For most machines, a size somewhere between 1,000 and 10,000 should lead to performance gains when running large queries that require the user to scroll through many results.
The Execute SQL Fetch All Rows options tries to do some adjustments to the batch / fetch size setting to increase performance. For certain databases, if the user has not specified a value for the Fetch All Rows Fetch Size preference, RazorSQL will use a fetch size of up to 2000 for the Execute SQL Fetch All Rows options. However, this may still be too small of a batch size, especially for queries returning a very large result set. To set a custom value for the fetch all rows fetch size, enter a value in the Preferences window on the Database / SQL tab for the Fetch All Rows Fetch Size option.
Limiting Results
To lessen the likelihood that a query will be executed that returns too much data that can be displayed given the amount of memory available to RazorSQL, users can set a limit on the number of rows RazorSQL will return for a given query.
To set a limit on query results, open the Preferences window, select the Query Results tab, and enter a value in the Max Rows to Retrieve Per Query field.
