x

using sql from front end or back end, which one is better??

how to use sql, is the major question. which situations i should consider for using sql front end or back end.

more ▼

asked May 12, 2010 at 09:47 AM in Default

Dharmendra gravatar image

Dharmendra
44 7 7 7

can you rephrase the question please? - what do you call the front/back end. Are you referring to using the SSMS GUI vs T-SQL?
May 12, 2010 at 09:51 AM Fatherjack ♦♦
Yep. I'm stuck here too.
May 12, 2010 at 09:58 AM Grant Fritchey ♦♦

my question is if i am using java as front end then all i have to do with sql is create database with sql and all other searching and all i am doing with java code.

which is batter sql searchig or searching using java code ??

this is just example on my question.
May 12, 2010 at 10:03 AM Dharmendra
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You mean, do you use stored procedures or client-side dynamic SQL?

Short answer, stored procedures.

Longer answer, it kind of depends. If you're working with an ORM product that creates good paramaterized SQL statements (and by good, I mean statements that will create a single query plan, not X number of plans like Hibernate does), then a well-structured paramaterized query is effectively the same thing as a stored procedure, so this is OK too. If you're just building SQL statements within the Java code and tossing them over the wall at the SQL Server instance, no, that's a problem for any number of reasons, not the least being a horrible thing called SQL Injection. There is a lot more subtlety & nuance that can be applied, but in general, this is the advice I give my own development teams.

more ▼

answered May 12, 2010 at 10:12 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

this is something helpful, thanks
May 12, 2010 at 10:22 AM Dharmendra
(comments are locked)
10|1200 characters needed characters left

I agree with everything Grant says, as usual. But I think a couple of those nuances he mentioned are worth elaborating on.

First, remember that ORM's are generally harder to optimize and troubleshoot than a stored procedure. This is not to say that ORMs are not great under certain circumstances, but that you should be very aware of the trade offs involved. There is some discussion of this at: http://ask.sqlservercentral.com/questions/1094/orms-from-a-dbas-perspective

Next, I do think there are some rare occassions where it makes sense to write code that directly sends SQL to the SQL Instance. I do this for instance for a few scripts where it is generating dynamic SQL based on sophisticated string parsing that is hard to achieve inside of SQL. But I do this rarely and only on programs that are meant to be run by the DBA team. These will break horribly and intentionally if run by someone without a high level of permissions. And again, while I do see some cases where it makes sense to encode SQL like that they are the rare exception.

As a general rule, I once again agree with Grant that stored procedures are the way to go most of the time and that ORMs are good in some situations. I would in particular very strongly advise avoiding encoded SQL on any program that is end user facing and especially if that program runs with higher permissions than the user has natively.

more ▼

answered May 12, 2010 at 11:31 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

Excellent points. I used ORM's as my hanging point, but you could just be talking about any data access layer and the issues & rewards would be the same.
May 12, 2010 at 11:40 AM Grant Fritchey ♦♦
Bottom line - SQL is designed to do the heavy lifting, let it do its job.
May 12, 2010 at 02:11 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x348

asked: May 12, 2010 at 09:47 AM

Seen: 1772 times

Last Updated: May 12, 2010 at 10:51 AM