plan - optimizar inner join sql server

Una consulta de prueba SQL eficiente o una consulta de validación que funcionará en todas las bases de datos(o la mayoría)

Question

Muchas bibliotecas de agrupación de conexiones de bases de datos brindan la capacidad de probar sus conexiones SQL en busca de inactividad. Por ejemplo, la biblioteca de agrupación JDBC c3p0 tiene una propiedad llamada preferredTestQuery , que se ejecuta en la conexión a intervalos configurados. Del mismo modo, Apache Commons DBCP tiene validationQuery .

Muchas queries example que he visto son para MySQL y recomiendan usar SELECT 1; como el valor para la consulta de prueba. Sin embargo, esta consulta no funciona en algunas bases de datos (por ejemplo, HSQLDB, para la cual SELECT 1 espera una cláusula FROM ).

¿Hay una consulta independiente de la base de datos que sea eficiente de forma equivalente pero que funcione para todas las bases de datos SQL?

Editar:

Si no hay (lo que parece ser el caso), ¿alguien puede sugerir un conjunto de consultas SQL que funcionarán para varios proveedores de bases de datos? Mi intención sería determinar de manera programática una declaración que pueda usar en función de la configuración de mi proveedor de base de datos.

Answer 1

Acabo de descubrir de la manera difícil que es

SELECT 1 FROM DUAL

para MaxDB también.

Answer 2

Después de un poco de investigación junto con la ayuda de algunas de las respuestas aquí:

SELECT 1

• H2
• MySQL
• Microsoft SQL Server (según NimChimpsky )
• PostgreSQL
• SQLite

SELECT 1 FROM DUAL

• Oráculo

SELECT 1 FROM any_existing_table WHERE 1=0

o

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

• HSQLDB (probado con la versión 1.8.0.10)

  Nota: Intenté usar una cláusula WHERE 1=0 en la segunda consulta, pero no funcionó como un valor para la validationQuery de DBCP de Apache Commons, ya que la consulta no devuelve ninguna fila

VALUES 1 o SELECT 1 FROM SYSIBM.SYSDUMMY1

• Apache Derby (vía daiscog )

SELECT 1 FROM SYSIBM.SYSDUMMY1

• DB2

select count(*) from systables

• Informix

Answer 3

Lamentablemente, no existe una instrucción SELECT que siempre funcione independientemente de la base de datos.

La mayoría de las bases de datos admiten

SELECT 1

Algunas bases de datos no son compatibles, pero tienen una tabla llamada DUAL que puede usar cuando no necesita una tabla:

SELECT 1 FROM DUAL

MySQL también admite esto por razones de compatibilidad, pero no todas las bases de datos. Una solución alternativa para las bases de datos que no admiten ninguno de los anteriores es crear una tabla llamada DUAL que contenga una sola fila, luego lo anterior funcionará.

HSQLDB no admite ninguno de los anteriores, por lo que puede crear la tabla DUAL o bien utilizar:

SELECT 1 FROM any_table_that_you_know_exists_in_your_database

Answer 4

Para MSSQL .

Esto me ayudó a determinar si los servidores vinculados estaban vivos. Usando una conexión Open Query y TRY CATCH para poner los resultados del error a algo útil.

