June 2, 2011 at 9:26 am
Rich Yarger (6/2/2011)
It's funny you mentioned the RECOMPILE option - I asked him about that, but found out that we are using 2005 and not 2008 (so I'm going to get slammed for this for being in the wrong forum), but if we are to re-factor this query, can you make a best recommendation of a direction I can have him move in to get the input parameters to work as they need to? In all honesty - I was thrown by the need to have the WHERE clause go with both AND and OR for saying IS NULL. I kind of thought that there would be something in the front end of the webapp that would eliminate that worry, but apparently he needs the database to have this flexibility.
Go read my blog post. It's on fixing exactly this kind of query so that it performs well.
P.P.S. On the DECIMAL precision question, I only know the static facts as they have been defined. I'm thinking that he just needs to eliminate the IS NULL part of the WHERE clause, and resolve that on the front end instead of in this query.
No.
He needs to go and fix the parameter of the stored proc. Decimal when declared without parameters is DECIMAL (38,0). Scale of 0 means no decimal places. Meaning that the value passed in is getting truncated and hence isn't matching. Removing the null check won't change the fact that the value is being silently truncated
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
June 2, 2011 at 9:31 am
Grrrrrrrrrrrrrrrr! I'm a flippin' idiot! Gail - I told him to tell me what he had defined the DECIMAL type as in his PROC, and sure enough - he hadn't. So guess what - he fixed that and now it works. I'm going to grab him by the ear to show him your blog. He thinks he is done now and is all happy.
:w00t:
June 2, 2011 at 9:34 am
Rich Yarger (6/2/2011)
I'm going to grab him by the ear to show him your blog. He thinks he is done now and is all happy.
I have a better idea. Require him to do performance tests with a couple hundred thousand rows and insist on a sub-second response time...
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
June 2, 2011 at 9:35 am
Actually, an even better idea...
Put it in prod and then in 4 or so months call me in to do a performance review... :hehe: :w00t:
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
June 2, 2011 at 9:39 am
😛
😀
🙂
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply