SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Code running slowly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 02/14/2013 :  06:53:04  Show Profile  Reply with Quote
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

United Kingdom
206 Posts

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

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 02/14/2013 :  10:38:04  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 02/15/2013 :  02:53:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 02/16/2013 :  06:15:45  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 02/18/2013 :  06:04:44  Show Profile  Reply with Quote
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 - 02/18/2013 :  06:38:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 02/18/2013 :  08:11:40  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 02/18/2013 :  08:31:04  Show Profile  Reply with Quote
Well, it seems to have worked wonders on this one so I think I'll leave it in.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 02/20/2013 :  08:00:05  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000