Looking for consultant - 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 2008 Forums

Transact-SQL (2008)

Looking for consultant

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2012-03-17 : 13:03:36

Hi,

We're looking for an SQL 2008 expert to help us with a badly performing sp (currently takes over 40 minutes to run)

In particular we're looking for advice on indexing etc.

If you're interested in working with us on this problem please drop me an email ben at chillimintlabs dot com

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-17 : 13:35:01

why dont you post it here? That will certainly make it visible to wider audience and you will surely get good suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2012-03-19 : 10:52:03

Ok! Here it is:

Thanks


ALTER PROCEDURE [dbo].[sp_CollectStatsForAccount_Jobs]
(
	@AccountId int
)
AS
BEGIN
	DECLARE
	@v_AccountIdStats int

	SET NOCOUNT ON;

	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	BEGIN TRAN tran1
		
	BEGIN TRY

	SELECT @v_AccountIdStats = accountId
	from tblAccountStats with(xlock, nowait)
	where accountId=@AccountId

	DECLARE
		@TotalJobEmails int,
		@SentJobEmails int,
		@DeliveredJobEmails int,
		@SoftBounceJobEmails int,
		@HardBounceJobEmails int,
		@ClickedJobEmails int,
		@ClickedJobEmailsUnique int,
		@DisplayedJobEmails int,
		@DisplayedJobEmailsUnique int,
		@UnsubscribedJobEmails int,
		@SpamTotal int,
		@SpamTotalAll int,
		@ClickedEmails int,
		@ClickedEmailsUnique int,
		@DisplayedEmails int,
		@DisplayedEmailsUnique int,
		@FacebookSharedViews int,
		@TwitterSharedViews int,
		@FacebookClickedTotal int,
		@TwitterClickedTotal int,
		@FacebookClickedAll int,
		@TwitterClickedAll int,
		@FacebookClickedUnique int,
		@TwitterClickedUnique int,
		@FacebookClickedAllUnique int,
		@TwitterClickedAllUnique int


	DECLARE
	@v_Jobs table 
	( 
		JobId int,
		JobTypeId tinyint,
		JobStatusId tinyint,
		SoftBounceEmails int,
		SoftBounceEmailsAll int,
		HardBounceEmails int,
		HardBounceEmailsAll int,
		UnsubscribedEmails int,
		SpamTotal int,
		SpamTotalAll int,
		Clicked int,
		ClickedTotal int,
		Displayed int,
		DisplayedTotal int,
		FacebookClicked int,
		FacebookClickedTotal int,
		TwitterClicked int,
		TwitterClickedTotal int
	)

	/* ------ UPDATE JOBS ----------*/
	BEGIN TRANSACTION tran_inner_3

		 insert into @v_Jobs
		 (JobId, JobTypeId, JobStatusId, 
		  SoftBounceEmails, SoftBounceEmailsAll, HardBounceEmails, HardBounceEmailsAll, UnsubscribedEmails, 
		  SpamTotal, SpamTotalAll, Clicked, Displayed)
		select jd.JobId, j.JobTypeId, j.JobStatusId,
		SUM(Case when JobDetailGeneralStatusId = 3 AND jd.IsDeleted=0 then 1 else 0 end) as SoftBounceEmails,
		SUM(Case when JobDetailGeneralStatusId = 3 then 1 else 0 end) as SoftBounceEmailsAll,
		SUM(Case when JobDetailGeneralStatusId = 4 AND jd.IsDeleted=0 then 1 else 0 end) as HardBounceEmails,
		SUM(Case when JobDetailGeneralStatusId = 4 then 1 else 0 end) as HardBounceEmailsAll,
		SUM(Case when Unsubscribed = 1 then 1 else 0 end) as UnsubscribedEmails,
		SUM(Case when IsSpam = 1 AND jd.IsDeleted=0 then 1 else 0 end) as SpamTotal,
		SUM(Case when IsSpam = 1 then 1 else 0 end) as SpamTotalAll,
		SUM(Case when Clicked = 1 AND jd.IsDeleted=0 then 1 else 0 end) as Clicked,
		SUM(Case when Displayed = 1 AND jd.IsDeleted=0 then 1 else 0 end) as Displayed

		from tblJobDetails jd 
		inner join tblJobs j on j.JobId = jd.JobId and j.AccountId = jd.AccountId
		where jd.AccountId = j.AccountId and j.AccountId = @AccountId and j.IsDeleted = 0 and j.JobStatusId = 4 /*--Completed--*/
		and DATEDIFF(day, EndTime, GETDATE()) < 7
		Group By jd.JobID, jd.AccountId, j.JobTypeId, j.JobStatusId


		 UPDATE j
		   SET 
			  j.SoftBounceEmails = st.SoftBounceEmails, 
			  j.SoftBounceEmailsAll = st.SoftBounceEmailsAll,
			  j.HardBounceEmails = st.HardBounceEmails,
			  j.HardBounceEmailsAll = st.HardBounceEmailsAll,
			  j.UnsubscribedEmails = st.UnsubscribedEmails,
			  j.SoftBounceEmailsCounter = 0,
			  j.HardBounceEmailsCounter = 0,
			  j.SpamTotal = st.SpamTotal,
			  j.SpamTotalAll = st.SpamTotalAll,
			  j.ClickedEmails = st.Clicked,
			  j.DisplayedEmails = st.Displayed
		 FROM tblJobs j 
		 inner join @v_Jobs st on st.JobId = j.JobId
		 WHERE j.AccountId = @AccountId

	COMMIT TRANSACTION tran_inner_3


	/* ----- Calculate general statisitcs -------*/
	SELECT  @TotalJobEmails = ISNULL(SUM(TotalEmails),0),
	        @SentJobEmails = ISNULL(SUM(SentEmails),0),
			@SoftBounceJobEmails = ISNULL(SUM(SoftBounceEmailsAll),0),
			@HardBounceJobEmails = ISNULL(SUM(HardBounceEmailsAll),0),
			@ClickedJobEmails = ISNULL(SUM(ClickedEmailsAll),0),
			@ClickedJobEmailsUnique = ISNULL(SUM(ClickedEmailsUnique),0),
			@ClickedEmailsUnique = ISNULL(SUM(ClickedEmails),0),
			@DisplayedJobEmails = ISNULL(SUM(DisplayedEmailsAll),0),
			@DisplayedJobEmailsUnique = ISNULL(SUM(DisplayedEmailsUnique),0),
			@DisplayedEmailsUnique = ISNULL(SUM(DisplayedEmails),0),
			@UnsubscribedJobEmails = ISNULL(SUM(UnsubscribedEmails),0),
			@SpamTotal = ISNULL(SUM(SpamTotal),0),
			@SpamTotalAll = ISNULL(SUM(SpamTotalAll),0),			
			@FacebookSharedViews = ISNULL(SUM(FacebookSharedViews),0),
			@TwitterSharedViews = ISNULL(SUM(TwitterSharedViews),0),
			@FacebookClickedAll = ISNULL(SUM(FacebookClickedTotal),0),
			@TwitterClickedAll = ISNULL(SUM(TwitterClickedTotal),0),
			@FacebookClickedAllUnique = ISNULL(SUM(FacebookClickedUnique),0),
			@TwitterClickedAllUnique = ISNULL(SUM(TwitterClickedUnique),0),
			
			@ClickedEmails = ISNULL(SUM(ClickedEmailsAll),0),
			@DisplayedEmails = ISNULL(SUM(DisplayedEmailsAll),0),
			@FacebookClickedTotal = ISNULL(SUM(FacebookClickedTotal),0),
			@TwitterClickedTotal =  ISNULL(SUM(TwitterClickedTotal),0),
			@FacebookClickedUnique = ISNULL(SUM(FacebookClickedTotal),0),
			@TwitterClickedUnique = ISNULL(SUM(TwitterClickedTotal),0)			
		FROM tblJobs
		WHERE AccountId = @AccountId
		  AND IsDeleted = 0
		  AND JobStatusId = 4 -- Completed
		  AND JobTypeId IN (1,2,3,4) -- SMS, FAX

    SET @DeliveredJobEmails = @SentJobEmails - @SoftBounceJobEmails - @HardBounceJobEmails
	IF @DeliveredJobEmails < 0
		SET @DeliveredJobEmails = 0

	UPDATE tblAccountStats
	   SET 
		   SentJobEmails = @SentJobEmails,
		   DeliveredJobEmails = @DeliveredJobEmails,
		   SoftBounceJobEmails = @SoftBounceJobEmails,
		   HardBounceJobEmails = @HardBounceJobEmails,
		   ClickedJobEmails = @ClickedJobEmails, -- All total
		   ClickedJobEmailsUnique = @ClickedJobEmailsUnique, -- All unique
		   ClickedEmailsUnique = @ClickedEmailsUnique, -- Unique active		   
		   ClickedEmails = @ClickedEmails, -- Total active
		   DisplayedJobEmails = @DisplayedJobEmails, -- All Total
		   DisplayedJobEmailsUnique = @DisplayedJobEmailsUnique, -- All unique
		   DisplayedEmailsUnique = @DisplayedEmailsUnique, -- Unique active
		   DisplayedEmails = @DisplayedEmails, -- Total active
		   UnsubscribedJobEmails = @UnsubscribedJobEmails,
		   SpamReported = @SpamTotal, -- Active
		   SpamReportedAll = @SpamTotalAll, -- All
		   FacebookSharedViews = @FacebookSharedViews,
		   TwitterSharedViews = @TwitterSharedViews,
		   FacebookClicked = @FacebookClickedTotal, -- All active
		   TwitterClicked = @TwitterClickedTotal, -- All active
		   FacebookClickedUnique = @FacebookClickedUnique, --Unique active
		   TwitterClickedUnique = @TwitterClickedUnique, -- Unique active
		   FacebookClickedAll = @FacebookClickedAll, -- Total All
		   TwitterClickedAll = @TwitterClickedAll, -- Total All
		   FacebookClickedAllUnique = @FacebookClickedAllUnique, -- Unique All
		   TwitterClickedAllUnique = @TwitterClickedAllUnique -- Unique All
     WHERE AccountId = @AccountId


	 INSERT INTO tblAccountStatsHistory
	 (AccountId, OnDate, TotalEmails, ActiveEmails, UnsubscribedEmails, SoftBouncedEmails, HardBouncedEmails, SentJobEmails, DeliveredJobEmails,
	 SoftBounceJobEmails, HardBounceJobEmails, ClickedJobEmails, DisplayedJobEmails, UnsubscribedJobEmails, SpamReported, SpamReportedAll,
	 AllEmails, ClickedEmails, DisplayedEmails, FacebookSharedViews, TwitterSharedViews, FacebookClicked, TwitterClicked, FacebookClickedAll,
	 TwitterClickedAll, ClickedJobEmailsUnique, ClickedEmailsUnique, DisplayedJobEmailsUnique, DisplayedEmailsUnique, TwitterClickedUnique,
	 FacebookClickedUnique, FacebookClickedAllUnique, TwitterClickedAllUnique)
	 SELECT AccountId, GETDATE(), TotalEmails, ActiveEmails, UnsubscribedEmails, SoftBouncedEmails, HardBouncedEmails, SentJobEmails, DeliveredJobEmails,
	 SoftBounceJobEmails, HardBounceJobEmails, ClickedJobEmails, DisplayedJobEmails, UnsubscribedJobEmails, SpamReported, SpamReportedAll,
	 AllEmails, ClickedEmails, DisplayedEmails, FacebookSharedViews, TwitterSharedViews, FacebookClicked, TwitterClicked, FacebookClickedAll,
	 TwitterClickedAll, ClickedJobEmailsUnique, ClickedEmailsUnique, DisplayedJobEmailsUnique, DisplayedEmailsUnique, TwitterClickedUnique,
	 FacebookClickedUnique, FacebookClickedAllUnique, TwitterClickedAllUnique
	 FROM tblAccountStats
	 WHERE AccountId=@AccountId
	 
	 
	 EXEC sp_UpdateGlobalAccountStats

	 COMMIT TRAN tran1

	END TRY
	BEGIN CATCH
		ROLLBACK TRAN tran1

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, -- Message text.
				@ErrorSeverity, -- Severity.
				@ErrorState -- State.
				);
	END CATCH
		
END

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-03-19 : 14:58:47

Have you run Profiler to try to narrow down which section is taking the longest?

Do you have indexes on the columns that you are joining on?

If not, those are 2 good ideas.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-19 : 15:19:05

Show us the execution plan as well as the output of stats io/time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 15:53:52

can you show execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-19 : 16:02:26

quote:
Originally posted by visakh16

can you show execution plan?

?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 16:07:12

quote:
Originally posted by tkizer

quote:
Originally posted by visakh16

can you show execution plan?

?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sorry..that was not intended
was away after typing, came back and posted without refreshing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2012-03-19 : 17:29:13

Hi,

Thanks for all the advice, I really appreciate it.

How do I post the execution plan? I've selected "show actual execution plan" but this seems to be a graphical view and i'm not sure how to interpret it to post?

Tkizer what do you mean by "output of stats io/time"?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-19 : 17:37:24

Add this right before your EXEC:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Show us the output of that.

In regards to the exec plan, save it as a sqlplan extension and then upload it to somewhere we can download or view it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-19 : 21:17:47

I re-wrote some things, but I unfortuantly don't have sql tools installed on my laptop, so I used notepad, so there may be some syntax errors. See if you have better luck with this. I removed some temp tables, and also cleaned up some transactions and join statements. You will undoubtedly want to run a execution plan as well to figure out the reason for each delay to ensure it is properly optimized, but here is my guess at it without seeing a execution plan:


