question

RizalValry avatar image
0 Votes"
RizalValry asked MelissaMa-msft edited

HELP: Load query execution can't stop

I Have 2 table as following :

131724-dump-trx.png
131782-file-scan.png

  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

131726-infinite-load-query.png

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


sql-server-generalsql-server-transact-sql
dump-trx.png (38.5 KiB)
file-scan.png (24.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft converted comment to answer

Hi @RizalValry,

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

on design table :
131738-dump-execute-varchar.png
131739-filescan-waktu-varchar.png

dump_trx.execute_date as varchar
file_scan.waktu_scan as varchar


expected From varchar convert to -> date(mm/dd/yyyy)

I want to convert to datetime so it can be read as date

but I found error when convert that's



0 Votes 0 ·

I Found Solution :


SELECT
DATEDIFF(DAY,TRY_CONVERT(DATETIME,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=file_scan.id


Horee, success :)

Thanks a lot for your help @MelissaMa-msft

0 Votes 0 ·

Hi @RizalValry,

Thanks for your comment.

Glad to hear that my suggestions are helpful to you.

Please kindly remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft converted comment to answer

Hi @RizalValry,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, but I Have Problem after run query :

131812-error-datetime.png


0 Votes 0 ·
error-datetime.png (32.9 KiB)

My Expect is

131706-my-expect.png

Hi @MelissaMa-msft , Can help me please?

0 Votes 0 ·
my-expect.png (11.0 KiB)
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @RizalValry,

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.