Re: Unit testing question and stored procedures

[Greg Keogh]

ata
>> sources.
>>
>> So I still see real wisdom in the industries practice of putting business
>> logic in the middle-tier.
>>
>> Especially in today's world where web-services and alternate sources of
>> data are the norm, and the quantity of data now being processed has forced
>> the consideration away from relational databases, and to consider more
>> scalable / cloud alternatives, such as No SQL databases, and Event Sourcing.
>>
>> Still, relational databases are the best fit in most cases, but I tend to
>> use light-weight performant ORMs such as PetaPoco - so database calls are
>> still written in SQL (and can be properly optimised by a competent DBA),
>> but I don't have to worry about mapping while being quite performant. And
>> you can always fall back to hand-written ADO.NET and a hand-written
>> mapper for absolute best performance (although I've not found I've needed
>> to).
>>
>> For Unit Testing, I either 'inject' the data-layer (so it can be faked,
>> or I use an in-memory database), or using a file-based database (so it can
>> be easily re-set).
>>
>> On 26 April 2017 at 13:42, Greg Keogh <gfke...@gmail.com> wrote:
>>
>>> I'm one of those ummm ... purists who reckons you shouldn't put anything
>>> (like business logic) in procs that needs unit testing -- *GK*
>>>
>>> On 26 April 2017 at 15:36, Greg Low (罗格雷格博士) <g...@greglow.com> wrote:
>>>
>>>> Yep, that’s how we do it. Some people use transactions to do a similar
>>>> thing but you can’t test transactional code by doing that.
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>>
>>>>
>>>> Greg
>>>>
>>>>
>>>>
>>>> Dr Greg Low
>>>>
>>>>
>>>>
>>>> 1300SQLSQL (1300 775 775) office | +61 419201410
>>>> <+61%20419%20201%20410> mobile│ +61 3 8676 4913 <+61%203%208676%204913>
>>>> fax
>>>>
>>>> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>>>>
>>>>
>>>>
>>>> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ozdot
>>>> net.com] *On Behalf Of *Tony Wright
>>>> *Sent:* Wednesday, 26 April 2017 3:08 PM
>>>> *To:* ozDotNet <ozdotnet@ozdotnet.com>
>>>> *Subject:* Re: Unit testing question and stored procedures
>>>>
>>>>
>>>>
>>>> So let me understand this. I believe what you are doing is having a
>>>> database snapshot (or testing database) that you can continuously revert to
>>>> its initial state, then you run the stored proc via nunit, then in the init
>>>> for the next test, revert back to the initial state and run that test,
>>>> etc.  I would have thought that it would take a lot of extra processing
>>>> time to run tests that way, especially if a restore is needed?
>>>>
>>>>
>>>>
>>>> I've used in memory databases (via the database first philosophy of EF
>>>> entity creation) but they don't handle stored procs.
>>>>
>>>>
>>>>
>>>> TSQLUnit looks...interesting. Must investigate.
>>>>
>>>>
>>>>
>>>> On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) <g...@greglow.com>
>>>> wrote:
>>>>
>>>> I should have added that the dac framework stuff had testing but has
>>>> now removed it.
>>>>
>>>>
>>>>
>>>> Some use TSQLUnit but I’ve not found it any more useful and NUnit fits
>>>> well with other testing.
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>>
>>>>
>>>> Greg
>>>>
>>>>
>>>>
>>>> Dr Greg Low
>>>>
>>>>
>>>>
>>>> 1300SQLSQL (1300 775 775) office | +61 419201410
>>>> <+61%20419%20201%20410> mobile│ +61 3 8676 4913 <+61%203%208676%204913>
>>>> fax
>>>>
>>>> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>>>>
>>>>
>>>>
>>>> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ozdot
>>>> net.com] *On Behalf Of *Tony Wright
>>>> *Sent:* Wednesday, 26 April 2017 11:53 AM
>>>> *To:* ozDotNet <ozdotnet@ozdotnet.com>
>>>> *Subject:* Unit tes

Re: Unit testing question and stored procedures

[Tony Wright]

greglow.com> wrote:
>>
>>> Yep, that’s how we do it. Some people use transactions to do a similar
>>> thing but you can’t test transactional code by doing that.
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> Greg
>>>
>>>
>>>
>>> Dr Greg Low
>>>
>>>
>>>
>>> 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410>
>>> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax
>>>
>>> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>>>
>>>
>>>
>>> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ozdot
>>> net.com] *On Behalf Of *Tony Wright
>>> *Sent:* Wednesday, 26 April 2017 3:08 PM
>>> *To:* ozDotNet <ozdotnet@ozdotnet.com>
>>> *Subject:* Re: Unit testing question and stored procedures
>>>
>>>
>>>
>>> So let me understand this. I believe what you are doing is having a
>>> database snapshot (or testing database) that you can continuously revert to
>>> its initial state, then you run the stored proc via nunit, then in the init
>>> for the next test, revert back to the initial state and run that test,
>>> etc.  I would have thought that it would take a lot of extra processing
>>> time to run tests that way, especially if a restore is needed?
>>>
>>>
>>>
>>> I've used in memory databases (via the database first philosophy of EF
>>> entity creation) but they don't handle stored procs.
>>>
>>>
>>>
>>> TSQLUnit looks...interesting. Must investigate.
>>>
>>>
>>>
>>> On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) <g...@greglow.com>
>>> wrote:
>>>
>>> I should have added that the dac framework stuff had testing but has now
>>> removed it.
>>>
>>>
>>>
>>> Some use TSQLUnit but I’ve not found it any more useful and NUnit fits
>>> well with other testing.
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> Greg
>>>
>>>
>>>
>>> Dr Greg Low
>>>
>>>
>>>
>>> 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410>
>>> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax
>>>
>>> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>>>
>>>
>>>
>>> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ozdot
>>> net.com] *On Behalf Of *Tony Wright
>>> *Sent:* Wednesday, 26 April 2017 11:53 AM
>>> *To:* ozDotNet <ozdotnet@ozdotnet.com>
>>> *Subject:* Unit testing question and stored procedures
>>>
>>>
>>>
>>> Hi all,
>>>
>>>
>>>
>>> A while ago, we were discussing avoiding using LINQ to query sql server.
>>> The preferred method of querying discussed was either to use direct SQL
>>> calls or stored procs to perform data manipulation.
>>>
>>>
>>>
>>> This was because the overhead of starting up Entity Framework is
>>> significant and the underlying queries produced by LINQ can be quite
>>> convoluted and inefficient. Lazy loading is also something to be avoided
>>> (at the very least you should be using Eager loading – which forced you to
>>> be explicit about what related data is being included/loaded. As an aside,
>>> I’ve also seen a massive performance drop when using mappers to covert
>>> database objects in EF to POCO objects using tools such as AutoMapper.)
>>>
>>>
>>>
>>> Add to this, that putting some business logic in stored procs is about
>>> the most efficient way to perform data manipulation in a SQL Server
>>> database. It is unbelievably fast and efficient compared to passing all the
>>> data over the wire to your middle tier to perform any updates and then
>>> passing it back to commit the data to the database.
>>>
>>>
>>>
>>> In fact, I would argue that the very fact that current “best practice”
>>> is to inefficiently pass all your data to the middle-tier to be modified,
>>> only to be returned to the database for the update, is a failure in modern
>>> development, but of course, there is not really an alternative if your
>>> intent is to performing proper unit testing. It is a very sad thing that
>>> modern enterprise development has not worked out how to utilise the full
>>> power of SQL Server other than to say "only use stored procs in special
>>> cases."
>>>
>>>
>>>
>>> So the question I have is, if it was decided to put business logic in
>>> stored procedures (and some of you have, I know, even though a few of you
>>> with the purist hat would deny it!), how do people currently unit test
>>> their stored procs?
>>>
>>>
>>>
>>> Kind regards,
>>>
>>> Tony
>>>
>>>
>>>
>>
>>
>

Re: Unit testing question and stored procedures

[Nathan Schultz]

If performance is essential, then I agree that stored procedures are going
to win, but in my experience it is not without major trade-offs.

I once worked on a large business critical project where all business logic
was in stored procedures, due to the reasons you have addressed above.

