SQL Server性能和查询执行 [英] SQL Server Performance and Query Execution
问题描述
很少有人前一天是很难..
Few day before it was hard time..,
我已经制定了网上录取过程中的应用为大学生，是相当成功的。
I have developed an Application for Online Admission process for College students and was quite successful.
让我来我所面临的问题，
Let me come to the problem i faced,
- 2桌都参与了这个问题： Student_AdmissionDetails （几乎包含30-35场，其中大部分是具有为nvarchar数据类型（70）），另一次是的 StudentCategory
- 从入院时，进程启动，该Student_AdmissionDetails是有记录的，并具有约StudentCategory几3,00,000经过一天的4条记录
-
我已经开发了，我是假设没有显示在每个类别申请学生的仪表板。而要达到这个我下面的查询。
- 2 tables were involved in the problem : Student_AdmissionDetails ( contains almost 30-35 fields and most of them were having datatype of nvarchar(70)) and the other one was StudentCategory
- After few day from when admission process started, The Student_AdmissionDetails was having about 3,00,000 of records and the StudentCategory was having 4 records
I have developed a dashboard where I was suppose to show No of Students applied in each category. and to achieve this I had following Query.
SELECT COUNT（*） 从Student_AdmissionDetails 内连接StudentCategory 上Student_AdmissionDetails.Id = StudentCategory.Id 其中CategoryTypeName = @的parameterValue
Select count(*)
from Student_AdmissionDetails
inner join StudentCategory
on Student_AdmissionDetails.Id=StudentCategory.Id
where CategoryTypeName=@ParameterValue
上面的查询获取单页3次发射。并有250-300的用户谁是访问同一页面simultaneously.Along与入院形式有1300年至2000年学生在同一时间填写表格。
The above query gets fired on single page 3 times. and There were 250-300 users who were accessing the same page simultaneously.Along with that on admission form there were 1300-2000 students were filling form at the same time.
这是我得到的是，当我在上面它被炒鱿鱼1出5时在SQL Server查询运行的问题。 它会抛出错误，一个死锁发生在访问从内存对象（原谅我不记得确切的错误）。
The problem that i get was when i ran above query in the sql server it gets fired 1 out of 5 time. It throws error that An deadlock has occurred while accessing object from memory(forgive me for not remembering the exact error).
什么，我从以下职位寻找的是：
what i'm looking for from the following post is :
- 这次我是有点幸运，我还没有做出有人从我的编码不开心，但任何人都可以让我知道什么可以做，以克服这样的场景。有什么可以处理大数据块最好的办法
- 我试着用SQL事件探查器来弄明白，但因为有5个应用程序正在运行相似类型矿井我没能找出的howmany用户试图访问同一资源。
我想以下几点将有助于回答我的问题。
I guess following points will be helpful for answering my question.
- 应用程序服务器和数据库服务器不同
- DB服务器是在Windows XP上运行（我猜！），它是具有128 GB的RAM
- 当我从SQL Server，它正在采取12-15秒的平均执行查询执行查询。
写道歉大长今，但我真的需要帮助，了解这一点：）
apologize for writing this big long but i really need help to learn this :)
推荐答案
尝试更新你的 SELECT 添加语句 WITH（NOLOCK）。这将使你的结果不太precise但似乎它足以让你的仪表板。
Try to update your SELECT statement adding WITH (NOLOCK). This will make your results less precise but it seemed that it's enough for your dashboard.
此外，最好在使用类似整数条款。 CategoryTypeId 比 CategoryTypeName WHERE
Also it's better to use something like integer CategoryTypeId than CategoryTypeName in WHERE clause.
这篇关于SQL Server性能和查询执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
