SP vs Identical T-SQL - different results

Question

SP vs Identical T-SQL - different results

Rob-350472

SSCertifiable

Points: 7197
More actions
July 23, 2013 at 4:11 am

#275559

Guys,
I've got an SP which takes two date parameters (a max and min), if I execute the SP I get X rows, if I script the SP to a new window, remove the SP stuff, declare and set the date parameters identically to when running the SP I get a totally different results set!
Looking at the two results sets they do both have data over the same date range so it isn't an odd date formatting issue or something (besides, I'm using the same format in both cases).
I've tried to re-compile the SP, recreate with a different name, even run on a test database on a separate server and I still get the same results. I passed this to a colleague for a sanity check, he hard coded hte dates into the SP - running the SP (now no params) and the code still produces different results.
By different I'm talking 90 odd rows vs over 1000 rows.
Is there any way to see if the sys objects has got confused or something? - As a side note, it's a proc used for an SSRS report that’s been in use for many months (stretching into years) without an issue, and there haven't been any changes to it.
Any help much appreciated - I'm going crazy here!

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

What if you look at the actual execution plans? Any differences?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 2

There are very subtle differencs in the Exceution plans between the SP and the code, however, they are different.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

What are your current settings and the stored procedure settings for ANSI_NULLS, CONCAT_NULL_YIELDS_NULL etc?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 4

As with all our Sprocs really,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Then straight into 'Alter Proc...' or 'Create Proc...'

Then the TSQL. Nothing fancy.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you post the two pieces of code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 6

Hmm, to an extent yes, however, without the objects and data behind it I'm not sure it'll be that useful - basically on the same DB - 'script procedure... Alter to' - remove hte stuff at the top and put in varialble names - so it's not like the code is any different between the two.

I did wonder if I can view any details for hte proc in the sys objects tables or something and if that would give any clues?

It seems even more odd that a new proc from the same code exhibits identical behaviour...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

If they're producing different results, then there must be a difference somewhere in code or connection settings. There's no hidden 'return different results' properties of a procedure, there's no hidden old copy of the procedure. Hence the answer is somewhere in the code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Rob-350472 (7/23/2013)
There are very subtle differencs in the Exceution plans between the SP and the code, however, they are different.

Can you post them? The Actual plans?

Do you have TOP without ORDER BY anywhere in your code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 9

I've uploaded the two execution plans (I had no idea SSMS tools allowed me to save it as a jpg, nifty!).

There's a couple of maxes but no tops in the query/proc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Rob-350472 (7/23/2013)
I've uploaded the two execution plans (I had no idea SSMS tools allowed me to save it as a jpg, nifty!).
There's a couple of maxes but no tops in the query/proc

Nifty but fairly useless, sorry Rob. Can you attach them as .sqlplan files please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 11

I did wonder how someone would find those useful to be honest, but I'm no plan guru by any means. See attached...

I emphasise that the only change from scripting the proc is commenting out this lot:

--USE [dbname]
--GO
--/****** Object: StoredProcedure [dbo].[SP_G_SSRS_ProcName] Script Date: 07/23/2013 13:08:53 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--ALTER Procedure [dbo].[SP_G_SSRS_ProcName]
--@MinDate datetime, @MaxDate datetime
-- AS

And uncommenting this:

DECLARE @MinDate datetime
DECLARE @MaxDate datetime
SET @MinDate = '2013-06-23 00:00:00'
SET @MaxDate = '2013-07-23 00:00:00'

And that the code doesn't have tops or anything, hmm

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Perhaps unrelated (or perhaps not), but those nolocks are going to cause you problems at some point.

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 13

I wondered if someone would pick up on those 😮

I'm aware of their caveats, they're not used in every proc we write but in some they are.

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 14

I've sussed it out, the code has this column:

CASE WHEN a.User_ID IN (select c.tgtUserID from Audit..tblConsolidated c) THEN 1 ELSE 0 END as UserConsolidated

If I comment out this part of hte code and recompile the sproc I get identical results under the sproc or the TSql code (I swear I tried this before posting last night).

There must be something different with connecting to a separate database either in straight tSQL vis via a procedure, not sure what though (it's very rare for a proc to be cross DB in our setup).