Slow running query - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Slow running query

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-04-02 : 12:07:50

Hello All,

I wrote a sql statement that has several joins and it time out in "SQL Server Enterpise Manager", but when I run the same statement in SQL "Query Analyzer" it just runs and runs without end. The statement is shown below. Please tell me how to rewrite it so it could run faster. Thanks.

SQL Statement:

SELECT     MPA.MU_ID, M_UNIT.M_NAME, '"' + RTRIM(SUBSTRING(RTRIM(MPA.AGENT_NAME), 1, CHARINDEX(' ', 
                      RTRIM(MPA.AGENT_NAME)))) + '"' AS LAST_NAME, '"' + LTRIM(SUBSTRING(RTRIM(MPA.AGENT_NAME), CHARINDEX(' ', 
                      RTRIM(MPA.AGENT_NAME)) + 1, LEN(RTRIM(MPA.AGENT_NAME)))) + '"' AS FIRST_NAME, MPA.A_ID, 
                      S_HEAD.[DATE], EXC.EXC_ID, S_DETAIL.EXC_CODE, S_DETAIL.SCHED_ID , S_HEAD.STATUS
FROM         S_DETAIL INNER JOIN
                      S_HEAD ON S_DETAIL.SCHED_ID = S_HEAD.SCHED_ID INNER JOIN
                      EXC ON S_DETAIL.EXC_CODE = EXC.EXC_ID INNER JOIN
                      MPA ON S_HEAD.A_ID = MPA.A_ID INNER JOIN
                      M_UNIT ON MPA.MU_ID = M_UNIT.MU_ID
GROUP BY MPA.MU_ID, M_UNIT.M_NAME, '"' + RTRIM(SUBSTRING(RTRIM(MPA.AGENT_NAME), 1, CHARINDEX(' ', 
                      RTRIM(MPA.AGENT_NAME)))) + '"', MPA.A_ID, S_HEAD.[DATE], EXC.EXC_ID, 
                      S_DETAIL.EXC_CODE, S_DETAIL.SCHED_ID, S_HEAD.STATUS, 
                      '"' + LTRIM(SUBSTRING(RTRIM(MPA.AGENT_NAME), CHARINDEX(' ', RTRIM(MPA.AGENT_NAME)) + 1, 
                      LEN(RTRIM(MPA.AGENT_NAME)))) + '"'
HAVING      (MPA.MU_ID <> 999) AND (S_HEAD.STATUS = 'A')
ORDER BY S_HEAD.[DATE], MPA.A_ID, MPA.MU_ID, M_UNIT.M_NAME

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 12:12:16

I think splitting the Agent_name values using CHARINDEX() should better be done at front-end. It may be keeping SQL Server from using the necessary indexes.

Also why you need GROUP BY here? using simple distinct will be more efficient.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pras2007
Posting Yak Master

216 Posts

Posted - 2007-04-02 : 12:34:45

quote:
Originally posted by harsh_athalye

I think splitting the Agent_name values using CHARINDEX() should better be done at front-end. It may be keeping SQL Server from using the necessary indexes.

Also why you need GROUP BY here? using simple distinct will be more efficient.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks for the post Harsh, but what do you mean by "front-end". Can you please rewrite the statement providing the "CHARINDEX() in the front-end?. Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-03 : 03:16:15

By front-end I mean the UI where you are going to present this data to the user.

For example you can use output of the query as it is and pass it to reporting tool where you can write formula to perform the splitting operation.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 04:47:37

Try this

SELECT DISTINCT	x.MU_ID,
		x.M_NAME,
		'"' + LEFT(x.AGENT_NAME, x.POS_SPACE - 1) + '"' AS LAST_NAME,
		'"' + SUBSTRING(x.AGENT_NAME, x.POS_SPACE + 1, 8000) + '"' AS FIRST_NAME,
		x.A_ID,
		x.[DATE],
		x.EXC_ID,
		x.EXC_CODE,
		x.SCHED_ID,
		x.STATUS
FROM		(
			SELECT		MPA.MU_ID,
					M_UNIT.M_NAME,
					RTRIM(MPA.AGENT_NAME) AS AGENT_NAME,
					CHARINDEX(' ', MPA.AGENT_NAME) AS POS_SPACE,
					MPA.A_ID,
					S_HEAD.[DATE],
					EXC.EXC_ID,
					S_DETAIL.EXC_CODE,
					S_DETAIL.SCHED_ID,
					S_HEAD.STATUS
			FROM		S_DETAIL
			INNER JOIN	S_HEAD ON S_HEAD.SCHED_ID = S_DETAIL.SCHED_ID
			INNER JOIN	EXC ON EXC.EXC_ID = S_DETAIL.EXC_CODE
			INNER JOIN	MPA ON MPA.A_ID = S_HEAD.A_ID
			INNER JOIN	M_UNIT ON M_UNIT.MU_ID = MPA.MU_ID
			WHERE		MPA.MU_ID <> 999
					AND S_HEAD.STATUS = 'A'
		) AS x
ORDER BY	x.[DATE],
		x.A_ID,
		x.MU_ID,
		x.M_NAME

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 05:41:07

<<
Please tell me how to rewrite it so it could run faster. Thanks.
>>

It also depends on how many rows those tables have. If there are millions of data in each table, it will take time to give results

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources