Or clear statistics :) Massive data growth can change a sproc's best intentions over time.
Placing select top 10000 in your query was good for testing speed but could lead to missing rows that you need over time. On Fri, Jul 24, 2020 at 9:08 AM Richard Kaye <rk...@invaluable.com> wrote: > With MSSQL it's all about execution plans and statistics. An SP is a known > quantity whereas SPT queries have to be evaluated each time they are run. > Regardless, a poorly written SP or one with a bad plan in the cache can > still perform badly. As Uncle Ted always tells us, you have to test in your > environment with your data. > > -- > > rk > > -----Original Message----- > From: ProfoxTech <profoxtech-boun...@leafe.com> On Behalf Of MB Software > Solutions, LLC > Sent: Friday, July 24, 2020 9:30 AM > To: profoxt...@leafe.com > Subject: Re: Unit tests (was Re: [NF] I will .....) > > I always thought stored procedures were faster?? > > On 7/24/2020 8:43 AM, Richard Kaye wrote: > > There are performance implications to using SPs, at least in MSSQL. > > > > -- > > > > rk > > > > -----Original Message----- > > From: ProfoxTech <profoxtech-boun...@leafe.com> On Behalf Of MB > > Software Solutions, LLC > > Sent: Thursday, July 23, 2020 10:27 PM > > To: profoxt...@leafe.com > > Subject: Re: Unit tests (was Re: [NF] I will .....) > > > > On 7/23/2020 8:07 PM, Stephen Russell wrote: > >> Pretty much for anything that is repetitive in nature. All apps yes. > >> I have the instances set to not allow homemade sql except for some of > >> our new stuff in R, or my certificate report that passes SQL to > >> include all of the Lot Numbers needed. It is on a separate sql > >> server and operates very slowly if I use a where in with code that > executed at the calling server. > >> > >> Why would you NOT use sprocs is my #1 question? > > > > Years ago there was the debate as to whether you put all the logic into > your backend database or DataObject class...I went for the latter. Gave me > easier portability if I ever needed it. (And actually, I did...I went from > VFP backends to MySQL/MariaDB 15 years ago. Required VERY LITTLE code > changes!) Now that doesn't mean I don't use stored procedure...I do! I > just don't use them for basic INSERT/UPDATE/DELETE statements. I do use > triggers though too. > > > > > > -- > > This email has been checked for viruses by Avast antivirus software. > > https://www.avast.com/antivirus > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/cajidmyk2bxx5kyabmnu4fqcdjkxhpwhzw2po3ixy4hpnbvc...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
