Sql Query String verses SQL Stored Procedure

I'm working on the performace of my pages.  I want to allow large datasets and get the best performance I can.  I've read that Stored Proceedures(SP) are faster than passing a Query String(QS).  The questions I have are:

Given the code of this page, will I see an improvement in performance using a SP?
If yes, how do I allow for just SOME of the variables being passed? Users are nor required to enter all parameters. As far as I know, if you create a SP in SQL Server and don't pass ALL of the parameters it expects, you'll get an error.
Also, someone mentioned that string concatination is faster using the System.Text string builder. Will using it make a difference with the strings I'm concantinating?
Thanks for you help.
Cory Bonallo
Developer

<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim strSearch As String, curPageIndex As Integer, strPaging As String

sub ClickHandler(sender as Object, e as ImageClickEventArgs)

dlCustomer.AllowPaging = False
dlCustomer.BorderWidth = Unit.Pixel(0)
Dim Printing As HttpCookie = New HttpCookie("Printing", "true")
Request.Cookies.Set(Printing)
BindData(strSearch)
Response.ClearHeaders()
Response.Buffer = true
Response.ContentType="application/vnd.ms-excel"
Response.Charset = ""
Response.AppendHeader("Content-Disposition","attachment; Filename = " & chr(34) & "MyFileName.xls" & chr(34) )

end sub
Sub PrintHandler(sender as Object, e as ImageClickEventArgs)

dlCustomer.AllowPaging = False
Dim Printing As HttpCookie = New HttpCookie("Printing", "true")
Request.Cookies.Set(Printing)
binddata(strsearch)

End Sub

Private objDataSet as New DataSet()

Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim esp As String
Dim itemno As String
Dim cust As String
Dim vend As String
Dim buyer As String
Dim tm As String
Dim multisku As String
Dim noZero As String
Dim filterDate as String
Dim strCritera as String

esp = Request.QueryString("esp")
itemno = Request.QueryString("item")
cust = Request.QueryString("cust")
vend = Request.QueryString("vend")
buyer = Request.QueryString("buyer")
tm = Request.QueryString("tm")
multisku = Request.QueryString("multisku")
If not IsNothing(multisku) then
multisku = multisku.Replace(vbCrLf, "','")
end if
noZero = Request.QueryString("noZero")
filterdate = Request.Cookies("filterDate").Value
if esp <> "" then
strCritera += "Promotion: " & esp & " "
end if
if itemno <> "" then
strCritera += "JDS Sku: " & itemno & " "
end if
if multisku <> "" then
strCritera += "Multiple Items: " & multisku & " "
end if
if cust <> "" then
strCritera += "Customer: " & cust & " "
end if
if vend <> "" then
strCritera += "Vendor: " & vend & " "
end if
if buyer <> "" then
strCritera += "Buyer: " & buyer & " "
end if
if tm <> "" then
strCritera += "TM #: " & tm & " "
end if
lblcriteria.text = "Search Criteria - " & strCritera

strSearch = " where "

'build where clause
if noZero = "on" then
strSearch += "vw_SearchCust.Promotion <> 0 and DATEPART(year, vw_SearchCust.[Expiration Date]) = " & filterDate
else
strSearch += "DATEPART(year, vw_SearchCust.[Expiration Date]) = " & filterDate
end if
if len(esp) > 0 then
if len(strSearch) > 8 then
strSearch += " and vw_SearchCust.Promotion = " & esp
else
strSearch += "vw_SearchCust.Promotion = " & esp
end if
end if
if len(itemno) > 0 then
if len(strSearch) > 8 then
strSearch = strSearch & " and vw_SearchCust.[Item No.] = '" & itemno.Insert(4,"-") & "'"
else
strSearch += "vw_SearchCust.[Item No.] = '" & itemno.Insert(4,"-") & "'"
end if
end if
if len(multisku) > 0 then ' variable not empty, add to query string
if len(strSearch) > 8 then 'check for previous criteria, add AND operator plus new criteria
strSearch = strSearch & " and vw_SearchCust.[Item No.] IN " & "('" & multisku & "')"
else ' add new criteria
strSearch += "vw_SearchCust.[Item No.] IN " & "('" & multisku & "')"
end if
end if
if len(cust) > 0 then
if len(strSearch) > 8 then
strSearch = strSearch & " and vw_SearchCust.strCustNum = " & cust
else
strSearch += "vw_SearchCust.strCustNum = " & cust
end if
end if
if len(vend) > 0 then
if len(strSearch) > 8 then
strSearch = strSearch & " and vw_SearchCust.Vendor = " & vend
else
strSearch += "vw_SearchCust.Vendor = " & vend
end if
end if
if len(buyer) > 0 then
if len(strSearch) > 8 then
strSearch = strSearch & " and vw_SearchCust.[Buyer No.] = " & buyer
else
strSearch += "vw_SearchCust.[Buyer No.] = " & buyer
end if
end if
if len(tm) > 0 then
if len(strSearch) > 8 then
strSearch = strSearch & " and vw_SearchCust.TM = " & tm
else
strSearch += "vw_SearchCust.TM = " & tm
end if
end if
if strSearch = " where " then
strSearch = ""
end if

btnPrint.Attributes.Add("onMouseOver", "this.src='images/bttn_print_over.jpg';")
btnPrint.Attributes.Add("onMouseOut", "this.src='images/bttn_print.jpg';")
btnPrint.Attributes.Add("onMouseDown", "this.src='images/bttn_print_down.jpg';")
btnExport.Attributes.Add("onMouseOver", "this.src='images/bttn_export_over.jpg';")
btnExport.Attributes.Add("onMouseOut", "this.src='images/bttn_export.jpg';")
btnExport.Attributes.Add("onMouseDown", "this.src='images/bttn_export_down.jpg';")
btnClose.Attributes.Add("onMouseOver", "this.src='images/bttn_Close_over.jpg';")
btnClose.Attributes.Add("onMouseOut", "this.src='images/bttn_Close.jpg';")
btnClose.Attributes.Add("onMouseDown", "this.src='images/bttn_Close_down.jpg';")
btnClose.Attributes.Add("onClick", "window.close();")

If Not Page.IsPostBack Then
strPaging = "Yes"
binddata(strSearch)

End If

End Sub

sub binddata(strSearch as string)

Dim strConnection as String = "user id=cbonallo;password=kmfdm123;"
strConnection += "database=Promodata;server=jdsnt5;"
strConnection += "Connect Timeout=30"
dim strSQL as string = "Select * from vw_SearchCust" & strSearch & ";"
dim strTMSQL as string = "Select Distinct tblCustomer.strCustNum, tblCustomer.strCustName, tblCustomer.strCity, tblCustomer.strState from tblCustomer inner join vw_SearchCust on tblCustomer.strCustNum = vw_SearchCust.strCustNum" & strSearch & ";"

dim strBoth as string = strTMSQL & strSQL

Dim objConnection as New SqlConnection(strConnection)

dim objDataAdapter as New SQLDataAdapter(strBoth,objConnection)
objDataAdapter.Fill(objDataSet)
objDataSet.Tables(0).TableName = "tblTM"
objDataSet.Tables(1).TableName = "Promotion"
if objDataSet.Tables("tblTM").Rows.Count > 8 AND strPaging = "Yes"
If left(request.userhostaddress, 12) <> "199.201.162." OR Request.Cookies("Paging").Value = "false"
With dlCustomer
' Enable paging.
.AllowPaging = True
' Display 5 page numbers at a time.
.PagerStyle.Mode = PagerMode.NumericPages
.PagerStyle.PageButtonCount = 50
.PageSize = 4
End With
End If
End If
dlCustomer.DataSource = objDataSet
dlCustomer.DataBind()
objDataAdapter.Dispose
objConnection.Close
objConnection.Dispose
end Sub

