Hello,
Is there any difference to between SLQ string in the code and call execute
query and call a stored procedure and execute the query that way concerning
speed, effectiveness, reliability, …?
Thanks,
Jim.
7  3205 
Stored procedures are compiled, so you should see improvement there. How
much improvement and how noticeable really all depends on a number of
factors.
"JIM.H." <JI**@discussio ns.microsoft.co m> wrote in message
news:C9******** *************** ***********@mic rosoft.com... Hello,
Is there any difference to between SLQ string in the code and call execute
query and call a stored procedure and execute the query that way
concerning
speed, effectiveness, reliability, .?
Thanks,
Jim.
SQL Server caching of queries has reduced the overall performance
gain of procedures versus dynamic sql strings. Procedures
in most cases will still be faster.
They are also "far more" secure than dynamic sql strings.
--
2004 and 2005 Microsoft MVP C#
Robbe Morris http://www.robbemorris.com http://www.masterado.net
"JIM.H." <JI**@discussio ns.microsoft.co m> wrote in message
news:C9******** *************** ***********@mic rosoft.com... Hello,
Is there any difference to between SLQ string in the code and call execute
query and call a stored procedure and execute the query that way
concerning
speed, effectiveness, reliability, .?
Thanks,
Jim.
I have to strongly disagree with the two other respondants. The real answer
is that it totally depends. To say that stored procedures are "far more
secure" is extremely misleading. By simply using parameterized values in
your dynamic SQL you've achieved high security. Conversly, by using execute
in your stored procedure it's not at all secure.
As far as performance, it really depends on what you are doing. That SQL
Server caches queries is a very misleading generalization. Complexe queries
are often uncacheable. Additionally, client-side (ASP.Net) caching can
belittle this minor advantage. What's worse, the most efficient search
queries are typically written in one form or another of dynamic sql
(otherwise SQL Server can't take advantage of indexes).
Personally I'm a fan of stored procedures (big time), but blanket statements
are dangerous. Saying that stored procedures are secure dangerously risks
taking the responsability away from the developer. Additionally, different
solutions are better suited for different scenarios. As such, only someone
who doesn't subscribe to blanket statements can make the right decision.
Let's look at a simplified example:
create procedure SearchProperty AS
@ListingType INT --pass 0 to search for all listing types
select listingId, listingName from Property
where (@listingType= 0 OR ListingType = @ListingType)
AND Status = 1
In the above example we need to allow 0 to be passed in to mean any listing.
This will likely result in SQL Server being unable to use an index on the
ListingType column, thus resulting in less that optimal performance. To get
ideal performance, we'd need to if the code:
if @ListingType = 0 BEGIN
select listingId, listingName from Property
WHERE Status = 1
END ELSE BEGIN
select listingId, listingName from Property
WHERE ListingType = @ListingType AND Status = 1
END
Now you are getting somewhere...exc ept that it'll get impossible to maintain
with the addition of just a few more search parameters (and query plan
caching will increasingly become less efficient).
We could use execute:
declare @sql varchar(1024)
set @sql = 'select listingId, listingName from Property WHERE Status = 1'
if @ListingType = 0 BEGIN
SET @sql = @sql + ' AND ListingType = ' + @ListingTypeId
END
exec(@sql)
Looky that, a stored procedure and a HUGE potential for SQL
injection...ega ds! (yes, the above sproc won't actually work but if
@ListingTypeID was a varchar (say comma-separated values) it would).
This simple, and common, example not only illustrates that stored procedures
aren't necessarily faster and just vulnerable, but that they can also be
harder to maintain.
In short, the correct answer is "it depends" and hopefully as you learn more
you'll learn to use the right solution at the right time :)
Karl
--
MY ASP.Net tutorials http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying) http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"JIM.H." <JI**@discussio ns.microsoft.co m> wrote in message
news:C9******** *************** ***********@mic rosoft.com... Hello,
Is there any difference to between SLQ string in the code and call execute
query and call a stored procedure and execute the query that way
concerning
speed, effectiveness, reliability, .?
Thanks,
Jim.
I couldn't agree with you more about blanket statements... implying that
proper research and understanding is in order. But I do have a question
regarding your statement:
<< the most efficient search queries are typically written in one form or
another of dynamic sql (otherwise SQL Server can't take advantage of
indexes).>>
I don't understand this. You seem to be saying that SQL Server can use
indexes *only* when fed dynamic SQL. I don't think that's true. Can you
explain further? I guess it's the "one form or another" part that I don't
get. How many forms of dynamic SQL are there?
Thanks.
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:ua******** ******@TK2MSFTN GP15.phx.gbl... I have to strongly disagree with the two other respondants. The real
answer is that it totally depends. To say that stored procedures are "far
more secure" is extremely misleading. By simply using parameterized values
in your dynamic SQL you've achieved high security. Conversly, by using
execute in your stored procedure it's not at all secure.
As far as performance, it really depends on what you are doing. That SQL
Server caches queries is a very misleading generalization. Complexe
queries are often uncacheable. Additionally, client-side (ASP.Net) caching
can belittle this minor advantage. What's worse, the most efficient
search queries are typically written in one form or another of dynamic sql
(otherwise SQL Server can't take advantage of indexes).
Personally I'm a fan of stored procedures (big time), but blanket
statements are dangerous. Saying that stored procedures are secure
dangerously risks taking the responsability away from the developer.
Additionally, different solutions are better suited for different
scenarios. As such, only someone who doesn't subscribe to blanket
statements can make the right decision.
Let's look at a simplified example:
create procedure SearchProperty AS
@ListingType INT --pass 0 to search for all listing types
select listingId, listingName from Property
where (@listingType= 0 OR ListingType = @ListingType)
AND Status = 1
In the above example we need to allow 0 to be passed in to mean any
listing. This will likely result in SQL Server being unable to use an
index on the ListingType column, thus resulting in less that optimal
performance. To get ideal performance, we'd need to if the code:
if @ListingType = 0 BEGIN
select listingId, listingName from Property
WHERE Status = 1
END ELSE BEGIN
select listingId, listingName from Property
WHERE ListingType = @ListingType AND Status = 1
END
Now you are getting somewhere...exc ept that it'll get impossible to
maintain with the addition of just a few more search parameters (and query
plan caching will increasingly become less efficient).
We could use execute:
declare @sql varchar(1024)
set @sql = 'select listingId, listingName from Property WHERE Status = 1'
if @ListingType = 0 BEGIN
SET @sql = @sql + ' AND ListingType = ' + @ListingTypeId
END
exec(@sql)
Looky that, a stored procedure and a HUGE potential for SQL
injection...ega ds! (yes, the above sproc won't actually work but if
@ListingTypeID was a varchar (say comma-separated values) it would).
This simple, and common, example not only illustrates that stored
procedures aren't necessarily faster and just vulnerable, but that they
can also be harder to maintain.
In short, the correct answer is "it depends" and hopefully as you learn
more you'll learn to use the right solution at the right time :)
Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"JIM.H." <JI**@discussio ns.microsoft.co m> wrote in message
news:C9******** *************** ***********@mic rosoft.com... Hello,
Is there any difference to between SLQ string in the code and call
execute
query and call a stored procedure and execute the query that way
concerning
speed, effectiveness, reliability, .?
Thanks,
Jim.
Teehee...I made my own blanket statement ;) but atleast I had the word
"typically" in there.
But I can explain what I was saying in more detail. I was specifically
refering to cases where some search parameters are optional, which in my
experience is almost always a requirement. Without using dynamic sql (I
typically use sp_executesql instead of building it in the DAL but that's
just implementation) , you are forced to either write a lot of IF/ELSE (which
is totally unacceptable for maintenance), or you write statements like AND
(@SomeParameter IS NULL OR SomeColumn LIKE @SomeParameter)
Using the above approach is sweet because it lets you avoid huge if/elses
AND also avoids dynamic sql. However, SQL server couldn't take advantage of
any indexes on SomeColumn in the above case. Don't get me wrong, it isn't
necessarily a bad approach, simply one which might have an unknown
side-effects (indexes are largely ignored). With dynamic SQL you're query
would either contain the SomeColumn LIKE @SomeParameter or not (determined
at runtime)...it wouldn't check if @SomeParameter IS NULL and wouldn't need
to OR (which I believe is why an index can't be used).
I can't recommend this article enough if your specific question is about
searches: http://www.sommarskog.se/dyn-search.html it's totally unbiased and
contains no blanket statements...it 'll purely educate you and give you
information necessary to making the right choice.
Hope that cleared some of the muck I said..
Karl
--
MY ASP.Net tutorials http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying) http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"PB" <A@B.com> wrote in message
news:uf******** ******@TK2MSFTN GP10.phx.gbl... I couldn't agree with you more about blanket statements... implying that
proper research and understanding is in order. But I do have a question
regarding your statement:
<< the most efficient search queries are typically written in one form or
another of dynamic sql (otherwise SQL Server can't take advantage of
indexes).>>
I don't understand this. You seem to be saying that SQL Server can use
indexes *only* when fed dynamic SQL. I don't think that's true. Can you
explain further? I guess it's the "one form or another" part that I don't
get. How many forms of dynamic SQL are there?
Thanks.
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:ua******** ******@TK2MSFTN GP15.phx.gbl...I have to strongly disagree with the two other respondants. The real
answer is that it totally depends. To say that stored procedures are "far
more secure" is extremely misleading. By simply using parameterized
values in your dynamic SQL you've achieved high security. Conversly, by
using execute in your stored procedure it's not at all secure.
As far as performance, it really depends on what you are doing. That SQL
Server caches queries is a very misleading generalization. Complexe
queries are often uncacheable. Additionally, client-side (ASP.Net)
caching can belittle this minor advantage. What's worse, the most
efficient search queries are typically written in one form or another of
dynamic sql (otherwise SQL Server can't take advantage of indexes).
Personally I'm a fan of stored procedures (big time), but blanket
statements are dangerous. Saying that stored procedures are secure
dangerously risks taking the responsability away from the developer.
Additionally, different solutions are better suited for different
scenarios. As such, only someone who doesn't subscribe to blanket
statements can make the right decision.
Let's look at a simplified example:
create procedure SearchProperty AS
@ListingType INT --pass 0 to search for all listing types
select listingId, listingName from Property
where (@listingType= 0 OR ListingType = @ListingType)
AND Status = 1
In the above example we need to allow 0 to be passed in to mean any
listing. This will likely result in SQL Server being unable to use an
index on the ListingType column, thus resulting in less that optimal
performance. To get ideal performance, we'd need to if the code:
if @ListingType = 0 BEGIN
select listingId, listingName from Property
WHERE Status = 1
END ELSE BEGIN
select listingId, listingName from Property
WHERE ListingType = @ListingType AND Status = 1
END
Now you are getting somewhere...exc ept that it'll get impossible to
maintain with the addition of just a few more search parameters (and
query plan caching will increasingly become less efficient).
We could use execute:
declare @sql varchar(1024)
set @sql = 'select listingId, listingName from Property WHERE Status = 1'
if @ListingType = 0 BEGIN
SET @sql = @sql + ' AND ListingType = ' + @ListingTypeId
END
exec(@sql)
Looky that, a stored procedure and a HUGE potential for SQL
injection...ega ds! (yes, the above sproc won't actually work but if
@ListingTypeID was a varchar (say comma-separated values) it would).
This simple, and common, example not only illustrates that stored
procedures aren't necessarily faster and just vulnerable, but that they
can also be harder to maintain.
In short, the correct answer is "it depends" and hopefully as you learn
more you'll learn to use the right solution at the right time :)
Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"JIM.H." <JI**@discussio ns.microsoft.co m> wrote in message
news:C9******** *************** ***********@mic rosoft.com... Hello,
Is there any difference to between SLQ string in the code and call
execute
query and call a stored procedure and execute the query that way
concerning
speed, effectiveness, reliability, .?
Thanks,
Jim.
He said "direct execute sql".
I took this to be executing an entirely dynamically
create sql string. Use of parameters would not
help in this scenario. You are correct in your
assessment when parameterized command objects
are used.
I also said that SQL Server's caching "reduced"
the overall performance gain as a general statement which
is true.
And, I did say the procedures were still faster
in most cases.
--
2004 and 2005 Microsoft MVP C#
Robbe Morris http://www.robbemorris.com http://www.masterado.net
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:ua******** ******@TK2MSFTN GP15.phx.gbl... I have to strongly disagree with the two other respondants. The real
answer is that it totally depends. To say that stored procedures are "far
more secure" is extremely misleading. By simply using parameterized values
in your dynamic SQL you've achieved high security. Conversly, by using
execute in your stored procedure it's not at all secure.
As far as performance, it really depends on what you are doing. That SQL
Server caches queries is a very misleading generalization. Complexe
queries are often uncacheable. Additionally, client-side (ASP.Net) caching
can belittle this minor advantage. What's worse, the most efficient
search queries are typically written in one form or another of dynamic sql
(otherwise SQL Server can't take advantage of indexes).
Personally I'm a fan of stored procedures (big time), but blanket
statements are dangerous. Saying that stored procedures are secure
dangerously risks taking the responsability away from the developer.
Additionally, different solutions are better suited for different
scenarios. As such, only someone who doesn't subscribe to blanket
statements can make the right decision.
Let's look at a simplified example:
create procedure SearchProperty AS
@ListingType INT --pass 0 to search for all listing types
select listingId, listingName from Property
where (@listingType= 0 OR ListingType = @ListingType)
AND Status = 1
In the above example we need to allow 0 to be passed in to mean any
listing. This will likely result in SQL Server being unable to use an
index on the ListingType column, thus resulting in less that optimal
performance. To get ideal performance, we'd need to if the code:
if @ListingType = 0 BEGIN
select listingId, listingName from Property
WHERE Status = 1
END ELSE BEGIN
select listingId, listingName from Property
WHERE ListingType = @ListingType AND Status = 1
END
Now you are getting somewhere...exc ept that it'll get impossible to
maintain with the addition of just a few more search parameters (and query
plan caching will increasingly become less efficient).
We could use execute:
declare @sql varchar(1024)
set @sql = 'select listingId, listingName from Property WHERE Status = 1'
if @ListingType = 0 BEGIN
SET @sql = @sql + ' AND ListingType = ' + @ListingTypeId
END
exec(@sql)
Looky that, a stored procedure and a HUGE potential for SQL
injection...ega ds! (yes, the above sproc won't actually work but if
@ListingTypeID was a varchar (say comma-separated values) it would).
This simple, and common, example not only illustrates that stored
procedures aren't necessarily faster and just vulnerable, but that they
can also be harder to maintain.
In short, the correct answer is "it depends" and hopefully as you learn
more you'll learn to use the right solution at the right time :)
Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"JIM.H." <JI**@discussio ns.microsoft.co m> wrote in message
news:C9******** *************** ***********@mic rosoft.com... Hello,
Is there any difference to between SLQ string in the code and call
execute
query and call a stored procedure and execute the query that way
concerning
speed, effectiveness, reliability, .?
Thanks,
Jim.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: dinesh prasad |
last post by:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to...
|
by: Zeke Hoskin |
last post by:
Suddenly a stored procedure, very much like several others, is giving
EXECUTE permission denied on object 'Add_Adjustment', database
'InStab', owner 'zhoskin'.
server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.
I'm zhoskin. I am the dbo and created the procedure, and when I look
at its properties, I have EXEC permission. Line 18 is just the return
statement. The values are all appropriate for the table. So what is...
|
by: dtwilliams |
last post by:
OK, i'm trying to do some error checking on stored procedures and am
following the advise in Erland Sommarskog's 'Implementing Error
Handling with Stored Procedures' document.
Can anybody help with my stored procedures and why it keeps erroring at
the '-- Create new Address Detail stage'? The errorCode value that is
being return in my web app is 0, so i'm not even sure why it's even
raising the error!!
Rather than executing the INSERT...
|
by: Paul Hale |
last post by:
Hi all,
being new to c# I'm trying to find the best way of passing multiple records
to insert into a sql database via a stored procedure.
I'm using visual studio 2005 RC
SQL server 2005 and C# of course.
My scenario is as follows:
I have a multiline textbox with which users can enter numerous items(one per
line) and all are then added to an Arraylist and then seperatly validated
using a Regex.
Once all valid entries are collated I would...
|
by: Thomas Beyerlein |
last post by:
I am building a UI, I am using stored procedures for executing SQL
commands on the server. When I ran a insert stored procedure it returned
me a error message that it could not find a parameter that I sent it, it
was sent but in the building of the parameter the first and second
parameters places were switched after the second parameter is loaded.
Has anyone herd of this and is there a way around it or a fix. If
looking at the code would...
| | |
by: IamtheEvster |
last post by:
Hi All,
I am currently using PHP 5 and MySQL 5, both on Fedora Core 5. I am
unable to call a MySQL stored procedure that returns output parameters
using mysql, mysqli, or PDO. I'm having a hell of a time with it...
The following comes from phpinfo():
PHP Version: 5.1.2
mysql Client API version: 5.0.18
mysqli Client API version: 5.0.18
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table.
When calling the stored procedure from VB.NET, in the CommandText, can I
just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)...
|
by: mch1ck3y |
last post by:
Hello there - hoping someone can offer some advice.
I am using SQL Server 2000 and want a stored procedure to execute 20 seconds after a particular field on a table is updated using a trigger on the table to be updated. Any ideas on how this would be achievable - any help would be greatly appreciated.
|
by: richkid |
last post by:
Good Day,
I'm Trying to pass parameters to represent a column name and value to a stored procedure to execute but havinfg difficulties... can anyone help?
declare @columnName as nvarchar(30)
declare @value as nvarchar(30)
declare @condition as nvarchar(10)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
