Author |
Topic |
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-02-14 : 06:53:04
|
Hi allI'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.ApplicationIDWHERE a.[Timestamp] BETWEEN @Start AND @Endgroup 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? |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
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? |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
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 indexJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
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. |
|
|
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 usageJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|