Partager via

Use Stored Procedures when you can for efficiency

  • Article

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