January 31, 2003 at 8:10 am
I programmed a VB6-Class which calculates several stock-values... using ADO Command parameters for input and output. This commands (SELECTs) are based on SQL-Views and a maximum index-tuning was already done. The number of input-parameters is variable. See this example :
Private Function GetPrBdgStkRsv(dDate As Date, Optional ieCalcOn As eCalcOn = ctPrice) As Double
With cmTemp
.CommandText = _
"SELECT ? = SUM(ROUND(Mg," & iDecMg & " & _
" FROM vStkPrBdgRsv" & _
" WHERE " & BuildWhereStringPrBdg(ieCalcOn)
.Execute , , adExecuteNoRecords
If Not EmptyValueStk(.Parameters(0).Value) Then
GetPrBdgStkRsv = .Parameters(0).Value
Else
GetPrBdgStkRsv = 0
End If
End With
End Function
Would it make sense to rewrite the code in a stored procedure ? Would I get some speed- improvements ?
Thanks,
Patrick Simons, MCP
Patrick SIMONS, MCP
January 31, 2003 at 8:48 am
quote:
Would it make sense to rewrite the code in a stored procedure ? Would I get some speed- improvements ?
Yes. Stored Procs will increase performance in at least the following ways: a) decreased network traffic due to only sending the name of procedure vs. sql statements, b) compilation of query plan and reuse of that plan. For more info, there are a number of articles on this site and http://www.sql-server-performance.com on ADO / SQL Server Best practices and performance tuning...Joe Chang, in particular on the latter site, has some gfreat articles comparing different techniques.
January 31, 2003 at 9:17 am
Nit-picking a bit, but ad hoc queries can have cached execution plans that are re-used (see Chapter 15 of Inside SQL Server 2000). With that said, you eliminate a lot of the uncertainty with stored procedures. Use stored procedures whenever you can.
Another reason to do so is it means you can more tightly control data access. If you use ownership chains, you can use stored procedures to control how the data is accessed. Users have execute rights on the stored procedures with no rights against the base tables. This is so sweet.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 3, 2003 at 12:54 am
But because I've to use a variable number of input-parameters (WHERE clause), I must use dynamic-SQL inside the stored-procedure ! So SQL-Server could not prepare the statement because it changes from one call to the other. Am I right ? Would the stored proc make nevertheless sense ?
Patrick SIMONS, MCP
Edited by - PatrickSimons on 02/03/2003 04:48:23 AM
Patrick SIMONS, MCP
February 3, 2003 at 3:34 am
Maybe, maybe not. If you have an unlimited number of where options or extremely high number anyway it may be better to use dynamic SQL. You still have some control but you have to be carefull for the potential of an injection attack. Which I believ is still possible the way you are doing with CommantText anyway.
However if you have a few and they don't al occurr in combination or just a few combinations keep in mind 1 SP can can others. So you can control with non-dynamic SQL.
Ex. (Note: I prefer SP groups so ;n is te number of the item in the SP group. ;1 is always default when calling SP. There is no requirement to group.)
CREATE PROC ip_dothis;1
@colname varchar(100),
@val varchar(100)
AS
SET NOCOUNT ON
if @colname = 'col1'
begin
EXEC ip_dothis;2 @val
end
else
begin
EXEC ip_dothis;3 @val
end
GO
CREATE PROC ip_dothis;2
@val VARCHAR(100)
AS
SET NOCOUNT ON
SELECT * FROM tbl_X WHERE col1 = @val
GO
CREATE PROC ip_dothis;3
@val VARCHAR(100)
AS
SET NOCOUNT ON
SELECT * FROM tbl_X WHERE col2 = @val
GO
SO in the above I can choose 1 of 2 columns and a query with save execution plan will be there.
Ex.
EXEC ip_dothis 'col1', 'this'
will run
select * from tbl_X WHERE col1 = 'this'
so you do have options, it is a matter of fesibility based on the number of options you need.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply