Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

Help............ SQL Query is too slow


I have a massive, inefficient query in SQL server 2000 database . This query is killing the processor and slows down the entire machine thus other computers are unable to get data from the SQL server. I have just taken over from the previous programmer who has written this query and I don't know much about SQL server. Please help and let me know how to optimize the query.

The query is as follows (I have copied it from the SQL Profiler):

exec sp_executesql N'select t1.jobid as [Lio No],case when len(t3.Name) > 15 then substring(t3.Name,0,15)+''...'' else t3.Name end AS [Client Name],t4.Name As [Matter/Ref],case when len(t1.Description) > 25 then substring(t1.Description,0,20)+'' ...'' else t1.Description end as [Job Description],SUBSTRING(t6.firstname,0,2)+ SUBSTRING(t6.lastName,0,2) as [Owner],convert(varchar, t1.TimeCreated,103)+ SubString(convert(varchar,  t1.TimeCreated,100),12,10) as [TimeIn],convert(varchar, t1.TimeDue,103)+ SubString(convert(varchar, t1.TimeDue,100),12,10) as [TimeDue],convert(varchar, dbo.[GetTimeSent](t1.JobId), 103) + SubString(convert(varchar, dbo.[GetTimeSent](t1.JobId),100),12,10) as [TimeSent],t7.JobStatusName as [Job Status],t1.TimeDue as [Time2],dbo.[GetTimeSent](t1.JobId) as [Time3],t6.firstname+ '' '' + t6.lastName as [FullOwnerName] ,t3.Name as  [ClientFullName],t2.FirstName + '' ''+ t2.LastName as [ContactName],case when len(t4.Name) > 25 then substring(t4.Name,0,20)+''...'' else t4.Name end as Matter,t1.Description as [FullDescription]  from lioe_jobs t1,lioe_contacts t2,lioe_clients t3,lioe_matters t4,lioe_jobTypes t5,USERS t6,lioe_jobs_Status t7 where  t1.MatterId=t4.MatterId and t1.JobTypeId = t5.JobTypeID and t1.OwnerId = t6.UserID and t1.Clientid=t3.clientid and t1.JobStatusId=t7.JobStatusId and t1.ContactId=t2.ContactId and  DateDiff(Month,t1.TimeDue,getDate()) like @MonthDue and DateDiff(day,t1.TimeDue,getDate()) like @DayDue and DateDiff(day,IsNull(t1.TimeCreated,01/01/2000),getDate()) like @DayIn and DateDiff(day,IsNull(dbo.[GetTimeSent](t1.JobId),01/01/2000),getDate()) like @DaySent and t1.JobId like @City and DateDiff(day,IsNull(t1.TimeCreated,getDate()),getDate()) between @TimeIn1 and @TimeIn2 and DateDiff(day,t1.TimeDue,getDate()) between @TimeDue1 and @TimeDue2 and DateDiff(day,IsNull(dbo.[GetTimeSent](t1.JobId),getDate()),getDate()) between @TimeSent1 and @TimeSent2 and DateDiff(Month,IsNull(t1.TimeCreated,getDate()),getDate()) like @TimeIn3 and DateDiff(Month,t1.TimeDue,getDate()) like @TimeDue3 and DateDiff(Month,IsNull(dbo.[GetTimeSent](t1.JobId),getDate()),getDate()) like @TimeSent3 Order By t1.TimeDue desc', N'@MonthDue nvarchar(1),@DayDue nvarchar(1),@DayIn nvarchar(1),@DaySent nvarchar(1),@City nvarchar(15),@TimeIn1 nvarchar(4),@TimeIn2 nvarchar(3),@TimeIn3 nvarchar(1),@TimeDue1 nvarchar(4),@TimeDue2 nvarchar(3),@TimeDue3 nvarchar(1),@TimeSent1 nvarchar(4),@TimeSent2 nvarchar(3),@TimeSent3 nvarchar(1),@Today nvarchar(1)', @MonthDue = N'%', @DayDue = N'%', @DayIn = N'0', @DaySent = N'%', @City = N'[A,C,D,E,][B,]%', @TimeIn1 = N'-365', @TimeIn2 = N'365', @TimeIn3 = N'%', @TimeDue1 = N'-365', @TimeDue2 = N'365', @TimeDue3 = N'%', @TimeSent1 = N'-365', @TimeSent2 = N'365', @TimeSent3 = N'%', @Today = N'%'


Thanks a lot

1 Solution
1) Do you have any indexes? Indexes on columns used in table joins (e.g. MatterId ,JobTypeId etc.), WHERE clauses (e.g. TimeDue, TimeCreated) are very useful.

2) You are using user-defined function (UDF) (dbo.[GetTimeSent]) in WHERE, event repeatedly. This is real performance killer. SQL server will have to execute this function for each record several times.

I suggest you write a stored procedure.
1) Copy records to a temporary table, without filtering data with UDF.
2) Create cursor on this table
3) Scroll through cursor. Execute UDF for each record. If record does not match required criteria, delete it
4) Return remaining records from temp table
5) Delete temp table

If you need more help on this, let me know.
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
Create index in you table to improve your performance also check the execution plan that you index are using for searching condition or not.

i think you should follow Rimvis comment.

it can give much benefit rather than you directly executing you code.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now