Stored procedure for delete from parameter - (Moved)

[KORN]

Hi everyone
        This the first my topic. I apologize in advance for my English. My english is not good. I'm Thailand.
So I try to find solution for programing by call stored procedure method "DELETE" on SQL 2012 database .
But I can't find a concept or idea, so I want to ask everyone if anyone has done it.

The source is query from autoit  to delete SQL with parameters.

But in the destination SQL database. I don't know how to do in stored procedure.For example, how to get parameters from autoit?
How to create stored procedure for concept that I need. Please guide me if anyone can . Thanks in advance

 

[Melba23]

Moved to the appropriate AutoIt General Help and Support forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[Lepes]

Create a IPAdress table in your Database

Create in AutoIt a insert row on IPAdress:  insert into IPaddress(.....

Your store procedure uses this IPAdress Table to get what you need.

Edited November 25, 2022 by Lepes

[KORN]

Hi Sir ,

In the variable already has a value. When i run this script it can delete where by IP = 10.xx.xx.xx.
But IP :10.xx.xx.xx there are hundreds of rows of data.

So I thought if writing a store procedure would it make the query more efficient or not? Or am I wrong, it's not different

[Lepes]

a SQL to delete rows is handled by your Database, if you run from AutoIt, it only add extra time for creating a connection to the database and stablishing that connection.

The performance of the deletion only matters on indexes of the table, how many rows affected it has and finally, to rebuild those indexes of the table where deletion take effects.

You shouldn't have better performance launching a Delete SQL inside or outside.

 

Maybe you need to take a look to your Database Manager System Documentation to know how to improve a "batch operation".  In my opinion "hundreds of rows is nothing", if it is envolved Thousand of rows... then it could be better a "batch delete", disable indexes prior that deletion... I'm not sure how your Database manages this...

Edited November 25, 2022 by Lepes

[Zedna]

$IP_list = '10.0.0.1,10.0.0.2,10.0.0.3'
$IP_list = "'" & StringReplace($IP_list, ",", "','", 0,1) & "'" ; enclose all values in apostroph

$SQL = 'delete from table where column in ('&$IP_list&');'
_SQLQuery($oConn, $SQL)

 

Edited November 26, 2022 by Zedna

[Lepes]

I didn't want to talk about the performance of the SQL itself, I think it is a different topic.

The "IN" operator maybe not efficient on some DBMS , not sure on SQL 2012 for hundreds of rows.

A different approach could be the one I said on the first reply, once again, not sure about SQL 2012:
 

DELETE FROM data_client_table where (IPAdress = ANY (SELECT IpAdress FROM TABLEIPADDRESS))

Some DBMS optimizes this such of subquerys and the "ANY" operator, others one optimizes the "IN" operator. That's why I suggest to search on DBMS Documentation, suggestions on better practices and things like that.

 

I forgot to mention contrains integrity rules, I mean, if the deletion of a row on data_client_table propagates a row deletion on another tables, that could be an important slowdown of the SQL execution. There is nothing about Autoit performance launching SQL statement.

I think that unknowing tables definition, indexes, how many rows they have, etc, It is imposible to give the best answer. I love the fact that we can give differents approach and he can test wich one fits its needs.

Cheers!

[KORN]

I have learned more that we can't adjust anything on the client about code. Because in the end, all query must be queued in database and completed in a 1-1 queue, 1 queue, complete the next queue, continue -complete next...

Thanks for all the advice.