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屋!