Hi
We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:
SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = "+ MyCountryVal.To String() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteRead er();
I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Para meters command as follow:
command.Command Text =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Paramet ers.Add(paramAr ray);
for (int j=0; j<paramArray.Le ngth; j++)
{
command.Paramet ers.Add(paramAr ray[j]) ;
}
Thanks in advance.
Oren. 9 1907
It's better 'cuz it's about 100000x more secure. Performance isn't an issue
either way.
Karl
-- http://www.openmymind.net/ http://www.codebetter.com/
<or***@tici.co. ilwrote in message
news:11******** **************@ s13g2000cwa.goo glegroups.com.. .
Hi
We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:
SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = "+ MyCountryVal.To String() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteRead er();
I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Para meters command as follow:
command.Command Text =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Paramet ers.Add(paramAr ray);
for (int j=0; j<paramArray.Le ngth; j++)
{
command.Paramet ers.Add(paramAr ray[j]) ;
}
Thanks in advance.
Oren.
Err...the command.Paramet ers is better is what i mean :)
karl
-- http://www.openmymind.net/ http://www.codebetter.com/
<or***@tici.co. ilwrote in message
news:11******** **************@ s13g2000cwa.goo glegroups.com.. .
Hi
We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:
SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = "+ MyCountryVal.To String() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteRead er();
I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Para meters command as follow:
command.Command Text =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Paramet ers.Add(paramAr ray);
for (int j=0; j<paramArray.Le ngth; j++)
{
command.Paramet ers.Add(paramAr ray[j]) ;
}
Thanks in advance.
Oren.
Convert your command into a stored procedure and then pass in
parameters.
If you have optional parameters e.g Search on First name or Last name
you can change you
SQL like this.
CREATE PROCEDURE spgUser
{
@firstName nvarchar(50) = null,
@lastName nvarchar(50) = null
}
AS
SELECT
userID
FROM
userTable
WHERE
(@firstName IS NULL OR firstName = @firstName )
AND
(@lastName IS NULL OR lastName = @lastName)
This will also allow this procedure to bring back ALL users if no
params are passed.
If you don't want that simply do an IF test for both being null at the
start or put that
logic in you code.
Hope this helps.
Karl Seguin [MVP] wrote:
Err...the command.Paramet ers is better is what i mean :)
karl
-- http://www.openmymind.net/ http://www.codebetter.com/
<or***@tici.co. ilwrote in message
news:11******** **************@ s13g2000cwa.goo glegroups.com.. .
Hi
We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:
SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = "+ MyCountryVal.To String() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteRead er();
I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Para meters command as follow:
command.Command Text =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Paramet ers.Add(paramAr ray);
for (int j=0; j<paramArray.Le ngth; j++)
{
command.Paramet ers.Add(paramAr ray[j]) ;
}
Thanks in advance.
Oren.
<pa*********@co rpoflondon.gov. ukwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .
WHERE
(@firstName IS NULL OR firstName = @firstName )
AND
(@lastName IS NULL OR lastName = @lastName)
I find the following much easier to read:
WHERE
firstName = COALESCE(@first Name, firstName)
AND
lastName = COALESCE(@lastN ame, lastName)
Agreed but is there a performance hit?
When we initially went for the solution I wrote many queries like this
(@firstName = firstName OR @firstName IS NULL )
This is ALOT slower than
( @firstName IS NULL OR .....
When @firstName is indeed NULL.
Mark Rae wrote:
<pa*********@co rpoflondon.gov. ukwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .
WHERE
(@firstName IS NULL OR firstName = @firstName )
AND
(@lastName IS NULL OR lastName = @lastName)
I find the following much easier to read:
WHERE
firstName = COALESCE(@first Name, firstName)
AND
lastName = COALESCE(@lastN ame, lastName)
"Paul" <pa*********@co rpoflondon.gov. ukwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...
Agreed but is there a performance hit?
I don't know - is there...?
When we initially went for the solution I wrote many queries like this
(@firstName = firstName OR @firstName IS NULL )
This is ALOT slower than
( @firstName IS NULL OR .....
When @firstName is indeed NULL.
OK - I'll have to take your word for that, as I've never benchmarked it.
Thanks for the tip.
Yes, there is a performance tip...and putting it in a sproc isn't
necessarily the best answer.
While I'm a big fan of sprocs (really big), they aren't any more secure, and
most developers don't realize that in many cases, they can run considerably
slower than inline SQL.
I disagree wth the blanket statement of putting it in an sproc - though I do
agree that it should be considered.
Karl
-- http://www.openmymind.net/ http://www.fuelindustries.com/
"Mark Rae" <ma**@markNOSPA Mrae.comwrote in message
news:ue******** ******@TK2MSFTN GP04.phx.gbl...
"Paul" <pa*********@co rpoflondon.gov. ukwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...
>Agreed but is there a performance hit?
I don't know - is there...?
>When we initially went for the solution I wrote many queries like this
(@firstName = firstName OR @firstName IS NULL )
This is ALOT slower than
( @firstName IS NULL OR .....
When @firstName is indeed NULL.
OK - I'll have to take your word for that, as I've never benchmarked it.
Thanks for the tip.
*tip* --*hit*
-- http://www.openmymind.net/ http://www.fuelindustries.com/
"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
netwrote in message news:e7******** ******@TK2MSFTN GP04.phx.gbl...
Yes, there is a performance tip...and putting it in a sproc isn't
necessarily the best answer.
While I'm a big fan of sprocs (really big), they aren't any more secure,
and most developers don't realize that in many cases, they can run
considerably slower than inline SQL.
I disagree wth the blanket statement of putting it in an sproc - though I
do agree that it should be considered.
Karl
-- http://www.openmymind.net/ http://www.fuelindustries.com/
"Mark Rae" <ma**@markNOSPA Mrae.comwrote in message
news:ue******** ******@TK2MSFTN GP04.phx.gbl...
>"Paul" <pa*********@co rpoflondon.gov. ukwrote in message news:11******* *************** @i3g2000cwc.goo glegroups.com.. .
>>Agreed but is there a performance hit?
I don't know - is there...?
>>When we initially went for the solution I wrote many queries like this
(@firstName = firstName OR @firstName IS NULL )
This is ALOT slower than
( @firstName IS NULL OR .....
When @firstName is indeed NULL.
OK - I'll have to take your word for that, as I've never benchmarked it.
Thanks for the tip.
I agree with you. However, some people might point out that an
advantage of stored procedures is that they can be modified without a
code recompile. That may or may not be a concern. I haven't found
that to be a high priority and like the inline parameter approach.
JT
Karl Seguin [MVP] wrote:
Yes, there is a performance tip...and putting it in a sproc isn't
necessarily the best answer.
While I'm a big fan of sprocs (really big), they aren't any more secure, and
most developers don't realize that in many cases, they can run considerably
slower than inline SQL.
I disagree wth the blanket statement of putting it in an sproc - though I do
agree that it should be considered.
Karl
-- http://www.openmymind.net/ http://www.fuelindustries.com/
"Mark Rae" <ma**@markNOSPA Mrae.comwrote in message
news:ue******** ******@TK2MSFTN GP04.phx.gbl...
"Paul" <pa*********@co rpoflondon.gov. ukwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...
Agreed but is there a performance hit?
I don't know - is there...?
When we initially went for the solution I wrote many queries like this
(@firstName = firstName OR @firstName IS NULL )
This is ALOT slower than
( @firstName IS NULL OR .....
When @firstName is indeed NULL.
OK - I'll have to take your word for that, as I've never benchmarked it.
Thanks for the tip.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Hursh |
last post by:
Hi,
I have written some stored procedures in SQL and these procedures
return some value.
I want these values to be captured by the ASP code.
I am able to access the tables using ADO( recordsets ) but is there a
way to pass data returned from stored procedures to vairables in ASP
code.
|
by: David Altemir |
last post by:
I have a table in MS Access 2003 that contains records that I would
like to copy to the end of the table. There is one slight deviation
from just doing a straightforwared COPY, however, in that I want to
append the new records using different value of column 1.
Here's an example of what I'm talking about:
Values in in Table1 before "copy" operation:
Bill, 3200 Palm Blvd
|
by: Bob |
last post by:
I'm new to Access projects and SQL server and am not a veteran VB
programmer. There's a cry for help! I'm attempting to print the
current form on screen by using a command button which the user clicks
once they have selected the desired record. The button calls a report
which uses a stored procedure as its record source. The SP has 2 input
parameters, one of which is a datetime data type. In the input
parameters data field of the...
|
by: rettigcd |
last post by:
Hello,
I need to create a custome form that operates similar to the MsgBox()
and InputBox() functions. I can't figure out how to pass data to a
MODAL dialog box (form).
I've tried 3 approaches:
Try #1:
|
by: Mike Dinnis |
last post by:
Hi,
I've been working through a number of turorials to try to learn more about
retrieving data from a SQL database. I think i've mastered techniques where
i create a sql string in the page and pass it to the Db and retrieveing data
from a stored procedure, but I can't get the hang of parameters.
I have a method where I can get the parameters passed to the sp but it
doesn't want to return any results. Here's a copy of my code:
| |
by: Perre Van Wilrijk |
last post by:
Hi there,
When I started using VB6, I used to write classes with properties and
functions as following ...
Private lngf1 As Long
Private strf2 As String
Public Property Get f1() As Long
f1 = lngf1
|
by: vishal |
last post by:
I am new to sql and require some help on cursors?
what are they and how and why are they used for???
it will be kind enough if anyone helps me in this regards..
regards
vishal jain.
|
by: Xah Lee |
last post by:
in March, i posted a essay “What is Expressiveness in a Computer
Language”, archived at:
http://xahlee.org/perl-python/what_is_expresiveness.html
I was informed then that there is a academic paper written on this
subject.
On the Expressive Power of Programming Languages, by Matthias
Felleisen, 1990.
http://www.ccs.neu.edu/home/cobbe/pl-seminar-jr/notes/2003-sep-26/expressive-slides.pdf
|
by: SLIMSHIM |
last post by:
Hi,
I"m new to c# and .net. I wrote a small program to add rows to an access
table.
the program goes thru the motions but the data never gets there.
here is my code. I am intentionaly not using the form controls. I"m trying
to do it solely thru code. You can safely assume I have an access datsabase
with one table called who with three columns(fields).
where did i go wrong ?
OleDbConnection con;
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 projectplanning, coding, testing, and deploymentwithout 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: 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...
| |