Some of the problems we faced were:
- It was a while ago, and the database was Ingres. Ingres was expensive,
but the difficulty (and so cost) in migrating to SQL Server meant it was
not economical for the business to migrate. This trapped the business into
paying a fortune on licensing.
- While the application indeed was performant, it quickly reached the
limits of scalability under heavy use (as it became more and more costly to
scale the data tier). The company ended up paying a huge sum at the time
for a beast of a server to host the database to get it to cope with demand
during the heaviest periods. Meanwhile, scaling the middle-tier would not
have been a problem, and it would have taken a fair bit of burden off the
data-tier.
- Business logic in stored procedures meant large atomic transactions, and
under heavy load this meant that deadlocks were common (although I'd admit
that this is more of a problem with how the stored procedures were
implemented, rather than their actual use).
- Back in those days, web-services were just becoming popular, and when
your business logic is in the data-tier, we found it to be a real headache
to ensure data integrity when integrating web-services and other data
sources.

So I still see real wisdom in the industries practice of putting business
logic in the middle-tier.

Especially in today's world where web-services and alternate sources of
data are the norm, and the quantity of data now being processed has forced
the consideration away from relational databases, and to consider more
scalable / cloud alternatives, such as No SQL databases, and Event Sourcing.

Still, relational databases are the best fit in most cases, but I tend to
use light-weight performant ORMs such as PetaPoco - so database calls are
still written in SQL (and can be properly optimised by a competent DBA),
but I don't have to worry about mapping while being quite performant. And
you can always fall back to hand-written ADO.NET and a hand-written mapper
for absolute best performance (although I've not found I've needed to).

For Unit Testing, I either 'inject' the data-layer (so it can be faked, or
I use an in-memory database), or using a file-based database (so it can be
easily re-set).

On 26 April 2017 at 13:42, Greg Keogh <gfke...@gmail.com> wrote:

> I'm one of those ummm ... purists who reckons you shouldn't put anything
> (like business logic) in procs that needs unit testing -- *GK*
>
> On 26 April 2017 at 15:36, Greg Low (罗格雷格博士) <g...@greglow.com> wrote:
>
>> Yep, that’s how we do it. Some people use transactions to do a similar
>> thing but you can’t test transactional code by doing that.
>>
>>
>>
>> Regards,
>>
>>
>>
>> Greg
>>
>>
>>
>> Dr Greg Low
>>
>>
>>
>> 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410>
>> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax
>>
>> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>>
>>
>>
>> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ozdot
>> net.com] *On Behalf Of *Tony Wright
>> *Sent:* Wednesday, 26 April 2017 3:08 PM
>> *To:* ozDotNet <ozdotnet@ozdotnet.com>
>> *Subject:* Re: Unit testing question and stored procedures
>>
>>
>>
>> So let me understand this. I believe what you are doing is having a
>> database snapshot (or testing database) that you can continuously revert to
>> its initial state, then you run the stored proc via nunit, then in the init
>> for the next test, revert back to the initial state and run that test,
>> etc.  I would have thought that it would take a lot of extra processing
>> time to run tests that way, especially if a restore is needed?
>>
>>
>>
>> I've used in memory databases (via the database first philosophy of EF
>> entity creation) but they don't handle stored procs.
>>
>>
>>
>> TSQLUnit looks...interesting. Must investigate.
>>
>>
>>
>> On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) <g...@greglow.com>
>> wrote:
>>
>> I should have added that the dac framework stuff had testing but has now
>> removed it.
>>
>>
>>
>> Some use TSQLUnit but I’ve not found it any more useful and NUnit fits
>> well with other testing.
>>
>>
>>
>> Regards,
>>
>>
>>
>> Greg
>>
>>
>>
>> Dr Greg Low
>>

Re: Unit testing question and stored procedures

[Greg Keogh]

I'm one of those ummm ... purists who reckons you shouldn't put anything
(like business logic) in procs that needs unit testing -- *GK*

On 26 April 2017 at 15:36, Greg Low (罗格雷格博士) <g...@greglow.com> wrote:

> Yep, that’s how we do it. Some people use transactions to do a similar
> thing but you can’t test transactional code by doing that.
>
>
>
> Regards,
>
>
>
> Greg
>
>
>
> Dr Greg Low
>
>
>
> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913
> fax
>
> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@
> ozdotnet.com] *On Behalf Of *Tony Wright
> *Sent:* Wednesday, 26 April 2017 3:08 PM
> *To:* ozDotNet <ozdotnet@ozdotnet.com>
> *Subject:* Re: Unit testing question and stored procedures
>
>
>
> So let me understand this. I believe what you are doing is having a
> database snapshot (or testing database) that you can continuously revert to
> its initial state, then you run the stored proc via nunit, then in the init
> for the next test, revert back to the initial state and run that test,
> etc.  I would have thought that it would take a lot of extra processing
> time to run tests that way, especially if a restore is needed?
>
>
>
> I've used in memory databases (via the database first philosophy of EF
> entity creation) but they don't handle stored procs.
>
>
>
> TSQLUnit looks...interesting. Must investigate.
>
>
>
> On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) <g...@greglow.com>
> wrote:
>
> I should have added that the dac framework stuff had testing but has now
> removed it.
>
>
>
> Some use TSQLUnit but I’ve not found it any more useful and NUnit fits
> well with other testing.
>
>
>
> Regards,
>
>
>
> Greg
>
>
>
> Dr Greg Low
>
>
>
> 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410>
> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax
>
> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@
> ozdotnet.com] *On Behalf Of *Tony Wright
> *Sent:* Wednesday, 26 April 2017 11:53 AM
> *To:* ozDotNet <ozdotnet@ozdotnet.com>
> *Subject:* Unit testing question and stored procedures
>
>
>
> Hi all,
>
>
>
> A while ago, we were discussing avoiding using LINQ to query sql server.
> The preferred method of querying discussed was either to use direct SQL
> calls or stored procs to perform data manipulation.
>
>
>
> This was because the overhead of starting up Entity Framework is
> significant and the underlying queries produced by LINQ can be quite
> convoluted and inefficient. Lazy loading is also something to be avoided
> (at the very least you should be using Eager loading – which forced you to
> be explicit about what related data is being included/loaded. As an aside,
> I’ve also seen a massive performance drop when using mappers to covert
> database objects in EF to POCO objects using tools such as AutoMapper.)
>
>
>
> Add to this, that putting some business logic in stored procs is about the
> most efficient way to perform data manipulation in a SQL Server database.
> It is unbelievably fast and efficient compared to passing all the data over
> the wire to your middle tier to perform any updates and then passing it
> back to commit the data to the database.
>
>
>
> In fact, I would argue that the very fact that current “best practice” is
> to inefficiently pass all your data to the middle-tier to be modified, only
> to be returned to the database for the update, is a failure in modern
> development, but of course, there is not really an alternative if your
> intent is to performing proper unit testing. It is a very sad thing that
> modern enterprise development has not worked out how to utilise the full
> power of SQL Server other than to say "only use stored procs in special
> cases."
>
>
>
> So the question I have is, if it was decided to put business logic in
> stored procedures (and some of you have, I know, even though a few of you
> with the purist hat would deny it!), how do people currently unit test
> their stored procs?
>
>
>
> Kind regards,
>
> Tony
>
>
>

RE: Unit testing question and stored procedures

[罗格雷格博士]

Yep, that’s how we do it. Some people use transactions to do a similar thing 
but you can’t test transactional code by doing that.

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> 
|http://greglow.me<http://greglow.me/>

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Tony Wright
Sent: Wednesday, 26 April 2017 3:08 PM
To: ozDotNet <ozdotnet@ozdotnet.com>
Subject: Re: Unit testing question and stored procedures

So let me understand this. I believe what you are doing is having a database 
snapshot (or testing database) that you can continuously revert to its initial 
state, then you run the stored proc via nunit, then in the init for the next 
test, revert back to the initial state and run that test, etc.  I would have 
thought that it would take a lot of extra processing time to run tests that 
way, especially if a restore is needed?

I've used in memory databases (via the database first philosophy of EF entity 
creation) but they don't handle stored procs.

TSQLUnit looks...interesting. Must investigate.

On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) 
<g...@greglow.com<mailto:g...@greglow.com>> wrote:
I should have added that the dac framework stuff had testing but has now 
removed it.

Some use TSQLUnit but I’ve not found it any more useful and NUnit fits well 
with other testing.

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410<tel:+61%20419%20201%20410> 
mobile│ +61 3 8676 4913<tel:+61%203%208676%204913> fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> 
|http://greglow.me<http://greglow.me/>

From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> 
[mailto:ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com>] On 
Behalf Of Tony Wright
Sent: Wednesday, 26 April 2017 11:53 AM
To: ozDotNet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>>
Subject: Unit testing question and stored procedures

Hi all,

A while ago, we were discussing avoiding using LINQ to query sql server. The 
preferred method of querying discussed was either to use direct SQL calls or 
stored procs to perform data manipulation.

This was because the overhead of starting up Entity Framework is significant 
and the underlying queries produced by LINQ can be quite convoluted and 
inefficient. Lazy loading is also something to be avoided (at the very least 
you should be using Eager loading – which forced you to be explicit about what 
related data is being included/loaded. As an aside, I’ve also seen a massive 
performance drop when using mappers to covert database objects in EF to POCO 
objects using tools such as AutoMapper.)

Add to this, that putting some business logic in stored procs is about the most 
efficient way to perform data manipulation in a SQL Server database. It is 
unbelievably fast and efficient compared to passing all the data over the wire 
to your middle tier to perform any updates and then passing it back to commit 
the data to the database.

In fact, I would argue that the very fact that current “best practice” is to 
inefficiently pass all your data to the middle-tier to be modified, only to be 
returned to the database for the update, is a failure in modern development, 
but of course, there is not really an alternative if your intent is to 
performing proper unit testing. It is a very sad thing that modern enterprise 
development has not worked out how to utilise the full power of SQL Server 
other than to say "only use stored procs in special cases."

So the question I have is, if it was decided to put business logic in stored 
procedures (and some of you have, I know, even though a few of you with the 
purist hat would deny it!), how do people currently unit test their stored 
procs?

Kind regards,
Tony

Re: Unit testing question and stored procedures

[Tony Wright]

So let me understand this. I believe what you are doing is having a
database snapshot (or testing database) that you can continuously revert to
its initial state, then you run the stored proc via nunit, then in the init
for the next test, revert back to the initial state and run that test,
etc.  I would have thought that it would take a lot of extra processing
time to run tests that way, especially if a restore is needed?

I've used in memory databases (via the database first philosophy of EF
entity creation) but they don't handle stored procs.

TSQLUnit looks...interesting. Must investigate.

On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) 
wrote:

> I should have added that the dac framework stuff had testing but has now
> removed it.
>
>
>
> Some use TSQLUnit but I’ve not found it any more useful and NUnit fits
> well with other testing.
>
>
>
> Regards,
>
>
>
> Greg
>
>
>
> Dr Greg Low
>
>
>
> 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410>
> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax
>
> SQL Down Under | Web: www.sqldownunder.com |http://greglow.me
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@
> ozdotnet.com] *On Behalf Of *Tony Wright
> *Sent:* Wednesday, 26 April 2017 11:53 AM
> *To:* ozDotNet 
> *Subject:* Unit testing question and stored procedures
>
>
>
> Hi all,
>
>
>
> A while ago, we were discussing avoiding using LINQ to query sql server.
> The preferred method of querying discussed was either to use direct SQL
> calls or stored procs to perform data manipulation.
>
>
>
> This was because the overhead of starting up Entity Framework is
> significant and the underlying queries produced by LINQ can be quite
> convoluted and inefficient. Lazy loading is also something to be avoided
> (at the very least you should be using Eager loading – which forced you to
> be explicit about what related data is being included/loaded. As an aside,
> I’ve also seen a massive performance drop when using mappers to covert
> database objects in EF to POCO objects using tools such as AutoMapper.)
>
>
>
> Add to this, that putting some business logic in stored procs is about the
> most efficient way to perform data manipulation in a SQL Server database.
> It is unbelievably fast and efficient compared to passing all the data over
> the wire to your middle tier to perform any updates and then passing it
> back to commit the data to the database.
>
>
>
> In fact, I would argue that the very fact that current “best practice” is
> to inefficiently pass all your data to the middle-tier to be modified, only
> to be returned to the database for the update, is a failure in modern
> development, but of course, there is not really an alternative if your
> intent is to performing proper unit testing. It is a very sad thing that
> modern enterprise development has not worked out how to utilise the full
> power of SQL Server other than to say "only use stored procs in special
> cases."
>
>
>
> So the question I have is, if it was decided to put business logic in
> stored procedures (and some of you have, I know, even though a few of you
> with the purist hat would deny it!), how do people currently unit test
> their stored procs?
>
>
>
> Kind regards,
>
> Tony
>