IF OBJECT_ID(''TEMPDB..#TEST_CONNECTION'') IS NOT NULL DROP TABLE #TEST_CONNECTION
IF OBJECT_ID(''TEMPDB..#RESULTSERROR'') IS NOT NULL DROP TABLE #RESULTSERROR
IF OBJECT_ID(''TEMPDB..#RESULTSGOOD'') IS NOT NULL DROP TABLE #RESULTSGOOD

DECLARE @LINKEDSERVER AS VARCHAR(25)    SET @LINKEDSERVER = ''SERVER NAME GOES HERE''
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @OPENQUERY AS VARCHAR(MAX)

--IF OBJECT_ID (''dbo.usp_GetErrorInfo'', ''P'' ) IS NOT NULL DROP PROCEDURE usp_GetErrorInfo;  
--GO  

---- Create procedure to retrieve error information.  
--CREATE PROCEDURE dbo.usp_GetErrorInfo  
--AS  
--SELECT     
--    ERROR_NUMBER() AS ErrorNumber  
--    ,ERROR_SEVERITY() AS ErrorSeverity  
--    ,ERROR_STATE() AS ErrorState  
--    ,ERROR_PROCEDURE() AS ErrorProcedure  
--    ,ERROR_LINE() AS ErrorLine  
--    ,ERROR_MESSAGE() AS Message;  
--GO  


BEGIN TRY
SET @SQL=''
SELECT 1 
''''''
--SELECT @SQL
SET @OPENQUERY = ''SELECT * INTO ##TEST_CONNECTION FROM OPENQUERY([''+ @LINKEDSERVER +''],'''''' + @SQL + '')''
--SELECT @OPENQUERY
EXEC(@OPENQUERY)
SELECT * INTO #TEST_CONNECTION FROM ##TEST_CONNECTION
DROP TABLE ##TEST_CONNECTION
--SELECT * FROM #TEST_CONNECTION
END TRY

BEGIN CATCH
-- Execute error retrieval routine.
IF OBJECT_ID(''dbo.usp_GetErrorInfo'') IS NOT NULL -- IT WILL ALWAYS HAVE SOMTHING... 
    BEGIN
        CREATE TABLE #RESULTSERROR (
        [ErrorNumber]       INT
        ,[ErrorSeverity]    INT
        ,[ErrorState]       INT
        ,[ErrorProcedure]   INT
        ,[ErrorLine]        INT
        ,[Message]          NVARCHAR(MAX) 
        )
        INSERT INTO #RESULTSERROR
        EXECUTE dbo.usp_GetErrorInfo
    END
END CATCH

BEGIN 
    IF (Select ERRORNUMBER FROM #RESULTSERROR WHERE ERRORNUMBER = ''1038'') IS NOT NULL --''1038'' FOR ME SHOWED A CONNECTION ATLEAST. 
        SELECT
        ''0'' AS [ErrorNumber]        
        ,''0''AS [ErrorSeverity]  
        ,''0''AS [ErrorState]     
        ,''0''AS [ErrorProcedure] 
        ,''0''AS [ErrorLine]      
        , CONCAT(''CONNECTION IS UP ON '', @LINKEDSERVER) AS [Message]            
    ELSE 
        SELECT * FROM #RESULTSERROR
END

docs.microsoft.com

Answer 5

Para las pruebas que usan select count(*) , debería ser más eficiente usar select count(1) porque * puede hacer que lea todos los datos de la columna.

Answer 6

Qué tal si

SELECT user()

Yo uso esto antes. MySQL, H2 está bien, no conozco otros.

Answer 7

Si su controlador es compatible con JDBC 4, no hay necesidad de una consulta dedicada para probar las conexiones. En cambio, hay Connection.isValid para probar la conexión.

JDBC 4 es parte de Java 6 desde 2006 y tu controlador debería soportar esto ahora!

Famosos grupos de conexiones, como HikariCP, todavía tienen un parámetro de configuración para especificar una consulta de prueba, pero desaconsejan enérgicamente su uso:

🔠connectionTestQuery

Si su controlador es compatible con JDBC4, recomendamos encarecidamente no configurar esta propiedad. Esto es para bases de datos "heredadas" que no admiten la API JDBC4 Connection.isValid (). Esta es la consulta que se ejecutará justo antes de que se le proporcione una conexión del grupo para validar que la conexión a la base de datos aún está activa. De nuevo, intente ejecutar el grupo sin esta propiedad, HikariCP registrará un error si su controlador no es compatible con JDBC4 para avisarle. Predeterminado: ninguno

Answer 8

Suponiendo que el OP quiere una respuesta de Java:

A partir de JDBC3 / Java 6 existe el método isValid() que se debe utilizar en lugar de inventar el propio método.

Se requiere que el implementador del controlador ejecute algún tipo de consulta en la base de datos cuando se llama a este ID de método. Usted, como un simple usuario de JDBC, no tiene que saber o entender qué es esta consulta. Todo lo que tiene que hacer es confiar en que el creador del controlador JDBC ha hecho su trabajo correctamente.

Answer 9

Yo uso este:

select max(table_catalog) as x from information_schema.tables

para verificar la conexión y la capacidad de ejecutar consultas (con 1 fila como resultado) para postgreSQL, MySQL y MSSQL.

Answer 10

Yo uso esto para Firebird

select 1 from RDB$RELATION_FIELDS rows 1

Answer 11

yo suelo

Select COUNT(*) As X From INFORMATION_SCHEMA.SYSTEM_USERS Where 1=0

para hsqldb 1.8.0

Answer 12

select 1 funcionaría en el servidor sql, no estoy seguro de los demás.

Use el estándar ansi sql para crear una tabla y luego consultar desde esa tabla.