优化SQL查询会花费太多时间 [英] Optmize SQL Query it is taking too much time

问题描述

我的查询要花很多时间.
请帮助如何优化这一点.我已经创建了索引.下面是代码.

Hi,

I have a query that is taking much time.
Please help How to optimize this one.I have already created index. Below is the code.

SELECT  ISNULL(dbo.Note.notCaseId,0) AS CaseID,
   ISNULL(CONVERT(varchar(20),dbo.Note.notNoteDate,103),'') AS NoteDate,
   ISNULL(dbo.Note.notDescription,'') AS NoteDescription,
   ISNULL(dbo.GetRealUserName(dbo.aspnet_Users.UserName),'') AS NoteUserName
 FROM    dbo.Note INNER JOIN
   dbo.aspnet_Users ON dbo.Note.notUserId = dbo.aspnet_Users.UserId
 WHERE dbo.Note.notCaseId IN (Select casCaseId From [case]  INNER join [CaseServiceTypeMapping] on [Case].casCaseID= csrvCaseId
inner join [CaseServiceType] on csrvServiceTypeId= cstyServiceTypeID AND IsGlobal=1  Where casCreateDate BETWEEN 'Apr  1 2012 12:00AM' AND 'Apr  3 2013 12:00AM' ) ORDER BY dbo.Note.notNoteDate DESC

推荐答案

使用Sql事件探查器并通过索引调整向导"调整索引:
http://msdn.microsoft.com/en-us/library/ff650692.aspx [ ^ ]
http://technet.microsoft.com/en-us/library/aa933123％28v = sql.80％29.aspx [ ^ ]
http://sqlmag.com/database-performance-tuning/boost-performance-index-调整向导 [ ^ ]
http://www.mssqltips.com/sqlservertip /1872/sql-server-database-engine-tuning-advisor-for-performance-tuning/ [

Use the Sql profiler and tune your indexes with the Index Tuning Wizard :
http://msdn.microsoft.com/en-us/library/ff650692.aspx[^]
http://technet.microsoft.com/en-us/library/aa933123%28v=sql.80%29.aspx[^]
http://sqlmag.com/database-performance-tuning/boost-performance-index-tuning-wizard[^]
http://www.mssqltips.com/sqlservertip/1872/sql-server-database-engine-tuning-advisor-for-performance-tuning/[^]

您可以在sql
下面尝试

you can try below sql

SELECT  ISNULL(dbo.Note.notCaseId,0) AS CaseID,
   ISNULL(CONVERT(varchar(20),dbo.Note.notNoteDate,103),'') AS NoteDate,
   ISNULL(dbo.Note.notDescription,'') AS NoteDescription,
   ISNULL(dbo.GetRealUserName(dbo.aspnet_Users.UserName),'') AS NoteUserName
 FROM    dbo.Note
 INNER JOIN dbo.aspnet_Users ON dbo.Note.notUserId = dbo.aspnet_Users.UserId
 INNER JOIN [case] ON [case].casCaseId = dbo.Note.notCaseId
 INNER join [CaseServiceTypeMapping] on [Case].casCaseID= csrvCaseId
  inner join [CaseServiceType] on csrvServiceTypeId= cstyServiceTypeID AND IsGlobal=1
        AND casCreateDate BETWEEN 'Apr  1 2012 12:00AM' AND 'Apr  3 2013 12:00AM'
ORDER BY dbo.Note.notNoteDate DESC

如果没有您的数据库，我将无法运行此sql，因此如果您发现任何问题，请运行并通知我.


还有一件事，如果您想要日期硬编码，则不要使用此"Apr 1 2012 12:00 AM"格式，因为如果sql server默认日期格式不同于此格式，则它将无法正常工作.可以使用yyyyMMdd代替此格式.

I can''t run this sql without your db, so please run and inform me if you found any issue.


One more thing if you want a date hardcode then don''t use this ''Apr 1 2012 12:00AM'' format because if sql server default date format is differ than this format then its not work. Instead of this formate you can use yyyyMMdd.

这篇关于优化SQL查询会花费太多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！