November 11, 2010 at 1:38 pm
Hi all, I've got a situation I've never encountered before. Any help is appreciated:
The source is a static table from a third party. The code is:
1. a set of select...into <temp table> SQL queries.
2. Then joining them all together and inserting into a static destination table.
3. Finally DROP TABLE <temp table> commands for each.
(Note: the code is structured this way as I'm running 50-odd median calculations against a 20 million record table. The total query time using this method instead of one big SQL statement drops to 1 hour from 3)
The problem:
If I run this code as a stored procedure, I get different results than if I manually select the stored procedure's code and hit F5. Specifically, I see NULLs in columns that should have data, and by manually running the code they get real values. I can't find a pattern to predict which fields are the problem, but the results are repeatable.
The join fields can have NULL values, so I thought of the ANSI NULL setting, but the missing data are for non-NULL join field situations.
Has anyone ever seen this before? I'm about to clear all my plan cache, but doubt that's the problem.
-Dom
Here's some pseudo-code so you get the idea:
select year, <geography fields>, AVG([loanamount]) as result
into <temp this geography this field table>
from (
select year, <geography fields>
, ROW_NUMBER() OVER (PARTITION BY 'dummypartition', year, <geography fields> ORDER BY [amount] ASC) as rownum_asc
, ROW_NUMBER() OVER (PARTITION BY 'dummypartition', year, <geography fields> ORDER BY [amount] DESC) as rownum_desc
, [amount]
from <source table>
where
<filter conditions>
) RowNum_toGetMedian
where abs(rownum_asc - rownum_desc) <= 1
group by year, <geography fields>;
select
A.[year], A.<geography fields>
, <temp this geography field 1 table>.<geography field 1>.[result] as <field 1>
, <temp this geography field 2 table>.<geography field 2>.[result] as <field 2>
, ...
into <temp this geography table>
from (
select distinct [year], <geography fields>
from <source table>
) A
left outer join <temp this geography field 1 table>
on
A.[year] = <temp this geography field 1 table>.[year]
and A.<geography field 1> = <temp this geography field 1 table>.<geography field 1>
and ...
left outer join <temp this geography field 2 table>
on
A.[year] = <temp this geography field 2 table>.[year]
and A.<geography field 1> = <temp this geography field 2 table>.<geography field 1>
and ...
...
insert into <destination table>
( [year], <geography fields>, <field 1>, <field 2>, ... )
select
[year], <geography fields>, <field 1>, <field 2>, ...
from <temp this geography table>;
DROP TABLE <temp this geography field 1 table>;
DROP TABLE <temp this geography field 2 table>;
...
DROP TABLE <temp this geography table>;
END
November 11, 2010 at 4:58 pm
Haven't seen that before, dfalso, except when I'm getting different defaults off my procedure declaration.
I know you're trying to keep the code to a reasonable length here, but this is a straight up troubleshoot on declaration vs. intent. We'd need to see a fresh script create of the proc vs. what you're manually running, and by preference a sample set of the data where it's different.
Anything with that much code going is going to be difficult, at best, to troubleshoot away from the source data showing the issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 7:52 am
Craig Farrell (11/11/2010)
Haven't seen that before, dfalso, except when I'm getting different defaults off my procedure declaration.I know you're trying to keep the code to a reasonable length here, but this is a straight up troubleshoot on declaration vs. intent. We'd need to see a fresh script create of the proc vs. what you're manually running, and by preference a sample set of the data where it's different.
Anything with that much code going is going to be difficult, at best, to troubleshoot away from the source data showing the issue.
Thanks for responding, Craig. While I understand where you're coming from and would like to show more, unfortunately the underlying logic is proprietary so I can't.
Another wrinkle that I found out this weekend is that running all the commands in one big script (with GO statements, even) also fails, in the exact same way. So it's not tied to manual vs. proc settings.
November 15, 2010 at 8:55 am
I would still check for the set options. Bellow there is a code that shows you what can happen when you use different set options at the procedure’s creating time and when you run the procedure’s code. Also notice that the case statement that was used doesn’t show the null value, so at first when you run it, it might look that a none null value is missing.
--use tempdb
go
create table tbl1 (i int, c char(10))
go
insert into tbl1 (i, c)
select 1, 'one'
union
select null, 'a'
go
--setting ansi_nulls off. The procedure will always work with this setting
--regardless of the setting during run time
set ansi_nulls off
go
--creating the procedure. Because the procedure was created with ansi_nulls off
--it will return the row that has null as a value for i. Because I used case
--statement, it will show -2 instead of null
create proc test (@val int)
as
select case when tbl1.i is null then -2 else tbl1.i end as i, tbl1.c
from tbl1
where tbl1.i = @val
go
--using the normal setting again
set ansi_nulls on
go
--running the exec same code as the procedure, but getting no results
declare @val int
select case when tbl1.i is null then -2 else tbl1.i end as i, tbl1.c
from tbl1
where tbl1.i = @val
--executing the procedure and getting results
exec test @val
go
--cleanup
drop table tbl1
go
drop proc test
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply