Optmize SQL Query it is taking too much time

Question

1.00/5 (1 vote)

See more:

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.

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

Posted 28-May-14 19:07pm

Mohd Wasif

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2014-05-28T19:59:00

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/[^]

Shanalal Kasim · Answer 2 · 2014-05-28T21:22:00

hi,

you can try below sql

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

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.

Optmize SQL Query it is taking too much time

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0