Well, the queries are taking a long time because they're searching through
8GB+ of database doing joins across multiple tables. The guy who did the
database design and indexing has gotten the database to the fastest point he
knows how. Any other indexes he's tried so far have actually SLOWED the
queries. The most interesting thing is that the index which sped things up
the most was on a field which isn't even a key.
Other queries exacerbate the issue, because to return the desired data they
MUST walk entire tables, so indexing doesn't buy anything there. (for
example, the 'list accounts' query.)
The main problem is that it's an interactive application where the database
can grow to immense sizes, and the most common searches can return huge
amounts of information. The first clue a user may have that they've
accidentally entered the wrong parameter (not invalid, just not what they
meant to) is when a search seems to be taking A LOT longer than usual.
(i.e.: you mean to run the search against items between 1/1/2001 and
2/1/2001, but accidentally run it between 1/1/2000 and 12/1/2001). We can't
limit the range, because we know there are perfectly valid reasons to do
that same search across 7 years or more, but they just aren't that common.
We've found a way to do it by going to asynchronous queries, but that
requires changes to quite a few pages, and that's something I'm hoping to
avoid if possible.
- Theo
-----Original Message-----
From: Svensson, B.A.T. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 3:10 PM
To: Brinkman, Theodore; [EMAIL PROTECTED]
Subject: RE: [PHP-WIN] Canceling a Query
Dear Brinkman,
First I wonder one thing: Why are the queries long running
in the first place? Might it be because improper indexing
of the attribute in the tables? Maybe it can be worth to
have alook at this to see if you can increase performance
this way. You might very well be able to speed up the
execution of your queries by rewriting them and/or
adding a few more new indexes to the table.
I know that within the SQL server one are able to track user
process, create new jobs, and delete jobs, and a thought around
this - which is untried by me - might be to uses these facilities
provided by SQL server, and build a kind of tracking system that
keeps track of the users queries with a kind of "ticket", when
a user sends a cancel request, you simply forward this cancel
message with the proper "ticket" to SQL server, and then you
lets the RDBMS do the killing of the long running process.
How ever I haven't done this my self, and don't even know if
it possible, but it might very well be worth in your case to
have a lock at it. In the SQL help, you might start out by
checking out sp_add_jobb and sp_delete_job.
Cheers,
/Anders
>-----Original Message-----
>From: Brinkman, Theodore
>[mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, May 16, 2001 5:45 PM
>To: [EMAIL PROTECTED]
>Subject: [PHP-WIN] Canceling a Query
>
>
>I'm working on an application for work where users will be querying the
>database to get information back about processed documents.
>I'm trying toset up a way for a user to cancel a query which is taking too
>long. I'm using MS SQL Server 2000. The interface for the cancel is
>easy enough, a form containing a button that says 'cancel' and a value or
set
>of values identifying the query being run.
>
>I've found 'KILL <SPID>', but the problem is that all the
>connections to the
>database are done through the web-server so they get the same
>SPID, which
>means that EVERY query being run by EVERY user gets killed.
>(Obviously not
>an acceptable solution.) Does anybody out there know of a way to
>specifically kill a single query/stored procedure? We're
>willing to track
>as many values as necessary to do it.
>
>Thanks.
>
> - Theo
>
>--
>PHP Windows Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail:
>[EMAIL PROTECTED]
>
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
