Query Question -- odd results

Question

Post reply

Query Question -- odd results

Mike Scalise

SSCrazy Eights

Points: 8145
More actions
June 29, 2015 at 3:51 pm

#302127

I've been developing a stored procedure (approx. 1100 lines) that returns a set of commands that will be used by the application that calls it. I'm having a problem where, each time I run the stored procedure, my row count is sometimes the same as the previous run and sometimes it's one or two records different. I don't understand why--I'm querying only static tables. The only function I use that would return different values each time is Getdate(). However, I'm stripping off the time, so as long as I'm testing the SP runs on the same day, they should return the same results since neither the data nor the date is changing.
I've compared the two sets of results and the difference between the two is different each time--usually a row that has no significance that is in one result set and not the other. I can't really explain it.
Does anyone have any thoughts as to what I might look for?
Oh, and the fact that the result sets sometimes match and sometimes don't make it really difficult to step through and debug to see what's happening.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com

Viewing 9 posts - 1 through 8 (of 8 total)

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

g.britton SSChampion Points: 13863 More actions · Answer 1

run it a section at a time and verify the results at each step. You'll likely have an "aha!" moment.

Gerald Britton, Pluralsight courses

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 2

Thanks for the suggestion, Gerald.

I've run it section by section, but without the row count changing intermittently, it's very difficult to pinpoint the exact problem.

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

Lowell SSC Guru Points: 323518 More actions · Answer 3

can you add a logging fucntionality, that dumps some details to a log table as it completes each substep? then you can run it three or four times, and review the table of log steps and see if something in what you log shows the difference; ; log the parameters used and a description of each step.

it's a little better than print statements and it might point you at the differences between runs; maybe something parameter is evaluated differently, due to implicit casting, or something.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sgmunson SSC Guru Points: 110640 More actions · Answer 4

mikes84 (6/30/2015)
Thanks for the suggestion, Gerald.
I've run it section by section, but without the row count changing intermittently, it's very difficult to pinpoint the exact problem.
Mike

And without a posted query and some sample data, there's not a whole lot for us to suggest. I suspect you may have a condition that isn't evaluating quite as deterministically as you think it is. Go looking for a TOP keyword and then for NOT finding the ORDER BY for that query. It is one thing that can actually return a different result on different executions from identical data. Even if you find the ORDER BY, it may not be sufficiently specific to nail down the exact same record every time.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 5

Hey guys,

Thanks so much for the suggestions! I found the exact query that produces different results each time I execute it. I'm still not exactly sure why, but there aren't any TOPs. I am using a Row_Number() function with an ORDER BY. In any case, I'll continue debugging. Thanks again for the help!

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

sgmunson SSC Guru Points: 110640 More actions · Answer 6

mikes84 (6/30/2015)
Hey guys,
Thanks so much for the suggestions! I found the exact query that produces different results each time I execute it. I'm still not exactly sure why, but there aren't any TOPs. I am using a Row_Number() function with an ORDER BY. In any case, I'll continue debugging. Thanks again for the help!
Mike

I'm guessing that the ORDER BY might not be specific enough to guarantee the exact same record appears with the value of 1 each time. You may need to add columns to that ORDER BY in order to guarantee exactly which record should be assigned the value of 1.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 7

Yes, that is it! I get consistent results when I'm more specific in the ORDER BY clause.

Thank you!

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

sgmunson SSC Guru Points: 110640 More actions · Answer 8

mikes84 (6/30/2015)
Yes, that is it! I get consistent results when I'm more specific in the ORDER BY clause.
Thank you!
Mike

You're welcome. Glad I could help.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)