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
 General SQL Server Forums
 New to SQL Server Programming
 Code running slowly

Author  Topic 

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-14 : 06:53:04
Hi all

I've got a piece of code:-

SELECT DISTINCT
a.[Timestamp] as [Timestamp],
tmp.SubService as [SubService],
sum(a.[ShortCallsAnswered]) as [ShortCallsAnswered]
FROM [SymposiumDW].[dbo].[FACT_iAgentPerformanceStat] (nolock) a
join [SymposiumDW].[dbo].[FACT_iAgentByApplicationStat] (nolock) ap
on a.HUBID = ap.hubid
and a.[Timestamp] = ap.[Timestamp]
and a.AgentLogin = ap.agentlogin
join #ApplicationIDs (nolock) tmp
on tmp.ApplicationID = ap.ApplicationID
WHERE a.[Timestamp] BETWEEN @Start AND @End
group by a.[Timestamp], tmp.SubService


that runs on our local server.

If I run it as a piece of code it runs in about a minute.
If I run it as part of a larger stored procedure, it stalls (and by stall I mean as a piece of code it takes about 90 seconds to run, as part of the stored procedure 45 minutes or even longer).

Can anyone see any issues with the code or suggest improvements?

All dates/times are datetime format (@start, @end, timestamp) and all the other field types match.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2013-02-14 : 07:11:58
Could you provide the table definitions, index definitions and execution plan?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-14 : 10:38:04
Check this post for how to troubleshoot a slow running query : http://www.sqlserver-dba.com/2012/11/sql-server-how-to-troubleshoot-a-slow-running-query.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-15 : 02:53:43
Jackv - I've run through the stuff on the link and I can't see any problems.
Abu-Dina - it's reading data into a temp table. I've checked where the original data is coming from and everything looks fine. Indexes on the apprropriate columns, etc. How do I post an execution plan?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-16 : 06:15:45
As it's part of a larger stored procedure - is there anything else that is in the stored procedure which cause delays. A classic example, DDL changes as part of the stored procedure- this forces a recompilation - use Profiler and the SQL:StmtRecompile event class, to identify recompilations.



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-18 : 06:04:44
As far as I know, there are no other issues with the SPROC, it runs fine as stand-alone code.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-18 : 06:38:56
I think I've solved it.
I've added 1 index and 1 statistics to the relevant table and it's run fine this morning.

Very odd. I always thought adding indexes, etc slow down writes to a table.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-18 : 08:11:40
It can slow writes down, but needs to be weighed up against benefits for selects.
We have some regular maintenance which looks at indexes to see whether there are any reads. If reads are not occuring , then consider dropping the index

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-18 : 08:31:04
Well, it seems to have worked wonders on this one so I think I'll leave it in.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-20 : 08:00:05
good news. Check out sys.dm_db_index_usage_stats for some interesting details on usage

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -