Improve Speed of query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Improve Speed of query

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2012-04-02 : 03:50:18

Dear Sir,
I have Written following query when i execute it takes much time i think it is due to case statement whats the procedure of improving Speed
my Stored Procedure is as Followed

ALTER PROCEDURE [dbo].[Procedure1]
@EmpID int,
@FromDate DATETIME,
@ToDate DATETIME,
@Dept NVARCHAR(50)
as
BEGIN
IF ISNULL(@FromDate,'') = ''
SELECT @FromDate = '20110101'
IF ISNULL(@ToDate,'') = ''
SELECT @ToDate = GETDATE()

SELECT
D1.EmployeeCode,
D1.[Date],
D2.[Time In], D2.[Time Out], D2.Spend,
D2.UserName, D2.USERID,
D2.DutyHours, D2.ShiftTimeDiff,
D2.ExcShort, CONVERT(CHAR(5), D2.EShortTime,108) EShortTime ,
D2.DiffHours, D2.DiffMin,
D2.Shift, D2.Branch, D1.Department, D1.Name,D1.EmpBranch,

CASE
-- WHEN ISNULL(LEFT(CONVERT(NVARCHAR, D4.TimeIn),11),'') <> '' THEN D4.Remarks
WHEN D3.Date IS NOT NULL THEN D3.Remarks
WHEN D1.[Date] > GETDATE() THEN NULL
WHEN D2.[Time in] IS NULL THEN 'Absent'
ELSE D2.Remarks
END Remarks,

LTIME, HDTIME, EARLYArival,D2.InBetween,D2.Comments

FROM
AllDateView2 D1
LEFT OUTER JOIN HFAllAttendance D2 ON D1.EmployeeCode = D2.[Employee Code] AND D1.[Date] = convert(datetime, D2.[Date], 105)
LEFT OUTER JOIN FHolidayView D3 ON D1.[Date] = D3.[Date]

WHERE
D1.[Date] BETWEEN @FromDate and @ToDate
AND (ISNULL(@Dept,'')='' OR D1.Department = @Dept)
AND (ISNULL(@EmpID,'')='' OR D1.[EmployeeCode] = @EmpID)

END

Thanks In Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 15:58:42

see

http://sqlandthelike.blogspot.com/2009/06/catch-all-queries-and-indexing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ceemack
Starting Member

3 Posts

Posted - 2012-04-03 : 12:23:33

The case statement in and of itself doesn't look too bad. Check the actual execution plan for table scans and clustered index scans to see what indexes might be added to help speed up the query. If you're using the 2008 version of SSMS, the execution plan will show you any indexes that Query Optimizer thinks would help.

I might also take the convert statement out of the conditions for the first outer join. Generally speaking, you want to keep conversions out of WHERE clauses and JOIN conditions. In this particular case, that conversion doesn't appear to be doing anything for you.

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-03 : 12:45:47

Can you post the DDL of the tables in your Query? Including all indexes and keys?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:25:43

if dataset is large it might be worth using dynamic sql rather than set of OR conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources