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

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.