Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP.NET (Non-Forum Related)
 Stored Procedures vs. Inline Code rehash
 New Topic  Topic Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Wholsea
Starting Member

21 Posts

Posted - 02 April 2007 :  23:01:18  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
I saw some talk in the .net thread, figured I'd bring it up again.

There are good debates for both styles of coding, but I felt I should express my experiences, and philosophy as far as data presentation goes...

In the environments that I have worked with the UI portion of the site is considered a "Delivery Channel" and all it should do is make a simple request for data and let the "Data Channel" do the heavy lifting of putting together the SQL commands building the data and returning it to the delivery channel.

In a smaller environment (Such as probably 90% of the forum sites out there) in-line code is robust enough to build, submit, and display the information requested, but what I have found is in an environment where there can be upwards of around 2,000 to 3,000 users firing requests against a page at the same time, its best to offload the "heavy lifting" of the data retreival to the machine that owns the data and let the UI do nothing but display the data. In the environment I work in, the "Data Channel" does everything from the sorting to the element formatting, the "Delivery Channel" does nothing but ask and display.

This makes no difference when the machine that is presenting the UI is the same as the machine that owns the database (Would I be wrong to assume that most of the folks here run their database on the same machine as their forum code?)

But for the few folks who run GIANT forums that will pull thousands of concurrent users, offloading the database to a different machine, and allowing said machine to do the churning and building of the queries, a performance increase will be shown.

(A for loop hitting a database compared to a for loop with an inline SQL is NOT a valid example of testing wether or not inline SQL is better than a stored procedure)

To do a valid test, you need to hit the same page at the same time with 1000+ users to see the performance gains with offloading the SQL over to the SQL server, not to mention in a SOX regulated environment, a change to a stored procedure will take minimal effort compared to a re-compile/code drop to your UI code (Not to mention the red tape involved in manipulating the production code)

No disrespect involved here, just expressing my experiences and philosophy about who should own the data, vs. who should display the data.

HuwR
Forum Admin

United Kingdom
20550 Posts

Posted - 03 April 2007 :  05:08:17  Show Profile  Visit HuwR's Homepage
there is no dispute as to who should own vs who should display the data, that is not actually the same argument as the difference between using inline SQL vs stored procedures.
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 03 April 2007 :  10:27:51  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
It is and it isn't There is a minute bit of overhead involved in the actual parsing of an inline SQL command vs. submitting values to a stored procedure and letting the server do the parsing for you.

It is not something you would notice on a board that gets maybe 200-300 concurrent users, but it something that is HIGHLY visible in an environment where you are throwing thousands of users at a page at the same time.

When looking at a page from a performance perspective, yes, executing an inline SQL statement against a database will return the results in the same amount of time that the stored procedure will, but when you call a stored procedure to build the SQL command, and do all of that work (as opposed to a stringcat on the UI side then submit the request) The database side is doing all of the nuts and bolts of defining what needs to be queried, query it and return the data, and the UI side should only be responsible only for the input parameters.

string.concat("select * from table where userid=",useridvar, " and trandate = '", trandatevar, "' order by titlefield")
conn.execute

is more overhead with the string concatination on the server side than


cmd.parameters.add(system.data.sqlclient.sqlparameter("@userid",varchar(10)).value=useridvar
cmd.parameters.add(system.data.sqlclient.sqlparameter("@trandate",datetime)).value = trandatevar
cmd.executenonquery()

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...

Again, the performance enhancement features that come along with it are not visible until you are HAMMERING a page, and I cannot stress the simplicity of maintenance in an environment where you don't have easy access to re-compile production code and need to do tuning on common queries...

Edited by - Wholsea on 03 April 2007 10:42:53
Go to Top of Page

pdrg
Support Moderator

United Kingdom
2897 Posts

Posted - 03 April 2007 :  12:26:55  Show Profile  Send pdrg a Yahoo! Message
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!
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 03 April 2007 :  13:35:34  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
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!



In the example noted, you don't have to dynamically build a query and execute, just reference the variables within the procedure...

But if your site arcitecture has the DB seporated from the web, it should have the CPU time available to run said queries because not every page has to hit the database for data...

You are basically shifting the processing time from the front door to the database server and letting the web server do nothing but deliver content.

If I have some time this evening, I'll try to put together a working example.

Edited by - Wholsea on 03 April 2007 13:36:12
Go to Top of Page

pdrg
Support Moderator

United Kingdom
2897 Posts

Posted - 03 April 2007 :  13:51:07  Show Profile  Send pdrg a Yahoo! Message
>>to concatinate into an ad-hoc SQL query

seems to conflict with

>>In the example noted, you don't have to dynamically build a query and execute, just reference the variables within the procedure...

which is why I raised the issue - either it's dynamic SQL (and so potentially slower written in T-SQL) or it isn't. The static SQL, I agree, is faster cached, but I also know a huge amount of work has been done around cacheing of ad-hoc queries too, so the difference may be less that you think.

>>If I have some time this evening, I'll try to put together a working example.

Brilliant - I'm always fascinated to see real-life data for these things as it varies a lot case-by-case, setup-by-setup. I wonder if the difference is still as significant as it once was back in the 6.5 days? I wonder if we're talking about a 10%+ difference, or whether it's now academic with the greatly reduced cost/transaction, and cheaper to throw resources at the problem.

P :)
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 03 April 2007 :  16:14:53  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
When I speak of ad-hoc queries, in my mind, any "rendered" SQL from the UI side would qualify...

