Whats your opinion on using complex sql statements within your code?

BrokenArrows

Im planning out a new development which will require data to be retrieved from a database.

Im considering whether to create one large sql query with lots of joins, unions and possibly a case or to create a few separate smaller and much simpler statements and build the data from within my code.

Whats your opinions. Im currents stuck in two minds. It would probably be easier to use a few smaller statements but not as efficient?

ciaranmac

BrokenArrows wrote: »

Im planning out a new development which will require data to be retrieved from a database.

Im considering whether to create one large sql query with lots of joins, unions and possibly a case or to create a few separate smaller and much simpler statements and build the data from within my code.

Whats your opinions. Im currents stuck in two minds. It would probably be easier to use a few smaller statements but not as efficient?

I'd suggest writing a stored procedure to run your query. It's almost always more efficient to let the server do the work, rather than retrieving multiple recordsets and processing them in client-side code. You can separate it into multiple statements that generate temporary tables, and then do further processing on those tables, all within a single stored procedure.

BrokenArrows

ciaranmac wrote: »

I'd suggest writing a stored procedure to run your query. It's almost always more efficient to let the server do the work, rather than retrieving multiple recordsets and processing them in client-side code. You can separate it into multiple statements that generate temporary tables, and then do further processing on those tables, all within a single stored procedure.

Good suggestion.

goldenhoarde

exactly what ciaranmac said!!! with the SP you can do what you like and also it will be indepenant of the code so changes would not require a rebuild (unless you change the input/output params)

Dar

I try to avoid hard-coding SQL unless absolutely necessary from a performance point of view. The problem is you end up extremely tightly coupled to the database schema, which means that any time you want to refactor the schema you will end up having to recheck all your hard-coded SQL statements to make sure you haven't broken anything.

Going the ORM route instead means that your schema details are only ever defined in one place, making changes a lot easier down the road. You can still use joins, unions etc, but now you are defining them at an object level which isn't directly tied to the underlying schema.

What language are you using?

What kind of performance requirements do you have?

BrokenArrows

C#

I dont have any performance requirements. Its a program which will be run once a day to do its job and runs in the background for a few seconds.

But in saying that i dont want to write something which is very inefficient even if it is just turning a 30 second job into a 45 second job.

Colonel Panic

You don't need to use stored procedures or an ORM library to manage SQL in code. You could just write a class to represent your data access code and that way, the implementation doesn't matter.

Draupnir

BrokenArrows wrote: »

C#

I dont have any performance requirements. Its a program which will be run once a day to do its job and runs in the background for a few seconds.

But in saying that i dont want to write something which is very inefficient even if it is just turning a 30 second job into a 45 second job.

I would have said that type of functionality would be achievable with stored procedures being run as a job by the SQL Server Agent. Do you have access to do that? Would save you writing any code for scheduling, logging or error handling and would be a lot easier to monitor and maintain.

DublinWriter

An even simpler approach would be to use views.

jmcc

There's good advice above. The main thing to remember when designing a web facing database backed service is that the trick is to simplify as much as possible, cache when possible and always limit what users can do.

Regards...jmcc