How can I improve the performance of the next query? ... it takes too long to get the result

Question

How can I improve the performance of the next query? ... it takes too long to get the result

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

When I run the following query, the results take a long time. If I execute the two queries separately, the result is returned immediately. How can I improve it?

Select Carpeta, Estado
from SCANAPP_JOB_SIMCE with(nolock) Where Nivel = '0M' and TipoPROC = 
'CEN' and TipoQUEST = 'LTR'
and Carpeta not in
(
    Select Distinct JobName from 
    VMEFLOW01.SIMCE_2018_2MCen_Salida.dbo.PC_Base2 with(nolock)
)

sql

asked by Guillermo E. Grillo 08.11.2018 в 14:26

source

2 answers

Problem with Hover effect with CSS in HTML How to animate an element with CSS every time it is clicked?

score 3 · Answer 1

Usually, when you must bring several data from a table in a linked server, you get better performance using OPENQUERY instead of SELECT * FROM LinkedServer.DB.dbo.Table . This is because the first option makes a single connection to the linked server, executes the query there, and brings the results; instead the second form makes a connection per row obtained.

I would try using a temporary table or directly OPENQUERY :

SELECT  Carpeta,
        Estado
FROM dbo.SCANAPP_JOB_SIMCE 
WHERE Nivel = '0M' 
AND TipoPROC = 'CEN'
AND TipoQUEST = 'LTR'
AND Carpeta NOT IN
(
    SELECT JobName
    FROM OPENQUERY(VMEFLOW01,'SELECT DISTINCT JobName 
                              FROM SIMCE_2018_2MCen_Salida.dbo.PC_Base2
                              WHERE JobName IS NOT NULL') x
)
;

Or with temporary table:

SELECT JobName
INTO #Temp1
FROM OPENQUERY(VMEFLOW01,'SELECT DISTINCT JobName 
                          FROM SIMCE_2018_2MCen_Salida.dbo.PC_Base2
                          WHERE JobName IS NOT NULL') x
;

SELECT  Carpeta,
        Estado
FROM dbo.SCANAPP_JOB_SIMCE 
WHERE Nivel = '0M' 
AND TipoPROC = 'CEN'
AND TipoQUEST = 'LTR'
AND Carpeta NOT IN
(
    SELECT JobName FROM #Temp1
)
;

score 0 · Answer 2

Try this: Select Carpeta, Estado from SCANAPP_JOB_SIMCE with(nolock) INNER JOIN ( Select Carpeta as JobName from SCANAPP_JOB_SIMCE with(nolock) Where Nivel = '0M' and TipoPROC = 'CEN' and TipoQUEST = 'LTR' Except Select Distinct JobName from VMEFLOW01.SIMCE_2018_2MCen_Salida.dbo.PC_Base2 with(nolock) )t On Carpeta = JobName