Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Web Site User vs Non web site user?

Author  Topic 

jrockfl
Posting Yak Master

223 Posts
Posted - 2005-03-15 : 19:58:32
Let's say I have a web site and "stand alone" application that interact with a database. 20 different web site users access the web site with 1 username

Dim sConnString As String = _
"Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUsername;" & _
"Pwd=MyPassword"

How does this compare to 1 user connecting to the same database but through a windows application and it's just one person.

Would the website user user of a lot more cpu and memory? Would this web site user affect the performance of the database?

Kristen
Test

22859 Posts
Posted - 2005-03-16 : 01:27:12
I reckon the web "application" will look like 20 Windows application users.

If your application is "chatty", in a SQL sense, it will perform badly. If its "client/server" it will perform well.

If you have lousy querries, no indexees, lots of cursors, dynamic SQL, etc. then it will not scale well!

We get several thousand users a day through some of our web sites. They hang around, on average, for about 15 minutes, and 95% of them arrive between 8am and midnight. My rough maths suggests thats around 30 concurrent users. It probably hits double that at times.

Each page they view uses around 30 Stored Procedures - some called by other SProcs, many called directly by the web application.

The sites use some heavy iron - quad xeon SQL box and separate quad sxeon web server, but neither goes much above 20% cpu usage for long.

The key is how efficiently you have made your database querries. And, unless you are already a well skilled DBA, you can probably improve that - post the querries here and let the helpful SQL Team folk make suggestions! - so just build enough into the budget for that process.

Kristen
Go to Top of Page

jrockfl
Posting Yak Master

223 Posts
Posted - 2005-03-16 : 07:23:18
Kristen,

Thank you for reply. I'm not familar with cursors, what do they do? What does "chatty" mean? I have a little dynamic SQL, not much at all. For example, to look up customer details. The user needs to input the customer number.

SELECT cus_no, cus_name, addr_1, addr_2, city, state, zip, contact, email_addr, phone_no, ar_terms_cd, sls_ptd, sls_ytd, sls_last_yr, last_sale_dt, last_sale_amt, last_pay_dt, last_pay_amt
FROM ARCUSFIL_SQL
WHERE cus_no = '#form.cus_no#'

Your web page is a lot more complex compared to mine. I only have one query on the first page to that looks up the customer details.

Here is my complex query :) This is on the order history page

SELECT A.cus_no, A.ord_dt, A.ord_no, A.ord_type, A.oe_po_no, A.orig_ord_type, A.inv_no, ISNULL(B.Ord_No, 0) AS Backorder
FROM OEHDRHST_SQL A, (
SELECT Ord_No
FROM OEORDHDR_SQL
) AS B
WHERE A.cus_no = '#cus_no#'
AND A.ord_no *= B.ord_no
ORDER BY #sortby# #direction#

No, I'm not a well skilled DBA, one of these days!
I use ColdFusion for the web, so #variables#
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-03-16 : 08:49:59
If you are not using cursors that's fine, They are inefficient.

But I'm afraid you do indeed have dynamic SQL. For your example SELECT statement SQL has to make a query plan. It will be specific to your query, right down to the specific customer number in your WHERE clause.

If you changed it to use a parameterised EXECUTE sp_ExecuteSQL ... syntax it would be dramatically more efficient - almost certain 10x faster, possibly even 100x.

Better still write a Stored Procedure to do each task, and "call" them instead.

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-03-16 : 08:50:39
P.S. But its a bit questionable whether any of that will be necessary, unless you expect fairly heavy demand, or your server is being shared by several applications

Kristen
Go to Top of Page

jrockfl
Posting Yak Master

223 Posts
Posted - 2005-03-16 : 09:24:12
Can you tell me more about parameterised EXECUTE sp_ExecuteSQL?

It might not be needed, but I would like to know more about it.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-03-16 : 09:26:35
first read about it in BOL. then try it. then if something doesn't work ask us
that's the best way you'll learn.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-03-16 : 09:47:02
I agree with spirit, but from where you're coming from you may find it a bit hairy!

