quote:

---

Originally posted by pdrg

Interesting point here ...

>>This is a very crude example and you'd get better testing results with something such as a search function that would utilize many parameters and other things that would be required to concatinate into an ad-hoc SQL query. String functions can be very expensive in a busy environment, much more so than a variable assignment...

A dynamic SQL string would need to be built and executed inside the sproc in MSSQL too, and as it is less efficient at procedural code, this could in fact create an overhead - the execution plan wouldn't (indeed couldn't) be cached so there's no saving plus some extra load on the DB Server.

Agreed for regular parameterised queries, but dynamic ones I'd seriously query unless you have actual test data you can present?! I'd be keen to see it if you have - I worked as a SQL Dev for MS for a while, fwiw, so appreciate some of the intricacies!

---

quote:

---

The procedure would be essentially be called by "procname val1, val2"

No concatination, just the use of variables.

---

quote:

---

Originally posted by HuwR

quote:

---

The procedure would be essentially be called by "procname val1, val2"

No concatination, just the use of variables.

---

This can be achieved in .Net without the use of stored procedures at the SQL server

---

inline code
            starttime = Timer
            Dim myCommand As New SqlCommand("select r_date, m_name,
                         r_message from tbl_forum_reply reply, tbl_forum_members members 
                        where cast(r_date as numeric) between @fromdate and @todate and 
                        reply.r_author = members.member_id and reply.r_author = @memberid 
                        order by r_Date", myConnection)
            mycommand.CommandType = CommandType.TableDirect
            Dim fromdate As New SqlParameter("@fromdate", SqlDbType.BigInt)
            Dim todate As New SqlParameter("@todate", SqlDbType.BigInt)
            Dim memberid As New SqlParameter("@memberid", SqlDbType.BigInt)
            fromdate.Value = 20060106030126
            todate.Value = 20060906030126
            memberid.Value = 2
            myCommand.Parameters.Add(fromdate)
            myCommand.Parameters.Add(todate)
            myCommand.Parameters.Add(memberid)

            Dim mydataadapter As New SqlDataAdapter
            mydataadapter.SelectCommand = myCommand

            Dim mydataset As New DataSet

            mydataadapter.Fill(mydataset)

            forumdata.DataSource = mydataset
            forumdata.DataBind()
            endtime = Timer

Proced code
            starttime = Timer
            Dim myCommand As New SqlCommand("getdata", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure
            Dim fromdate As New SqlParameter("@fromdate", SqlDbType.BigInt)
            Dim todate As New SqlParameter("@todate", SqlDbType.BigInt)
            Dim memberid As New SqlParameter("@memberid", SqlDbType.BigInt)
            fromdate.Value = 20060106030126
            todate.Value = 20060906030126
            memberid.Value = 2
            myCommand.Parameters.Add(fromdate)
            myCommand.Parameters.Add(todate)
            myCommand.Parameters.Add(memberid)

            Dim mydataadapter As New SqlDataAdapter
            mydataadapter.SelectCommand = myCommand

            Dim mydataset As New DataSet

            mydataadapter.Fill(mydataset)

            forumdata.DataSource = mydataset
            forumdata.DataBind()
            endtime = Timer

SQL Server Stored Proc
CREATE PROCEDURE dbo.getdata 
@fromdate as numeric,
@todate as numeric,
@memberid as int
AS

select
	r_date, 
	m_name, 
	r_message 
from 
	tbl_forum_reply reply, 
	tbl_forum_members members
where 
	cast(r_Date as numeric) between @fromdate and @todate
	and reply.r_author = members.member_id
	and reply.r_author = @memberid
order by r_date
GO

quote:

---

paging on the SQL Server side

---

quote:

---

Originally posted by HuwR


We are already doing that in the .Net version where recordsets are likely to be very large and paged frequently

---