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

Posted on 2006-07-12
Last Modified: 2012-06-22

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

Question by:LioElectronic
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 19

Accepted Solution

Rimvis earned 500 total points
ID: 17097452
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.
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 17098529
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.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question