EXEC sp_ExecuteSQL 'SELECT cus_no, cus_name, addr_1, addr_2, city, state, zip, contact, email_addr, phone_no, ar_terms_cd, sls_ptd, sls_ytd, sls_last_yr, last_sale_dt, last_sale_amt, last_pay_dt, last_pay_amt
FROM ARCUSFIL_SQL
WHERE cus_no = @cus_no',
'@cus_no varchar(10)',
@cus_no = '#form.cus_no#'

So ... the WHERE clause uses a parameter (@cus_no) instead of the actual value; you have to declare the parameter in the second bi (I've assumed that its a varchar(10), and then you have to rovide a list of all [one the one in this case] the parameters you want to use.

What happens is that SQL creates a query plan for whatever isin the first string - i.e. your original SQL with "@cus_no" in the WHERE clause. Next time you call this the first string parameter will be IDENTITCAL (although the actual parameter list will ahve different values of course), and SQL will recognise that the query plan for that string is already in its cache and reuse it. Bingo!

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-03-16 : 09:48:35
Oh bother, typing too fast. Note that in the first string parameter you need to double-up any single quotes (none in this example)

Kristen
Go to Top of Page

jrockfl
Posting Yak Master

223 Posts
Posted - 2005-03-16 : 09:54:27
Yes, I agree too. BOL is the best place to start.
I'm familiar with SPS
CREATE Procedure OI_OrderHistory
(
@cus_no char (12)

)

AS

SELECT A.cus_no, A.ord_dt, A.ord_no, A.ord_type, A.oe_po_no, A.orig_ord_type, A.inv_no, ISNULL(B.Ord_No, 0) AS Backorder
FROM OEHDRHST_SQL A, (
SELECT Ord_No
FROM OEORDHDR_SQL
) AS B
WHERE A.cus_no = @cus_no
AND A.ord_no *= B.ord_no

I just didnt know what you meant by "parameterised EXECUTE sp_ExecuteSQL"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-03-16 : 10:46:14
Ah, OK then, just use Sprocs then and away you go!

We had a client who had a middle-layer, which they had built, between their application and the database and they were able to convert the "dynamic SQL" from their application, using their "middle layer", into sp_ExecuteSQL calls, instead of just passing it through. They got a significant increase in performance and it took them all of half an hour to do! Very satisfying

Kristen
Go to Top of Page

jrockfl
Posting Yak Master

223 Posts
Posted - 2005-03-16 : 16:52:13
Views

Would it help performance if I created a view out of?

SELECT A.cus_no, A.ord_dt, A.ord_no, A.ord_type, A.oe_po_no, A.orig_ord_type, A.inv_no, ISNULL(B.Ord_No, 0) AS Backorder
FROM OEHDRHST_SQL A, (
SELECT Ord_No
FROM OEORDHDR_SQL
) AS B
WHERE A.cus_no = @cus_no
AND A.ord_no *= B.ord_no
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-03-17 : 04:19:55
If you replace

SELECT A.cus_no, A.ord_dt, A.ord_no, A.ord_type, A.oe_po_no, A.orig_ord_type, A.inv_no, ISNULL(B.Ord_No, 0) AS Backorder
FROM OEHDRHST_SQL A, (
SELECT Ord_No
FROM OEORDHDR_SQL
) AS B
WHERE A.cus_no = '#cus_no#'
AND A.ord_no *= B.ord_no
ORDER BY #sortby# #direction#

with a VIEW and call it thus:

SELECT cus_no, ord_dt, ord_no, ord_type, oe_po_no, orig_ord_type, inv_no, Backorder
FROM MyView
WHERE cus_no = '#cus_no#'
ORDER BY #sortby# #direction#

it will be just the same - the optimiser cannot cache the query (but the syntax will be easier to read, and if you need to change the JOIN or somesuch that will be centralised in the View's definition). You'll have to use a Stored procedure, or sp_ExecuteSQL, to parameterise the query to get it into the query plan cache.

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site