在庞大的网站上执行Sloq SQL（调用存储过程）(Sloq SQL execution (calling store procedures) on huge site)

首先，我意识到我的问题可能很广泛，请耐心等待，因为我一直在思考如何形成一个月，我仍然不能100％确定如何表达我的问题。

我目前正在开发一个网站，每天将被成千上万的用户使用。瓶颈是与数据库的通信。

每个与表的对话都是通过存储过程完成的，其调用如下所示：

public void storedProcedure(int id, out DataSet ds) { ds = new DataSet("resource"); SqlDataReader objReader = null; SqlCommand cmd = new SqlCommand("storedProcedure", DbConn.objConn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@id", id)); openConnection(cmd); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); cmd.Connection.Close(); }

要么

public void anotherStoredProcedure(int var1, int var2, int var3, int var4, string var5, out DataSet ds) { ds = new DataSet("ai"); SqlCommand cmd = new SqlCommand("anotherStoredProcedure", DbConn.objConn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@var1", var1)); cmd.Parameters.Add(new SqlParameter("@var2", var2)); cmd.Parameters.Add(new SqlParameter("@var3", var3)); cmd.Parameters.Add(new SqlParameter("@var4", var4)); cmd.Parameters.Add(new SqlParameter("@var5", var5)); openConnection(cmd); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); cmd.Connection.Close(); }

我的objConn定义如下：

public static string DatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["objConnLocal"].ConnectionString; public static SqlConnection objConn = new SqlConnection(DatabaseConnectionString);

在web.config当然我有

现在问题是：在每个page_load上都有几个sp调用（上面），当用户开始浏览页面时，会进行更多调用。

目前只有开发和测试团队在现场，有时速度非常慢。它经常会一直加载直到超时（错误504）。

在第一次用户登录时的另一个问题（只是偶尔，但肯定经常足以引起注意）是它会继续尝试运行一个呼叫但是连接会声称被打开，即使它不应该。一个相当不合作的解决办法是

private void openConnection(SqlCommand cmd){ if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } try { cmd.Connection.Open(); } catch (Exception ex) { System.Threading.Thread.Sleep(1000); HttpContext.Current.Response.Redirect("/"); } }

这使连接速度变慢但至少没有显示YSOD。

那么，我在SQL调用中做错了什么，以至于只有5-10个用户这么慢？到目前为止我所看到的：我已经阅读了Stack Overflow，使用“using”是相当不错的，但我不完全确定为什么以及如何在答案下进行单行注释。另一个改进的想法是使用几个连接字符串，而不仅仅是一个。

解决：

在从username / pwd到Integrated Security的连接字符串中更改等待连接已解决问题。如果有人遇到类似问题，请参阅http://www.codeproject.com/Articles/17768/ADO-NET-Connection-Pooling-at-a-Glance

First of all, I realize that my question MAY be broad, please bear with me as I've been thinking on how to form it for a month and I still am not 100% sure how to express my issues.

I'm currently developing a website, that will be used by many thousands of users daily. The bottle neck is the communication with the Data Base.

Each and every conversation with the tables is done through stored procedures, whose calls look like this:

public void storedProcedure(int id, out DataSet ds) { ds = new DataSet("resource"); SqlDataReader objReader = null; SqlCommand cmd = new SqlCommand("storedProcedure", DbConn.objConn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@id", id)); openConnection(cmd); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); cmd.Connection.Close(); }

Or

public void anotherStoredProcedure(int var1, int var2, int var3, int var4, string var5, out DataSet ds) { ds = new DataSet("ai"); SqlCommand cmd = new SqlCommand("anotherStoredProcedure", DbConn.objConn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@var1", var1)); cmd.Parameters.Add(new SqlParameter("@var2", var2)); cmd.Parameters.Add(new SqlParameter("@var3", var3)); cmd.Parameters.Add(new SqlParameter("@var4", var4)); cmd.Parameters.Add(new SqlParameter("@var5", var5)); openConnection(cmd); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); cmd.Connection.Close(); }

My objConn is defined as following:

public static string DatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["objConnLocal"].ConnectionString; public static SqlConnection objConn = new SqlConnection(DatabaseConnectionString);

And ofcourse in web.config I have

Now the issue is: On every page_load there a few sp calls (above), and when the user starts navigating through the page, more calls are made.

At the moment only the developing and testing team are on the site and at times the speed is really slow. Frequently it would keep loading till it times out (err 504).

Another problem (only ever now and then, but certainly frequent enough to be noticeable) on first user login is it would keep trying to run a call but the connection would claim to be opened, even though it shouldn't be. A fairly not-working work-around is

private void openConnection(SqlCommand cmd){ if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } try { cmd.Connection.Open(); } catch (Exception ex) { System.Threading.Thread.Sleep(1000); HttpContext.Current.Response.Redirect("/"); } }

Which makes connecting slow but at least doesn't show the YSOD.

So, what am I doing wrong on my SQL calls so that it is so slow for only 5-10 users? What I have so far: I've read on Stack Overflow that using "using" is quite nice, but am not entirely sure why and how come as it was a single line comment under an answer. Another idea for improvement was to use several connection strings and not only one.

Resolved:

Changing the wait the connection is established in the connection string from username/pwd to Integrated Security resolved the issue. IF anyone's having similar issue refer to http://www.codeproject.com/Articles/17768/ADO-NET-Connection-Pooling-at-a-Glance

最满意答案

你是对的 - 这是一个广泛的问题！

对于上下文 - 从性能的角度来看，许多“每日数千名用户”并不是很大。一个精心构建的ASP.Net应用程序通常可以在一个体面的指定开发人员笔记本电脑上支持数百个并发用户; 假设每天有10K用户，在高峰时间你可能只有几十个并发用户（当然这完全取决于应用程序域）。

首先要做的是在运行代码上使用分析器来查看性能瓶颈所在。这在VS中可用，并且有几个第三方解决方案（我喜欢RedGate和JetBrains）。

分析器会告诉你代码的位置很慢 - 如果需要花费几秒钟来渲染页面，这应该是非常明显的。

乍一看，您似乎遇到了数据库问题。因此，您还可以使用SQLServer活动监视器查看长时间运行的查询。

You're right - it's a broad question!

For context - many "thousands of users daily" isn't huge from a performance point of view. A well-built ASP.Net application can typically support hundreds of concurrent users on a decently specified developer laptop; assuming 10K users per day, you probably only have a few dozen concurrent users at peak times (of course this depends entirely on the application domain).

The first thing to do is to use a profiler on your running code to see where the performance bottleneck is. This is available in VS, and there are several 3rd party solutions (I like RedGate and JetBrains).

The profiler will tell you where your code is slow - it should be pretty obvious if it's taking seconds for pages to render.

At first glance, it looks like you have a problem with the database. So you can also use the SQLServer activity monitor to look at long-running queries.

更多推荐