Protected Function getDetailsDataSource(ByVal strCustNum As String) As DataView

Dim orderDet As DataView = objDataSet.Tables(1).DefaultView

orderDet.RowFilter = "strCustNum = '" & strCustNum & "'"
orderDet.Sort = "Promotion ASC"
Return orderDet

End Function

Dim intQtyShipSum as Double = 0
Dim intQtyOrdSum as Double = 0
Sub ComputeSum(sender As Object, e As DataGridItemEventArgs)

'First, make sure we are dealing with an Item or AlternatingItem
If e.Item.ItemType = ListItemType.Item OR _
e.Item.ItemType = ListItemType.AlternatingItem then

Dim intQtyShip As Double = Databinder.Eval(e.Item.DataItem, "$ Shipped")
Dim intQtyOrd As Double = Databinder.Eval(e.Item.DataItem, "$ Ordered")
intQtyShipSum += intQtyShip
intQtyOrdSum += intQtyOrd

ElseIf e.Item.ItemType = ListItemType.Footer then

e.Item.Cells(4).Text = "Total: " & String.Format("{0:c}", intQtyShipSum)
e.Item.Cells(3).Text = "Total: " & String.Format("{0:c}", intQtyOrdSum)
intQtyShipSum = 0
intQtyOrdSum = 0
End If

End Sub


Private Sub dlCustomer_PageIndexChanged(ByVal source As Object, _
ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)
curPageIndex = e.NewPageIndex
dlCustomer.CurrentPageIndex = e.NewPageIndex
BindData(strSearch)

End Sub


Sub dlCustomer_ItemCreated(sender As Object, e As DataGridItemEventArgs)
Dim strPrint as String
If e.Item.ItemType = ListItemType.Item OR _
e.Item.ItemType = ListItemType.AlternatingItem then
'Check to see if we are printing
strPrint = Request.Cookies("Printing").Value
If strPrint = "true" then
'Get the grid. The Grid can be found in the 0th Cell (since it
'is in the first DataGrid column), and use findcontrol to return a reference to the grid.
Dim myGrid as DataGrid = CType(e.Item.Cells(0).FindControl("grdDetails"), Datagrid)
myGrid.CssClass = "fred"
'response.write("fred")
Else
Dim myGrid as DataGrid = CType(e.Item.Cells(0).FindControl("grdDetails"), Datagrid)
myGrid.CssClass = "tblMain"
' response.write("tblMain")
End If
End If
End Sub
</script>
<html>
<head>
<title>Promotion Search Results By Store</title>
<link title="setScreen" media="screen" href="screen.css" type="text/css" rel="StyleSheet" />
<script language="javascript">
</script>
</head>
<body>
<form name="results" runat="server">
<p align="center">
<asp:imagebutton id="btnPrint" onclick="PrintHandler" runat="server" visible="true" name="btnSubmit" ImageUrl="images/bttn_print.jpg" ImageAlign="top"></asp:imagebutton>
&nbsp;&nbsp;&nbsp;&nbsp;
<asp:imagebutton id="btnExport" onclick="ClickHandler" runat="server" ImageUrl="images/bttn_export.jpg" ImageAlign="top"></asp:imagebutton>
&nbsp;&nbsp;&nbsp;&nbsp;
<asp:imagebutton id="btnClose" runat="server" ImageUrl="images/bttn_close.jpg" ImageAlign="top"></asp:imagebutton>
<br />
<asp:Label id="lblcriteria" runat="server" font-size="8pt" font-name="arial" forecolor="#000099"></asp:Label>
<br />
<asp:DataGrid id="dlCustomer" runat="server" AutoGenerateColumns="False" OnItemCreated="dlCustomer_ItemCreated" OnPageIndexChanged="dlCustomer_PageIndexChanged" ItemStyle-ForeColor="Blue" ItemStyle-Wrap="false" HeaderStyle-ForeColor="blue" HeaderStyle-BackColor="#dcdcdc" CellPadding="3" Font-Size="8pt" Font-Name="arial">
<Columns>
<asp:TemplateColumn ItemStyle-VerticalAlign="Top" ItemStyle-Font-Size="9pt" ItemStyle-BackColor="#D8CFCF" HeaderText="Results Grouped By Customer">
<ItemTemplate>
Customer Purchases: <%# DataBinder.Eval(Container.DataItem, "strCustNum") %>&nbsp;<%# DataBinder.Eval(Container.DataItem, "strCustName") %> &nbsp;<%# DataBinder.Eval(Container.DataItem, "strCity") %>&#44;&nbsp;<%# DataBinder.Eval(Container.DataItem, "strState") %>
<hr />
<asp:datagrid runat="server" name="grdDetails" id="grdDetails" headerCSS="tblHeader" bodyCSS="tblBody" bodyHeight="105" DataSource='<%# getDetailsDataSource(DataBinder.Eval(Container.DataItem, "strCustNum".ToString()).ToString()) %>' CellPadding="3" Font-Name="arial" Font-Size="8pt" HeaderStyle-BackColor="#dcdcdc" HeaderStyle-ForeColor="blue" ItemStyle-Wrap="false" ItemStyle-BackColor="white" Width="100%" AutoGenerateColumns="false" ShowFooter="true" BorderWidth="0" OnItemDataBound="ComputeSum">
<Columns>
<asp:BoundColumn DataField="Item No." HeaderText="Item#" />
<asp:BoundColumn DataField="Jensen Item Description" HeaderText="& Description" />
<asp:BoundColumn DataField="Description" HeaderText="Description" />
<asp:BoundColumn DataField="$ Ordered" DataFormatString="{0:c}" HeaderText="Ordered" />
<asp:BoundColumn DataField="$ Shipped" DataFormatString="{0:c}" HeaderText="Shipped" />
</Columns>
</asp:datagrid>
</itemtemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</p>
</form>
</body>
</html>

