HELP: Load query execution can't stop

Question

I Have 2 table as following :

1. Table dump_trx a as much as 50.389 rows
   and
2. Table file_scan as much as 1004 rows

The query I run is as follows :

SELECT
DATEDIFF(DAY,CONVERT(varchar(30),dump_trx.execute_date, 102),file_scan.waktu_scan) AS SLA,
file_scan.waktu_scan,
dump_trx.norek_debit,
dump_trx.namarek_debit,
dump_trx.bank_tujuan,
dump_trx.norek_kredit,
dump_trx.namarek_kredit,
dump_trx.nama_perusahaan,
dump_trx.deskripsi,
dump_trx.execute_date,
dump_trx.id_perusahaan,
dump_trx.jenis_trx,
REPLACE(jumlah,',','') AS total
FROM vm.dump_trx, vm.file_scan

problem & question : when I run both queries at the same time, I have a problem.. Load query execution can't stop

What should the ideal query look like so as not to run into an infinite stack and load ?, please help me sir

Answer 1

Hi @Rizal Valry ,

What are the data types of both dump_trx.execute_date and file_scan.waktu_scan columns?

If both of them are date or datetime, you could refer below:

SELECT DATEDIFF(DAY,dump_trx.execute_date,file_scan.waktu_scan) AS SLA  
FROM vm.dump_trx left join vm.file_scan on dump_trx.id=file_scan.id  

In addition, I analyzed the data of your two tables, the join condition could be below which is better.

FROM vm.dump_trx left join vm.file_scan on dump_trx.id_file=file_scan.id  

You could check which one is better according to your own condition.

Best regards,
Melissa

Answer 2

Hi @Rizal Valry ,

Welcome to Microsoft Q&A!

Is there any column relationship between two tables?

You could have a try to add one join condition, for example, dump_trx.id=file_scan.id, otherwise you will receive around 50389*1004=50,290,556 rows which cause your query could not stop.

Please refer below:

SELECT  
DATEDIFF(DAY,CONVERT(varchar(30),dump_trx.execute_date, 102),file_scan.waktu_scan) AS SLA,  
file_scan.waktu_scan,  
dump_trx.norek_debit,  
dump_trx.namarek_debit,  
dump_trx.bank_tujuan,  
dump_trx.norek_kredit,  
dump_trx.namarek_kredit,  
dump_trx.nama_perusahaan,  
dump_trx.deskripsi,  
dump_trx.execute_date,  
dump_trx.id_perusahaan,  
dump_trx.jenis_trx,  
REPLACE(jumlah,',','') AS total  
FROM vm.dump_trx left join vm.file_scan on dump_trx.id=file_scan.id  

If above is not working, please provide your expected output.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Rizal Valry ,

Please refer below:

SELECT DATEDIFF(DAY,CONVERT(DATETIME,dump_trx.execute_date, 102),file_scan.waktu_scan) AS SLA  
FROM vm.dump_trx left join vm.file_scan on dump_trx.id_file=file_scan.id  

OR

SELECT DATEDIFF(DAY,CONVERT(DATETIME,dump_trx.execute_date),file_scan.waktu_scan) AS SLA  
FROM vm.dump_trx left join vm.file_scan on dump_trx.id_file=file_scan.id  

Best regards,
Melissa