Efficient SQL test query or validation query that will work across all (or most) databases

user_29995 0
Viewed 154948

Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.

Many example queries I've seen are for MySQL and recommend using SELECT 1; as the value for the test query. However, this query doesn't work on some databases (e.g. HSQLDB, for which SELECT 1 expects a FROM clause).

Is there a database-agnostic query that's equivalently efficient but will work for all SQL databases?

Edit:

If there's not (which seems to be the case), can somebody suggest a set of SQL queries that will work for various database providers? My intention would be to programmatically determine a statement I can use based on my database provider configuration.

connection-pooling sql
14 Answers

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"
user_5217990

Just found out the hard way that it is

SELECT 1 FROM DUAL

for MaxDB as well.

user_57233840

For Oracle the high performing query will be 

select 'X' from <your_small_table> where <primay_key_coulmn> = <some_value>

This is from a performance perspective.

user_106754240
Related