Jump to content

Stored procedure for delete from parameter - (Moved)


Recommended Posts

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.

image.thumb.png.8d5bb5c930ed728141c39f20f6178db0.png

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

 

Link to post
Share on other sites
  • Moderators

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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to post
Share on other sites

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

Link to post
Share on other sites

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 by Lepes
Link to post
Share on other sites

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!

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...