locked
[LINQ to SQL] Stop linq query RRS feed

  • Question

  • Hi,
    I'm doing a address search application, and I want that as soon as the user types, the program starts filtering the  address and displaying the matches. The problem is that I'm dealing with a 260K records SQL database, and the querys take up to 3 second to execute.
    The ideal were to run the query on a working thread, but being able to stop if it was still querying the DB and the user typed any more stuff, so to issue the new correct query.
    Is this possible with LINQ to SQL?
    Wednesday, January 9, 2008 3:15 PM

Answers

  • Here's a thought... if you don't care about the results from the first query, why do you care about stopping execution?

     

    Simply issue the new query and stop listening for the result of the original query.

     

     

    Ok Ok, DB resources are precious.  Maybe there's some other things to try.

    DataContext has a Connection property, so you can get the Connection.

    DataContext also has a constructor which takes an IDBConnection, so you can pass your own in.

     

    So, if you're feeling very bold, you could ask the connection to dispose itself during query execution (I have no idea what that would do - both on the C# side and on the database side).

     

     

    Another way to go would be to get the connection to tell you what spid it's running as

    The SQL is  

    SELECT @@SPID

     

    With this information, you could establish connection #2 and issue KILL on that SPID during query execution on connection #1.  I believe the database will rollback any transaction on Connection#1 and then C# will see a SQL Exception.

     

    These may not be the correct solution for your purpose, but maybe they will encourage others to do deeper research on the "right" way to accomplish this behavior.

    Wednesday, January 9, 2008 7:36 PM

All replies

  • No! LINQ to SQL has no mechanism to do this. Well! May be in your scenario, considering the number of records in the databse, it would be more suitable if you pre-fetch and cache all your addess strings in a suitable data-structure in your application, do in memory searches, and avoid querying your database again and again.
    Wednesday, January 9, 2008 4:31 PM
  • You could probably get a faster query if you were to limit the results to small number of rows using Take(n). 

     

    Wednesday, January 9, 2008 4:43 PM
  • Here's a thought... if you don't care about the results from the first query, why do you care about stopping execution?

     

    Simply issue the new query and stop listening for the result of the original query.

     

     

    Ok Ok, DB resources are precious.  Maybe there's some other things to try.

    DataContext has a Connection property, so you can get the Connection.

    DataContext also has a constructor which takes an IDBConnection, so you can pass your own in.

     

    So, if you're feeling very bold, you could ask the connection to dispose itself during query execution (I have no idea what that would do - both on the C# side and on the database side).

     

     

    Another way to go would be to get the connection to tell you what spid it's running as

    The SQL is  

    SELECT @@SPID

     

    With this information, you could establish connection #2 and issue KILL on that SPID during query execution on connection #1.  I believe the database will rollback any transaction on Connection#1 and then C# will see a SQL Exception.

     

    These may not be the correct solution for your purpose, but maybe they will encourage others to do deeper research on the "right" way to accomplish this behavior.

    Wednesday, January 9, 2008 7:36 PM
  • No! LINQ to SQL has no mechanism to do this. Well! May be in your scenario, considering the number of records in the databse, it would be more suitable if you pre-fetch and cache all your addess strings in a suitable data-structure in your application, do in memory searches, and avoid querying your database again and again.

    hi! yes, it is so. i've experienced this problem too, but in my case i worked with list of files.
    so, i thought of a simple (not pretty) way to interrupt Linq query execution, here is simple example:

    //for example we need to stop query after iterating to element with value "3":
    var arrayOfInt = new int[] {1,2,3,4,5};
    Func<bool, int> checkCancel = (val) => {
      if(val == 3) throw new Exception("Linq query is interrupted!");
      return true;
    };
    try{
      var results = from val in arrayOfInt
        where checkCancel(val)
        select result;
    } catch {
      //query was interrupted
    }

    nya-nya!
    Wednesday, December 9, 2009 7:52 PM