RE: Unit testing question and stored procedures

[罗格雷格博士]

I should have added that the dac framework stuff had testing but has now 
removed it.

Some use TSQLUnit but I’ve not found it any more useful and NUnit fits well 
with other testing.

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com 
|http://greglow.me

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Tony Wright
Sent: Wednesday, 26 April 2017 11:53 AM
To: ozDotNet 
Subject: Unit testing question and stored procedures

Hi all,

A while ago, we were discussing avoiding using LINQ to query sql server. The 
preferred method of querying discussed was either to use direct SQL calls or 
stored procs to perform data manipulation.

This was because the overhead of starting up Entity Framework is significant 
and the underlying queries produced by LINQ can be quite convoluted and 
inefficient. Lazy loading is also something to be avoided (at the very least 
you should be using Eager loading – which forced you to be explicit about what 
related data is being included/loaded. As an aside, I’ve also seen a massive 
performance drop when using mappers to covert database objects in EF to POCO 
objects using tools such as AutoMapper.)

Add to this, that putting some business logic in stored procs is about the most 
efficient way to perform data manipulation in a SQL Server database. It is 
unbelievably fast and efficient compared to passing all the data over the wire 
to your middle tier to perform any updates and then passing it back to commit 
the data to the database.

In fact, I would argue that the very fact that current “best practice” is to 
inefficiently pass all your data to the middle-tier to be modified, only to be 
returned to the database for the update, is a failure in modern development, 
but of course, there is not really an alternative if your intent is to 
performing proper unit testing. It is a very sad thing that modern enterprise 
development has not worked out how to utilise the full power of SQL Server 
other than to say "only use stored procs in special cases."

So the question I have is, if it was decided to put business logic in stored 
procedures (and some of you have, I know, even though a few of you with the 
purist hat would deny it!), how do people currently unit test their stored 
procs?

Kind regards,
Tony

RE: Unit testing question and stored procedures

[罗格雷格博士]

Hi Tony,

I’d still just use something like NUnit, along with all the other tests in your 
.NET code. Just put a wrapper calling them. Makes it easy to integrate with 
other tests.

One thing that I often do as well, is to have a wrapper that uses database 
snapshots, to get the test DB back into exactly the right state before each 
test. (the one exception is when testing performance)

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com 
|http://greglow.me

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Tony Wright
Sent: Wednesday, 26 April 2017 11:53 AM
To: ozDotNet 
Subject: Unit testing question and stored procedures

Hi all,

A while ago, we were discussing avoiding using LINQ to query sql server. The 
preferred method of querying discussed was either to use direct SQL calls or 
stored procs to perform data manipulation.

This was because the overhead of starting up Entity Framework is significant 
and the underlying queries produced by LINQ can be quite convoluted and 
inefficient. Lazy loading is also something to be avoided (at the very least 
you should be using Eager loading – which forced you to be explicit about what 
related data is being included/loaded. As an aside, I’ve also seen a massive 
performance drop when using mappers to covert database objects in EF to POCO 
objects using tools such as AutoMapper.)

Add to this, that putting some business logic in stored procs is about the most 
efficient way to perform data manipulation in a SQL Server database. It is 
unbelievably fast and efficient compared to passing all the data over the wire 
to your middle tier to perform any updates and then passing it back to commit 
the data to the database.

In fact, I would argue that the very fact that current “best practice” is to 
inefficiently pass all your data to the middle-tier to be modified, only to be 
returned to the database for the update, is a failure in modern development, 
but of course, there is not really an alternative if your intent is to 
performing proper unit testing. It is a very sad thing that modern enterprise 
development has not worked out how to utilise the full power of SQL Server 
other than to say "only use stored procs in special cases."

So the question I have is, if it was decided to put business logic in stored 
procedures (and some of you have, I know, even though a few of you with the 
purist hat would deny it!), how do people currently unit test their stored 
procs?

Kind regards,
Tony