Cory Bonallo
Retail Services
Developer
0
cory
7/20/2004 12:32:00 AM
asp.net.web-forms 93655 articles. 6 followers. Follow

1 Replies
979 Views

Similar Articles

[PageSpeed] 32

Yes, stored procedures are faster as SQL Server compiles an execution plan when you save them. However, SQL Server also caches an execution plan for parametrised queries, so that's worth considering too. How much extra performance you get out of a stored procedure versus a parametrised query is actually the subject of a bit of debate.

What you certainly must not do is what you've done here, and stick the values themselves directly into your query string. Not only is this bad for performance (SQL Server has to re-create the execution plan each time since it caches based on the query string) but it also poses a security risk -- do a Google search on "SQL injection attacks" to find out the gory details.
A StringBuilder will also give you increased performance here so yes, use that too.
0
jammycakes
7/20/2004 2:56:50 PM
Reply:

Similar Artilces:

SQL Query , SQL or Procedural
HIHi i need some help on what the SQL query would be, let's say i have a resultant table like this. Group TestID Result A AA 10 A BB 30 A CC 20 B AB 200 B AC 300 A DD 50 C AA 1000 and i need to query by group and re-name every field as its TestID value for that selected group:  Example:if group "A" is selected: AA BB CC DD 10 30 20 50 if group "B" is selected: AB BC 200 300 and so on.. thanks.   Here is a sample for you to play with PIVOT: SELECT [...

ADO.NET 2.0 - Three-tier application - connection string error
Using ASP.NET 2.0 beta, VS2005 beta 1, W2k3 server with IIS I try to build a three-tier app structure as followed: - the store procedure in SQL with a input parameter as Username and retrieve the UserId as output parameter. - the business logic (class) with connection string defined in web.config (“AppConnectionStringASPNETapp”) - the presentation aspx file after the user has logged in through login.aspx I have work out 2 scenarios with 2 different errors. Scenario 1: Here is the code is business logic (class) (catalog.cs) using System; using System.Data; usi...

