Programmaticaly cancel a SQL command sent to the server? 1

[huckfinn19]

Hi!

I am using .Net's C# language in an application that is using a SQL Server database. Some of the calls in my application to the DB take quite a while to handout the results and I would like to give my users the possibility to cancel the request if they think it's taking too long. When using the SQL Enterprise application, there is a cancel button that can be hit to stop the request in the middle of it's processing, so I guess it's also possible programmatically.

Therefore, I would like to know if someone could guide me on how to programmatically and asynchronously cancel a SQL command that was sent to SQL Server?

Thanks!

Huck

 

[donutman]

For a quicker and possibly better answer, post the question in the C# forum. It's clearly not a SQL Server question. EM is an application just like any other.
I believe there are ways to connect to the database so that the query can be run in the background. And you also can play with a timer control.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

You can use the kill command to stop the query that is running on the server. You'll have to know the spid of the connection that is running the long running query.

The better solution would be to optimize the long running query and tables so the query doesn't take so long to run.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLSister]

Ah Denny, you took the words right out of my mouth.

Poorly written T-SQL code is the number one cause of database inefficiency. I've seen estimates that over 70% of the database performance problems can be placed directly on badly designed T-SQL code and poor use of indexes. Most slow running queries can be fixed to run faster.

As a user, I don't want to cancel my request; I want it to run faster and stop wasting my time. If I need the information; I need it. Canceling gets me nothing unless the problem is too many users on the system and the network is bogged down. If the problem is the query itself, I will never find a better time to run it and get the answer.

Common causes of slow running queries
1. Wrong or missing indexes
2. Cursors
3. Code which does not allow the use of existing indexes (using like '%sometext%' for instance)
4. Returning more information than you actually need. This could be more records than you need or more fields than you need. Select * is almost always a an inefficent method to use, especially if you have a join.
5. Use of subqueries, IN statements or simliar constructs instead of joins or derived tables. Derived tables are also usually faster than subqueries.
6. Dynamic SQL and the poor programming practice of writing reuseable but inefficent code. Reusable code is only approprate if all the different times it is invoked the user needs the same information.
7. Use of nested views or the use of views at all in a system which is not running Enterprise edition (where the views themselves can be indexed).
8. Failure to use stored procedures which are usually the most efficent way to query a database.
9. Failure to use a where clause to limit data returned.
10. Poorly designed database structure. In particular if you find yourself using a lot of self joins or concatenation to get the answers you need or if you find yourself continually have to convert the dataype of the field in question.
11. Failure to archive old data that is not frequently accessed. Why query against 10 million rows when only 500,000 of them contain the data you want 90% of the time?

I'm sure there are more, but these are the common ones that leap to mind and all are fixable.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[jdgonzalez]

Very well put SQLSister.