In classic ASP (as I've done in the past) you build a query then just submit to the database for execution, this arcitecture does not port well to an environment that gets POUNDED...

strSql="Select * from table where Field1='" & field1val & "' and field2='" & field2val & "'"

Can be turned into a Stored procedure with this call in it:

select * from table where field1=@field1 and field2=@field2

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

No concatination, just the use of variables.

This method also makes it more difficult for SQL Insertion. You can write functions and whatnot to catch all of that, but that adds even more overhead processing...
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20550 Posts

Posted - 03 April 2007 :  16:30:03  Show Profile  Visit HuwR's Homepage
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
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 03 April 2007 :  16:49:11  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
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



Make a believer out of me, show me how you would do the same, and I'll try to put together a mock-up and get some test results.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20550 Posts

Posted - 03 April 2007 :  17:28:29  Show Profile  Visit HuwR's Homepage
it is called a parametised query in .net, as well as eleminating concatenation they also provide protection from sql injection.

psuedo example.

sql = "Select * from aTable where fieldname =@FieldValue"
cmd.Parameters.Add(New SQLParameter("@FieldValue", SomeVariable))
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 03 April 2007 :  17:39:21  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
Okay, if I have some time this evening, I'll put together some testing examples and see what kind of results we get back...
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 04 April 2007 :  00:13:37  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
Okay, testing results (I made sure to utilize HuwR inline code example above) ....

TestCount,ExecuteTime,TranType
255,0.17095588235294118,inline
255,0.16495098039215686,proced

Since I don't have a copy of loadrunner or other competant load generating utility I used a dynamically created page with 51 (0-50) iframe entries on the page. Each iframe page loaded an instance of my test page (In a crude attempt to get multiple pages to load at the same time). I then re-rendered the page 5 times per trantype.

The test page has a simple if..then with a starttime = timer endtime = timer wraped around the entire database execute...

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


This is the stored procedure:

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



You can see by the results that there is a slight advantage to the stored procedure, given the TINY amount of load I put on the server by myself, can potentially, with a more complex process, cause performance issues for the end-user...

I'd say 90-95% of the consumers of any discussion forum software out there (PHP included) don't have the user-base to expose any sort of performance issue with inline vs. stored procedure advantages, I was just sharing my philosophy on the issue...

Edited by - Wholsea on 04 April 2007 00:21:32
Go to Top of Page

Wholsea
Starting Member

21 Posts

Posted - 04 April 2007 :  10:04:26  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
In addition to simple stuff like this, you can also offload formatting and paging to the SQL Server as well. I have some examples that I didn't think about until this morning that I'll post up when I get home tonight that do formatting and paging on the SQL Server side and allow the UI to just display the content with minimal formatting...

Edited by - Wholsea on 04 April 2007 10:05:01
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20550 Posts

Posted - 04 April 2007 :  10:39:48  Show Profile  Visit HuwR's Homepage
quote:
paging on the SQL Server side

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

Wholsea
Starting Member

21 Posts

Posted - 04 April 2007 :  10:45:13  Show Profile  Visit Wholsea's Homepage  Send Wholsea an AOL message
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

Care to share your pseudocode?

I've done it a few different ways and am interested in seeing other folks' handle large recordsets...

Edited by - Wholsea on 04 April 2007 10:45:48
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20550 Posts

Posted - 04 April 2007 :  10:47:38  Show Profile  Visit HuwR's Homepage
I will try and dig out one of the stored procedures later, am a bit tied up at the moment and don't have my sql tools intsalled on this laptop so can't get at the server.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2019 Snitz™ Communications Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000 Version 3.4.07