Query is taking too long

Ramon Quintana

7y2.4k

Hi you people,

I need to join two tables with only one condition that not to list records with NULLS values in a specific field in both tables, but my query in a never ending one, there is no compiled errors when i try to run it.

here my query:

after run this query it seem to be in a loop duplicating the data, i have to stop it.

Some recomend acction, or clause on it?

Thank..

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nirav Gandhi7y ago
Accepted answer
You have already mentioned “trans_type” in Inner join. so not need to write isnull() condition on where clause. By default, Inner join will eliminate NULL values.

Now please remove Isnull condition in “Where” clause and check the output.
I think, you will get not null values in result.

Please confirm.
+3
Ramon Quintana7y ago
Hi Nirav Gandhi,

Only changed

Select *,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F32
from [1RQ_BPCSQL1].[dbo].[Cuentas_Maestro]
INNER JOIN [1RQ_BPCSQL1].[dbo].[ProcesoReporteSQL] ON Cuentas_Maestro.trans_type=ProcesoReporteSQL.Trans_Type
where ISNULL(cuentas_maestro.trans_type,'') <> '' and cuentas_maestro.Numero_cuenta between ProcesoReporteSQL.F33 and ProcesoReporteSQL.F34 and ProcesoReporteSQL.trans_type <> ' '.

instead of:

ProcesoReporteSQL.trans_type IS NOT NULL.

and works:

+2
Jignesh Trivedi7y ago
you have use <> (not) in your query. As per the my knowledge, it take long time to execute. To execution of you query faster, try to remove this. And also don't do any data manipulation in where clause. here you have use ISNULL.
make sure, you have provide enough information when joining table. It may be possible of cross-join happen and data may be duplicate or triplicate.
Hope this will help you.
+3
Amit Gupta7y ago
It might contains huge records which obviously takes more time to fetch the data. In addition to what @Anoop said, try to fetch data in chunks (as user won't saw thousands of records at a time which is not a optimal solution.
+2
Ramon Quintana7y ago
For example:

I execute this query and it listed some records:

But i execute the same query with no conditions in the WHERE clause but only list the NOT NULLS records:

and NOTHING HAPPENS just the query is an never ending one.

Why?
+2
Anoop Kumar Sharma7y ago
Hi Ramon,

I will recommend you to check the Execution plan for your query which will help you to find the root cause or issue in your Query.

Check below link for more details:
https://solutioncenter.apexsql.com/how-to-optimize-sql-server-query-performance/

Hope this will help you.
+3