Break a Query Possible ?

[Efo74]

Hello Autoit Team,

I'm looking for a method to block a running query. I would like with a Hotkey Block the execution of a line. I don't want to be written the code to do, I just want to know what kind of approach needs to be taken to solve this problem. Inside the Hotkey function there should be a line break instruction in current code, so the program should not terminate, it should continue. It's possible ?

I Use Mysql UDF and the query line is like: $aResult = _EzMySql_GetTable2d($sQuery_Func)

 

Thank you. 

[mLipok]

Only ADO.

 

[Efo74]

Thanks, I try to find out how to do it with ADO.

 

[mLipok]

But Why you need this ?

Because maybe I misunderstood you.

Overall, you would have to implement Transactions.
Of course, if the transaction is complete, you won't be able to break anything.

 

[Efo74]

Mine was meant to be an experiment, I wanted to try to end the running query by pressing the ESC key. For example, if the query lasts more than 3-4 seconds, I have a table in a database with about 20,000 records every year, so in 5 years I expect to have 100,000 records.

[mLipok]

10 hours ago, mLipok said:

Only ADO.

I just read some about 

 

This would be also able to do with:

https://www.mysqltutorial.org/mysql-transaction.aspx/

but it is related to runing EXECUTE asynchronically and I'm no sure if EzMySql is able to do this.

 

With ADO it is possible. I just added support for ASYNC to my dev version of ADO.au3 if you want to check my dev version just PM me, after test we will back to open forum.

 

 

[mLipok]

Take a look also here:

This is a good video about concept how to use ADO asynchronously.

[Skysnake]

Perhaps you are using the wrong tool to solve this problem?

Most users have no idea how much data is involved in a query. Timeouts (both session / connection and query time outs) can be set. 

Generally the SQL query will return very quickly and the delay occurs during the AutoIt processing / display, which is genrally done in a loop. Simply build the break check into the AutoIt loop.

 

 

 

[jchd]

3 hours ago, Efo74 said:

so in 5 years I expect to have 100,000 records

A well-written moderate to somehow complex query on such a small table should take well under one second, provided the DB schema is appropriate. Even with hogs like MySQL it should never be that slow.

[Efo74]

Thanks everyone for your help, as Skysnake said, the query shouldn't take too much of the system(But, will it always be the same even with 100,000 records?), the slowdown comes with the display in the listview. I have just provided the software to the client, and I have seen that he enters about 2000 records per month. The query is read only, the user by setting filters significantly reduces the number of records displayed. I wanted to provide the user with a tool that allows him to stop a query started "by mistake" without filters, and therefore with many records to display.

[jchd]

Add a LIMIT n clause to your query, so that at most n rows are returned and fed to the ListView.

Since it's the time spent filling the listview with a large number of rows which can be an issue when unsufficient filtering is setup, the problem lies in the code filling of the ListView, hence in your own AutoIt code instead of the SQL query. Besides, a listview with thousands of rows is essentially unmanageable. Using a reasonnable n, e.g. LIMIT 200, clause would probably do.

[Efo74]

I agree with the use of the LIMIT clause in the query, a value of 2500 elements would allow you to always have 1 month data on hand.

[dmob]

16 hours ago, Efo74 said:

 the slowdown comes with the display in the listview.

You could try _GUICtrlListView_AddArray, in some cases its quicker, assuming yr data is in an array.

Edited February 23, 2021 by dmob
Add

[mLipok]

17 hours ago, Efo74 said:

the slowdown comes with the display in the listview.

On the forum is few topics about speed with filling listview, you sould do a research.

  

17 hours ago, Efo74 said:

query started "by mistake" without filters, and therefore with many records to display.

Change you GUI to provide predefined filters for TODAYS day.

User should be focused on changing them.