Improve SQL Query Syntax

[Fletch12]

This query takes a long time to run and actually ends up causing another application that is accessing SQL at the same time to halt. I thought the "WITH (NOLOCK)" clause should have taken care of that problem but it hasn't. Can anyone offer any suggestions to improving this query?

IF DATEPART(hour, getdate()) BETWEEN 6 AND 17 
BEGIN 
SELECT     	DATEPART(hour, ProcessDate) AS 'Beg Hr', 
		DATEPART(hour, ProcessDate) + 1 AS 'End Hr', 
		COUNT(*) / 2 AS 'Number of Parts'
FROM         dbo.[Scan Detail] WITH (NOLOCK)
WHERE     	((DATEPART(hour, ProcessDate) BETWEEN 6 AND 9) AND (ProcessDate >= GETDATE() - 0.5) 
                  	AND (LEFT(ScanData, 3) = 'TDL'))
GROUP BY DATEPART(hour, ProcessDate), DATEPART(hour, ProcessDate) + 1
ORDER BY DATEPART(hour, ProcessDate) 
END ELSE 
BEGIN
SELECT     	DATEPART(hour, ProcessDate) AS 'Beg Hr', 
		DATEPART(hour, ProcessDate) + 1 AS 'End Hr', 
		COUNT(*) / 2 AS 'Number of Parts'
FROM         dbo.[Scan Detail] WITH (NOLOCK)
WHERE     	((DATEPART(hour, ProcessDate) BETWEEN 18 AND 21) 
	     	AND (ProcessDate >= GETDATE() - 0.5) 
	     	AND (LEFT(ScanData, 3) = 'TDL'))
GROUP BY DATEPART(hour, ProcessDate), DATEPART(hour, ProcessDate) + 1
ORDER BY DATEPART(hour, ProcessDate) 
END

 

[SQLSister]

HAve you looked at the execution plan? Are you doing table scans? What kind of indexing is on this table?

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Do you have indexes on this table? If so, are the indexes being used?

I would 'attack' this problem by making the queries faster. Sometimes simple things can make a big difference. For example, if you have an index on the ScanData column in the [Scan Detail] table, then a simple change of....

Left(ScanData, 3) = 'TDL'

To:
ScanData Like 'TDL%'

could make a big difference.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

I see a bunch of scans already

WHERE((DATEPART(hour, ProcessDate) BETWEEN 18 AND 21)
AND (LEFT(ScanData, 3) = 'TDL'))

functions on columns in the WHERE clause will cause scans


for the second one use ScanData Like 'TDL%'
for the first one create an additional column call it DateHour (or something like that) use datepart(hh,ProcessDate) to populate the values, you can make it an indexed computed column, tinyint should be fine



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Fletch12]

Currently there is no index on the "ScanData" column. If I add this index do you think by then changing the query to ScanData Like 'TDL%' would help?

 

[SQLDenis]

>> If I add this index do you think by then changing the query to ScanData Like 'TDL%' would help?

yes but not for the date part

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Fletch12]

Okay, I'm slightly hesitant to add new columns and indexes to this table in fear of causing any application problems. But if I only add a new column and index on that one column I wouldn't really have to anything to worry about, correct?

 

[gmmastros]

How many rows are in this table?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Fletch12]

# of rows = 1299079

 

[Fletch12]

Based on the number of rows, 1299079, would it benefit to make the column addtion for date part?

 

[gmmastros]

You do have a development database don't you?

Seriously, you should NOT make changes on a production server. I recommend that you back up the database and restore it on a development server. Then, you can see how it performs on a development server.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom