February 12, 2009 at 4:40 pm
Banged my head against this most of the afternoon. One of my programmers needs an empty result set and is making this call to get it:
exec dbo.ord_sp_Select_Order NULL, NULL, NULL
Yes, it is a dumb way to do it and why he needs an empty resultset is a Delphi thing I'm told. Yes, I work in a Delphi shop, please don't laugh.
In dev (and prod) it runs in 816 milliseconds, in QA (aka test) it runs 22 seconds. In both environments it returns no data. In dev, it doesn't read any tables, in QA it does a full table scan of two tables to return nothing. The IO stats are attached.
I have checked the following:
*Server options are the same
*Code is the same
*Schema is the same
*Sql Server versions are the same
*Stats are fresh with high sampling ratios
*Stats steps counts are very close.
*Row counts are about same.
*Compile time is a little higher on QA but is only 1500 milliseconds
*Execution plan is the same.
*Indexes are all in good shape.
*Trace shows all the work being done on the same step. Dev just takes less because it isn't reading the tables.
Any guesses or suggestions of what to check or try next are welcome.
February 13, 2009 at 7:34 am
Another way to get an empty result set back is to issue the Set FMTONLY ON command at the start of the batch. Just remember to set it back to off.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2009 at 5:43 am
Hope both the server are of same configuration..?
March 17, 2009 at 1:49 pm
I am not sure whether I could help you with finding out the reason why it is happeing. But I would suggest to put 'If ' statement and return 'NULL' directly as the output when all the parameters are NULL.
March 17, 2009 at 2:14 pm
Jack suggestion was implemented and is working.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply