Solved

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

Posted on 2006-07-12
2
529 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
2 Comments
 
LVL 19

Accepted Solution

by:
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.
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 28
Error when creating an UPDATE Trigger in SQL 6 21
Are triggers slow? 7 12
MS SQL query to show nearest date 6 39
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?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

828 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