Use Stored Procedures when you can for efficiency


In this post (Install Northwind for SQL Express and use Visual Studio and DLINQ to query it ) I posted a fairly complex SQL command. Of course, it’s important to keep in mind that a more efficient way of executing this query may be by keeping all the data on the server (which might be remote) and just transmitting the results.


 


Two ways to do this are with a direct SQL command and by creating a (temporary) Stored Procedure on the server.


 


(Another way is via a Stored Proc in a Fox native database)


 


Below are Fox (will run in versions since 1993) and VB (will run in versions since 2003) versions of code that execute the same query using each of these techniques.


Note that in both languages, the SQL is in quotes, so the language has no intrinsic knowledge of the SQL and thus there’s no intellisense.


Also, note that the Order Details table has an embedded space in the name, so it requires special handling.


 


In both cases, I just added cosmetics like quotes and line continuations to the original Fox query code:


 


Original Fox query code:


 


SELECT ;


   C.customerID, ;


   C.companyname, ;


   SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ,;  


   (SUM(D.quantity*D.unitprice*(1-discount)) / ;


     (SELECT SUM((quantity*unitprice)*(1-discount)) ;


        FROM OrderDetails D2) ;


    )*100 AS PctTotal ;


  FROM Customers C ;


 INNER JOIN Orders O ;


    ON C.customerID = O.customerID ;


 INNER JOIN OrderDetails D ;


    ON O.orderid = D.orderid ;


 GROUP BY C.customerID, C.companyname  


 ORDER BY pctTotal DESC


 


 


 


Fox code using Stored Proc or remote SQL:


 


      CLOSE DATABASES all


      CREATE DATABASE test


      CREATE CONNECTION nwind CONNSTRING “DRIVER=SQL Server;SERVER=.\sqlexpress;DATABASE=northwind;Trusted_Connection=Yes”


      nh=SQLCONNECT(“nwind”)


      cSQl=;


“SELECT “+;


   C.customerID, “+;


   C.companyname, “+;


   SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ,”+;  


   (SUM(D.quantity*D.unitprice*(1-discount)) / “+;


     (SELECT SUM((quantity*unitprice)*(1-discount)) “+;


[        FROM “Order Details” D2) ]+;


    )*100 AS PctTotal “+;


  FROM Customers C “+;


” INNER JOIN Orders O “+;


    ON C.customerID = O.customerID “+;


[ INNER JOIN “Order Details” D ]+;


    ON O.orderid = D.orderid “+;


” GROUP BY C.customerID, C.companyname   “+;


” ORDER BY pctTotal DESC “


 


      IF .t.      && Use Stored Proc


            SQLEXEC(nh,”create procedure #temp as “+cSql)


            SQLEXEC(nh,”exec #temp”)


      ELSE


            SQLEXEC(nh,cSQL)  && Exec SQL directly


      ENDIF


     


      BROWSE LAST NOWAIT


      SQLDISCONNECT(0)


 


End of Fox code


 


 


VB Code using Stored Proc or remote SQL:


 


 


Imports System.Data.SqlClient


Public Class Form1


 


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


        Me.WindowState = FormWindowState.Maximized


        Dim sqlconn As New SqlConnection(“Database=Northwind;Data Source=.\sqlexpress;Trusted_Connection=Yes”)


        Dim sqlad As New SqlDataAdapter()


        Dim sqlcmd = sqlconn.CreateCommand()


        Dim cSQL = _


“SELECT “ + _


   C.customerID, “ + _


   C.companyname, “ + _


   SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ,” + _


   (SUM(D.quantity*D.unitprice*(1-discount)) / “ + _


     (SELECT SUM((quantity*unitprice)*(1-discount)) “ + _


        FROM “”Order Details”” D2) “ + _


    )*100 AS PctTotal “ + _


  FROM Customers C “ + _


” INNER JOIN Orders O “ + _


    ON C.customerID = O.customerID “ + _


” INNER JOIN “”Order Details”” D “ + _


    ON O.orderid = D.orderid “ + _


” GROUP BY C.customerID, C.companyname  + _


” ORDER BY pctTotal DESC “


        If 1 Then


            sqlcmd.CommandText = “create procedure #temp as “ + cSQL


            sqlcmd.Connection = sqlconn


            sqlconn.Open()


            sqlcmd.ExecuteScalar()  ‘ exec to create the SP


            sqlad.SelectCommand = New SqlCommand(“exec #temp”, sqlconn) ‘cmd to exec the sp


        Else


            sqlcmd.CommandText = cSQL


            sqlad.SelectCommand = sqlcmd


        End If


        Dim dt As New DataTable


        sqlad.Fill(dt)


 


        Dim dv As New DataView(dt)


        Dim dg As New DataGrid


        dg.DataSource = dv


        dg.Width = Me.Width


        dg.Height = Me.Height


        dg.Visible = 1


        Me.Controls.Add(dg)


    End Sub


End Class


 


 


End of VB code


 

Comments (2)

  1. Tod McKenna says:

    One thing to consider is the ability to use SQL Server’s (or any database’s) native syntax and proprietary features that do not exist in foxpro when making the decision to implement this. This is one great advantage to using stored procs and SQLEXEC on the database itself. Best thing is that VFP makes this simple.

    Examples:

    "SQL implementations, database servers, and VFP" at http://blog.todmeansfox.com/2007/04/17/sql-implementations-database-servers-and-vfp/

    and

    "VFP INTERSECTing SQL" at http://blog.todmeansfox.com/2007/04/04/vfp-intersecting-sql/

  2. Cyrus says:

    In fox pro i like the TEXT … ENDTEXT Command.

    It is a lot cleaner when passing queries to a database.