CODEWITHSUNDEEP
javasql-server-2008validationconnection-pooling
richarbernal
richarbernal

Reputation: 1059

What's the quickest validation query from a jdbc-pool to a specific table in SQL Server 2008

I usually use SELECT 1 as my preferred validation-query from tomcat jdbc pools, because it returns only one row with the result 1 that is very faster, but today I've found one terrible mistake:

My database only has one table with its primary key and is not nullable. This table sometimes is dropped, and then appears again by application circumstances. And that's the problem, SELECT 1 validate connection to database because it's already up but the table is missing so I get a terrible exception.

So, the solutions passes by finding one validation-query against the only table that exists in the database. And also, I need the query to be as fast as possible, because the performance in the application is one of the main objetives.

You can answer than an obvius query may be SELECT 1 FROM THE_TABLE but this query returns 1 for each row that the table has, and that's not very quick.

So, what could be the faster validation-query to this table??

EDIT

If I need to return at least one result, How should be the validation-query?
I ask this because some pool implementations, like commons-dbcp doesn't accept a query without results as a validated query.

Upvotes: 3

Views: 2291

Answers (1)

mellamokb
mellamokb

Reputation: 56779

How about this, which should validate the table exists without actually loading any data by pulling 0 rows and no actual columns.

SELECT TOP 0 1 FROM THE_TABLE

Demo: http://www.sqlfiddle.com/#!3/c670b/3

There are also built-in ways to check for the existance of objects in SQL SERVER. Here are two examples that do effectively the same thing.

select count(1) from information_schema.tables where table_name = 'THE_TABLE'
select OBJECT_ID('THE_TABLE') is not null

Upvotes: 4

Related Questions