SQL Query Continues Running for a Very Long Time If Search Term Not Found

Issue with MySQL query taking a long time

Add an index to the entities.ticker field.
Indexes drammatically speed up query execution time if they are part of the where clause.

Does my sql query continue executing after i been disconnected due to long operation?

It does. As Mustafa mentioned, you can see the query still running if you look at "Administration tab" --> Management --> Client Connections.

Also good to remember that you can change the 30sec cap to longer, shorter or none.

How to kill/stop a long SQL query immediately?

What could the reason be

A query cancel is immediate, provided that your attention can reach the server and be processed. A query must be in a cancelable state, which is almost always true except if you do certain operations like calling a web service from SQLCLR. If your attention cannot reach the server it's usually due to scheduler overload.

But if your query is part of a transaction that must rollback, then rollback cannot be interrupted. If it takes 10 minutes then it needs 10 minutes and there's nothing you can do about it. Even restarting the server will not help, it will only make startup longer since recovery must finish the rollback.

To answer which specific reason applies to your case, you'll need to investigate yourself.

Slow Query Execution: Takes long time to execute = Very Strange Why?

As pointed out by the posts, adding an index (on the date field) immediately fix the problem. I will monitor this but for now marking this post as "Answered".

SQL Statement with the same multiple subqueries running very slow

It would seem your query should boil down to the following, does this work for you and perform better?

select
    t1.ID, t1.A, t1.B,
    IsNull(t1.c, t2.c) C,
    IsNull(t1.d, t2.d) D,
    IsNull(t1.e, t2.e) E,
    t1.f, t1.g, t1.h, t1.i
from TABLE1 t1
outer apply (
    select Max(c) c, Max(d) d, Max(e) e
    from TABLE2 t2
    where t2.Id = t1.Id
)t2

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

Running the same query causing error the second time

You need to open and close the connection after each query execution.

And also return the OracleDataReader after you have closed the connection or else it would lead to memory leak. If you return the OracleDataReader before you close connection, you would get the same error.

Try something like this:

public OracleDataReader ExecuteReader(string SelectQuery, string conString)
        {
        try
        {
        OpenDbConnection(conString);
        OracleCommand cmd = new OracleCommand();
        con.Open(); 
        cmd.Connection = con;
        cmd.CommandText = SelectQuery;
        cmd.CommandType = System.Data.CommandType.Text;
        OracleDataReader ora_dataReader = cmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        Logging.LogMessage(Logging.LogLevel.Error, 0, "DAL", this.GetType().Name, ex.Message + " : " + ex.StackTrace);
        throw ex;
    }
    finally
    {
     con.close();
     con.Dispose(); 
    }

        return ora_dataReader;
}

More info in this reference: https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledatareader(v=vs.110).aspx

---

Related Topics