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