ALTER PROCEDURE [dbo].[sp_CollectStatsForAccount_Jobs]
(
	@AccountId int
)
AS
BEGIN
	SET NOCOUNT ON;

	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--Might take out above depending on why this is being used

BEGIN TRAN tran1
BEGIN TRY
	BEGIN TRAN tran2	
	

	DECLARE
		@TotalJobEmails int,
		@SentJobEmails int,
		@DeliveredJobEmails int,
		@SoftBounceJobEmails int,
		@HardBounceJobEmails int,
		@ClickedJobEmails int,
		@ClickedJobEmailsUnique int,
		@DisplayedJobEmails int,
		@DisplayedJobEmailsUnique int,
		@UnsubscribedJobEmails int,
		@SpamTotal int,
		@SpamTotalAll int,
		@ClickedEmails int,
		@ClickedEmailsUnique int,
		@DisplayedEmails int,
		@DisplayedEmailsUnique int,
		@FacebookSharedViews int,
		@TwitterSharedViews int,
		@FacebookClickedTotal int,
		@TwitterClickedTotal int,
		@FacebookClickedAll int,
		@TwitterClickedAll int,
		@FacebookClickedUnique int,
		@TwitterClickedUnique int,
		@FacebookClickedAllUnique int,
		@TwitterClickedAllUnique int,
		@V_AccountIDStats int

		set @V_AccountIDStats = @Accountid --You had a select statement setting it to the same value as accountid?



		 UPDATE j
		   SET 
			  j.SoftBounceEmails = st.SoftBounceEmails, 
			  j.SoftBounceEmailsAll = st.SoftBounceEmailsAll,
			  j.HardBounceEmails = st.HardBounceEmails,
			  j.HardBounceEmailsAll = st.HardBounceEmailsAll,
			  j.UnsubscribedEmails = st.UnsubscribedEmails,
			  j.SoftBounceEmailsCounter = 0,
			  j.HardBounceEmailsCounter = 0,
			  j.SpamTotal = st.SpamTotal,
			  j.SpamTotalAll = st.SpamTotalAll,
			  j.ClickedEmails = st.Clicked,
			  j.DisplayedEmails = st.Displayed
		 FROM tblJobs j 
		 inner join
			(
			 	
				select jd.JobId, j.JobTypeId, j.JobStatusId,jd.AccountID
				SUM(Case when JobDetailGeneralStatusId = 3 AND jd.IsDeleted=0 then 1 else 0 end) as SoftBounceEmails,
				SUM(Case when JobDetailGeneralStatusId = 3 then 1 else 0 end) as SoftBounceEmailsAll,
				SUM(Case when JobDetailGeneralStatusId = 4 AND jd.IsDeleted=0 then 1 else 0 end) as HardBounceEmails,
				SUM(Case when JobDetailGeneralStatusId = 4 then 1 else 0 end) as HardBounceEmailsAll,
				SUM(Case when Unsubscribed = 1 then 1 else 0 end) as UnsubscribedEmails,
				SUM(Case when IsSpam = 1 AND jd.IsDeleted=0 then 1 else 0 end) as SpamTotal,
				SUM(Case when IsSpam = 1 then 1 else 0 end) as SpamTotalAll,
				SUM(Case when Clicked = 1 AND jd.IsDeleted=0 then 1 else 0 end) as Clicked,
				SUM(Case when Displayed = 1 AND jd.IsDeleted=0 then 1 else 0 end) as Displayed
				from 
					tblJobDetails jd 
				inner join 
					tblJobs j 
				on 		j.JobId = jd.JobId 
				and 		j.AccountId = jd.AccountId
				where 
					j.AccountId = @AccountId 
				and 	j.IsDeleted = 0 
				and 	j.JobStatusId = 4 
				and 	DATEDIFF(day, EndTime, GETDATE()) < 7
				Group By jd.JobID, jd.AccountId, j.JobTypeId, j.JobStatusId
			 ) st
		on j.Jobid = st.Jobid
 		and j.AccountID = st.AccountID


	/* ----- Calculate general statisitcs -------*/
	SELECT  @TotalJobEmails = ISNULL(SUM(TotalEmails),0),
	        @SentJobEmails = ISNULL(SUM(SentEmails),0),
			@SoftBounceJobEmails = ISNULL(SUM(SoftBounceEmailsAll),0),
			@HardBounceJobEmails = ISNULL(SUM(HardBounceEmailsAll),0),
			@ClickedJobEmails = ISNULL(SUM(ClickedEmailsAll),0),
			@ClickedJobEmailsUnique = ISNULL(SUM(ClickedEmailsUnique),0),
			@ClickedEmailsUnique = ISNULL(SUM(ClickedEmails),0),
			@DisplayedJobEmails = ISNULL(SUM(DisplayedEmailsAll),0),
			@DisplayedJobEmailsUnique = ISNULL(SUM(DisplayedEmailsUnique),0),
			@DisplayedEmailsUnique = ISNULL(SUM(DisplayedEmails),0),
			@UnsubscribedJobEmails = ISNULL(SUM(UnsubscribedEmails),0),
			@SpamTotal = ISNULL(SUM(SpamTotal),0),
			@SpamTotalAll = ISNULL(SUM(SpamTotalAll),0),			
			@FacebookSharedViews = ISNULL(SUM(FacebookSharedViews),0),
			@TwitterSharedViews = ISNULL(SUM(TwitterSharedViews),0),
			@FacebookClickedAll = ISNULL(SUM(FacebookClickedTotal),0),
			@TwitterClickedAll = ISNULL(SUM(TwitterClickedTotal),0),
			@FacebookClickedAllUnique = ISNULL(SUM(FacebookClickedUnique),0),
			@TwitterClickedAllUnique = ISNULL(SUM(TwitterClickedUnique),0),
			
			@ClickedEmails = ISNULL(SUM(ClickedEmailsAll),0),
			@DisplayedEmails = ISNULL(SUM(DisplayedEmailsAll),0),
			@FacebookClickedTotal = ISNULL(SUM(FacebookClickedTotal),0),
			@TwitterClickedTotal =  ISNULL(SUM(TwitterClickedTotal),0),
			@FacebookClickedUnique = ISNULL(SUM(FacebookClickedTotal),0),
			@TwitterClickedUnique = ISNULL(SUM(TwitterClickedTotal),0)			
		FROM tblJobs
		WHERE AccountId = @AccountId
		  AND IsDeleted = 0
		  AND JobStatusId = 4 -- Completed
		  AND JobTypeId IN (1,2,3,4) -- SMS, FAX

    SET @DeliveredJobEmails = @SentJobEmails - @SoftBounceJobEmails - @HardBounceJobEmails
	IF @DeliveredJobEmails < 0
		SET @DeliveredJobEmails = 0

	UPDATE tblAccountStats
	   SET 
		   SentJobEmails = @SentJobEmails,
		   DeliveredJobEmails = @DeliveredJobEmails,
		   SoftBounceJobEmails = @SoftBounceJobEmails,
		   HardBounceJobEmails = @HardBounceJobEmails,
		   ClickedJobEmails = @ClickedJobEmails, -- All total
		   ClickedJobEmailsUnique = @ClickedJobEmailsUnique, -- All unique
		   ClickedEmailsUnique = @ClickedEmailsUnique, -- Unique active		   
		   ClickedEmails = @ClickedEmails, -- Total active
		   DisplayedJobEmails = @DisplayedJobEmails, -- All Total
		   DisplayedJobEmailsUnique = @DisplayedJobEmailsUnique, -- All unique
		   DisplayedEmailsUnique = @DisplayedEmailsUnique, -- Unique active
		   DisplayedEmails = @DisplayedEmails, -- Total active
		   UnsubscribedJobEmails = @UnsubscribedJobEmails,
		   SpamReported = @SpamTotal, -- Active
		   SpamReportedAll = @SpamTotalAll, -- All
		   FacebookSharedViews = @FacebookSharedViews,
		   TwitterSharedViews = @TwitterSharedViews,
		   FacebookClicked = @FacebookClickedTotal, -- All active
		   TwitterClicked = @TwitterClickedTotal, -- All active
		   FacebookClickedUnique = @FacebookClickedUnique, --Unique active
		   TwitterClickedUnique = @TwitterClickedUnique, -- Unique active
		   FacebookClickedAll = @FacebookClickedAll, -- Total All
		   TwitterClickedAll = @TwitterClickedAll, -- Total All
		   FacebookClickedAllUnique = @FacebookClickedAllUnique, -- Unique All
		   TwitterClickedAllUnique = @TwitterClickedAllUnique -- Unique All
     WHERE AccountId = @AccountId


	 INSERT INTO tblAccountStatsHistory
	 (AccountId, OnDate, TotalEmails, ActiveEmails, UnsubscribedEmails, SoftBouncedEmails, HardBouncedEmails, SentJobEmails, DeliveredJobEmails,
	 SoftBounceJobEmails, HardBounceJobEmails, ClickedJobEmails, DisplayedJobEmails, UnsubscribedJobEmails, SpamReported, SpamReportedAll,
	 AllEmails, ClickedEmails, DisplayedEmails, FacebookSharedViews, TwitterSharedViews, FacebookClicked, TwitterClicked, FacebookClickedAll,
	 TwitterClickedAll, ClickedJobEmailsUnique, ClickedEmailsUnique, DisplayedJobEmailsUnique, DisplayedEmailsUnique, TwitterClickedUnique,
	 FacebookClickedUnique, FacebookClickedAllUnique, TwitterClickedAllUnique)
	 SELECT AccountId, GETDATE(), TotalEmails, ActiveEmails, UnsubscribedEmails, SoftBouncedEmails, HardBouncedEmails, SentJobEmails, DeliveredJobEmails,
	 SoftBounceJobEmails, HardBounceJobEmails, ClickedJobEmails, DisplayedJobEmails, UnsubscribedJobEmails, SpamReported, SpamReportedAll,
	 AllEmails, ClickedEmails, DisplayedEmails, FacebookSharedViews, TwitterSharedViews, FacebookClicked, TwitterClicked, FacebookClickedAll,
	 TwitterClickedAll, ClickedJobEmailsUnique, ClickedEmailsUnique, DisplayedJobEmailsUnique, DisplayedEmailsUnique, TwitterClickedUnique,
	 FacebookClickedUnique, FacebookClickedAllUnique, TwitterClickedAllUnique
	 FROM tblAccountStats
	 WHERE AccountId=@AccountId
	 
	 	 
	 COMMIT TRAN tran2
 EXEC sp_UpdateGlobalAccountStats
 COMMIT TRAN tran1
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN tran1

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, -- Message text.
				@ErrorSeverity, -- Severity.
				@ErrorState -- State.
				);
	END CATCH
		
END

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 21:45:30

You should treat all of the work as 1 transaction

You should get rid of the table variable

Whats the KEY to the tblAccountStats

It seems like the SELECT from tblJobs might be returning MANY rows...it needs to be 1..actually the SET should GO AWAY and should be part of the update

We would benefit from a Business Requirement Document explaining what this is SUPPOSE to be doing and the DDL of the tables

I would almost BET $100 dollars that this WAY over complicated han it needs to be

So..please explain the purpose of the sproc...No code, no pseudo code, just business language

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources