Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-12
2
Medium Priority
?
535 Views
Last Modified: 2012-06-22
Hi

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


HELLLPPPPPP.............

Thanks a lot

AB
0
Comment
Question by:LioElectronic
[X]
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
2 Comments
 
LVL 19

Accepted Solution

by:
Rimvis earned 1500 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.
0
 
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.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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