Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mschilder
Starting Member
4 Posts |
 Posted - 2005-06-24 : 16:51:49
|
| I have a ms-sql query, modestly complex, joining about 5 or 6 different tables into 1 result set. I am running it in a java program, through a sql connection to my database. It generally returns only a handful of rows at any given time (in my testing, 2, generally it will be rarely more than a dozen). Usually, it runs in less than a second...blink and you're done. Occasionally, it takes in excess of a minute. When it takes a minute, it still works, I still get the same data, but yeah....over a minute. I have not been able to predict any pattern as when it will be fast, and when slow. The same query, one time, 1 second, next time 1 minute. Roll them dice, spin the wheel, I don't know the system, and it's never a medium time. Never 30 seconds for example. I also have many other queries, of similar complexity, reliably running on consistent time frames. Something about this query.What do I look for? There are 2 outer joins, 4 or 5 inner joins, 1 sub-select statement (I know, poor practice, but it's small and fast), a variety of column conditions, and an order by statement. Because the problem is only ever intermittent, it's sort of hard to decide if any change made a difference in this regard. In query analyzer, it is never slow...or I've just lucked out on those self-same dice rolls, but my application does hundreds of queries of all kinds through these connections. This is the only one that seems to flucture like this.This is my script. I am aware that it is not pure elegance, and I am thinking that the sub-select down in the where may actually be quite redundant now that i look at it, but it does work, and gives me what I need.select users.userid, users.given, users.surname, manager.given, manager.surname, milestone.status, milestone.responsible_id, milestone.status_date, managerappraisal.status , subject_self.status, manager.userid from ea_owner.user_info users, ea_owner.subject_milestone milestone, ea_owner.subject_milestone managerappraisal, ea_owner.user_role user_role, ea_owner.subject_assignment sa left outer join ea_owner.user_info manager on sa.manager_id=manager.userid, ea_owner.subject_assignment sa2 left outer join ea_owner.subject_milestone subject_self on sa2.subject_id=subject_self.subject_id and sa2.sid=subject_self.sid where sa.subject_id=users.userid and sa.subject_id=sa2.subject_id and sa.sid=sa2.sid and sa.sid=milestone.sid and milestone.subject_id=users.userid and ( subject_self.milestone_id=4 or subject_self.milestone_id is null) and sa.subject_id=milestone.subject_id and managerappraisal.subject_id=users.userid and sa.sid=managerappraisal.sid and managerappraisal.milestone_id = 1 and exists ( select 1 from ea_owner.subject_milestone where sid=milestone.sid and milestone_id=milestone.milestone_id and subject_id=users.userid) and sa.sid=79 and milestone.responsible_id=7 and milestone.milestone_id=3 and user_role.sid=subject_self.sid and user_role.userid=subject_self.subject_id and user_role.roleid=4 and milestone.status in ('I','A', 'P') order by upper(users.surname), upper(users.given) |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-24 : 17:44:00
|
| Hi,Without knowing the details of your schema, it's kind of difficult to analyze, but:Keep in mind that this query is most likely to involve a sort, an interrum table, and possible scans (again, without knowing the indexing, it's difficult to tell). On systems that make heavy use of tempdb, there can be queuing on the tempdb page allocation map, and that might be where (some of) your troubles lie. It appears that your server is case-insensitive, which I am assuming by the "UPPER" in your "order by" statement. This invalidates the use of an index on these columns (if it were to be used to sort). Outer joins generally hurt too.Also, how heavily involved are these tables in other updates that may be happening throughout the system? Cheers. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-06-24 : 17:46:40
|
| Maybe it is a locking issue.Use the NOLOCK hint in the query or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,see if this changes the behavior.rockmoose |
|
|
|
mschilder
Starting Member
4 Posts |
Posted - 2005-06-27 : 09:19:46
|
| It may be a locking thing, the tables involved are subject to many updates. What confuses me is that other queries, ... any other query really, never is affected by this lag, even queries on the same table. I seems unlikely that there is anything wrong with the tables, if other queries hitting those tables are never an issue. Certainly there are other queries using joins (inner and outer), and some using sub-selects (though for on general principle I have been trying to adjust those as I hit them). If it was just an table index issue...wouldn't other queries be affected the same way? The confusing part is the polarization of this problem. Either less than a second, or around a minute, and pretty much always one or the other (the long time varies a bit....55 to 65 seconds perhaps when I have bothered to time it). I will run some experiments with the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" hint...the challenge as well of course is that if it runs quickly, I have no idea whether I have fixed it, or whether it's just behaving for now. |
|
|
|
mschilder
Starting Member
4 Posts |
Posted - 2005-06-27 : 10:28:07
|
| just a followup...I don't think it's a locking issue. I artificially locked one of the tables involved, and my entire application hung waiting for the lock to clear. As soon as I clear it, everything flushes through normally. I don't think these tables are in any way locked when I am running this query. |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-27 : 10:41:21
|
| Have you tried monitoring server activity to see what else is going on when your query is running slow. Crack open profiler?How much do your parameters vary. It could be that the cached execution plan is not suitable for all of your possible searched. In such sitauations it may be necessary to use recompile with your CREATE PROCEDURE statement.Large amounts of data inserted into tables can also cause statistics to be wrong.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
|
|
|
mschilder
Starting Member
4 Posts |
Posted - 2005-06-27 : 11:57:37
|
| I've tried profiler. The handicap is mostly the unreliability of the bug, it has to happen while I am running profiler, and I have to have profiler configured in such a way as something useful might be revealed. I'm not very good with it unfortunately, there is to much in there to wade through that I don't understand. I've tried removing some profiling info to clean it down, but I am worried I am removing something I will actually need. |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-27 : 12:18:27
|
| I would use the TSQL and Stored Procedure Event Categories. From the TSQL Event Class I would include the following eventsSQL:BatchCompletedSQL:StmtCompletedFrom the Stored Proc Event Class, I would include the following eventsRPC:CompletedSP:CompletedAnd I would choose the following columns:EventClassSpidObjectIDObjectNameTextDataDurationEndTimeI would run profiler to write the results to a SQL table on another machine other than the machine you are monitoring. That way you can query the results.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
|
|
|
|
|
|
|
|
query alternately super fast, or super slow