Stored Procedure executing durations are different between executing from application(web) and SQl server management studio
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack  through sql management studio you get better perfomance because it directly access to db engine an...

Copy stored procedures from SQL to SQL DE?
Is there an easy way to copy all stored procedures from an SQL server to en SQL Desktop Engine or do I have build all stored procedures by hand?Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful reply/replies as "Answer". You can use the SQL Server import/export wizard, or you can generate a script of them using SQL Server Management Studio. Thanx, In the mean time I copied them manually (copy-paste). So I think a seperate script would be usefull when you have 100+ stored procedures. Or have the script available.Johan Theu...

Linq to SQL and stored procedure with dynamic sql
I have a stored procedure like create proc test as ..... set @sql = 'select ' + .... + ' from ....' exec(@sql)The select list is dynamically (in fact, pivot table). However, after drag the stored procedure to dbml design view and called, the stored procedure only return an integer.How to get the result set of the stored procedure when using dbml?Thanks,  Is this the only SELECT statement that @sql gets?  I created a simple example and it performed as I would expect returning a complete recordset....I'm wondering if your stored proc is returning 2 record se...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

SQL string into Stored Procedure
Not sure this is the right forum as I'm not sure quite what the problem is, but I have a feeeling it's the stored procedure that I'm using to replace the SQL string I used previously.I have a search form used to find records in a (SQL Server 2005) db. The form has a number of textboxes and corresponding checkboxes. The user types in a value they want to search for (e.g. search for a surname)  and then selects the corresponding checkbox to indicate this. Or they can search for both surname and firstname by typing in the values in the correct textboxes and selecting the checkboxes corress...

LINQ in a SQL CLR Stored Procedure (SQL 2008)
Hey all, i'd like to use LINQ in a CLR Stored Procedure in SQL 2008. The problem is, i can't add a reference to System.Xml.Linq. How do i make that happen? my SQL project is targeting .net 3.5, and i have SP1 installed.I saw a blog about this somewhere recently but now i can't find it. so if you can point me to something, that would be awesome.thanks,-b I googled and found this blog http://oakleafblog.blogspot.com/2007/09/problems-using-stored-procedures-for_08.html  Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth) That does...

SQL Stored Procedure Issue
This is the Stored Procedure below ->  SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object:  Stored Proc...

SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...

cancelling query initiated from web form, WAS 'Cancelling HUGE sql queries'
RE: http://archive.develooper.com/dbi-users@perl.org/msg08566.html http://archive.develooper.com/dbi-users@perl.org/msg08567.html It was suggested to capture the PID in order to kill any abandoned queries. This appears to work if the web server, web app, and database are all on the same machine. However, it doesn't seem so if the web server and database are on different machines. What we've noticed is that when we kill the web app PID, the database query process still remains active. Yet, when we directly kill the database query PID, the web app PID is automagically k...

Stored Procedures
I am trying to create a StoredProcedure (below) that requires more than one SQL statement to be executed. I am getting a batch error as follows: Result set not permitted in '<batch statement>'. I am using ASA9 - version 9.0.1751. The following is the stored procedure that I am trying to execute. ALTER PROCEDURE "DBA"."asi_SDSelectUserDetailsRange" ( @lrange varchar(5), @hrange varchar(5), @Filter varchar(20), @ColumnName varchar(20) ) BEGIN DECLARE @SQL1 varchar(8000); DECLARE @SQL2 varchar(8000); DECLARE @SQL3 varchar(8000); D...

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

Web resources about - Sql Query String verses SQL Stored Procedure - asp.net.web-forms

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

Metal Gear Online PS4 release trolled by ancient PSN update procedures
One of the chief problems I have with the PS4 is the way the storefront (and subsequently, the release of most games) works. Not only has the ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Resources last updated: 11/22/2015 8:47:20 AM