Slow SELECT statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow SELECT statement

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 05:17:19

Please note this is related to post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117644

I have a SELECT statement which is very very slow at the moment (around two minutes to execute). Since I have very little experience at SQL and research proving rather fruitless I have included the code and execution plan here.

Any tips?


SELECT DISTINCT t1.MTRLCODE, t1.MATCON
FROM MATERIAL t1
JOIN Material_Hazard t2 ON
	t1.mtrlcode = t2.mtrlcode

JOIN Material_Risks t3 ON
	t1.mtrlcode = t3.mtrlcode

JOIN Activities t4 ON
	t1.mtrlcode = t4.mtrlcode

JOIN Activity_Symbols t5 ON
	t1.mtrlcode = t5.mtrlcode

JOIN Consider t6 ON
	t1.mtrlcode = t6.mtrlcode

JOIN Supplier t7 ON
	t1.suppcode = t7.suppcode

WHERE	   t1.LASTUPDATE > DATEADD(hh, - 1, GETDATE())
		OR t2.LASTUPDATE > DATEADD(hh, - 1, GETDATE())
		OR t3.LASTUPDATE > DATEADD(hh, - 1, GETDATE())
		OR t4.LASTUPDATE > DATEADD(hh, - 1, GETDATE())
		OR t5.LASTUPDATE > DATEADD(hh, - 1, GETDATE())
		OR t6.LASTUPDATE > DATEADD(hh, - 1, GETDATE())
		OR t7.LASTUPDATE > DATEADD(hh, - 1, GETDATE())

ORDER BY MTRLCODE ASC

Sorry if its hard to see - you will notice the highest cost are the two Merge Joins (Inner Join) at the top left.

View on Flickr (bigger image) [url]http://www.flickr.com/photos/33249415@N06/3193201339/[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 05:28:54

It's better if you save the execution plan (*.sqlplan), and post a link to that file.

E 12°55'05.63"
N 56°04'39.26"

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 05:42:56

Hmm yes I have no where to host it though? I've tried posting on geocities however it has to be of extension .jpg or something :-( anyone got somewhere I can host the .sqlplan?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 05:44:04

As mentioned in previous post indexes may help.
Highligh your query and click on "analyse query in database engine tuning advisor" button. Enter password if prompted, then click start analysis button.
If recommendations are made - copy text from definition column and run in new query window.

I've added sample code which would create indexes for the joining fields but look at recommendations first.
If you still have problems: sample data, table structure and size of tables would help us.

CREATE NONCLUSTERED INDEX [inx_Mtrlcode] ON Material
(
mtrlcode ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [inx_Mtrlcode] ON Material_Risks
(
mtrlcode ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [inx_Mtrlcode] ON Activities
(
mtrlcode ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [inx_Mtrlcode] ON Activity_Symbols
(
mtrlcode ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [inx_Mtrlcode] ON Consider
(
mtrlcode ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [inx_Mtrlcode] ON Supplier
(
mtrlcode ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 06:00:07

Okay, just ran the analyse query in database engine tuning advisor and no recommendations were made

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 06:21:37

Using trial and error the problem appears to be the WHERE clause. How can this be modified in order to speed things up?

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 06:49:58

Okay I have now narrowed it down - its due to the functions in the WHERE clause causing SQL Server to evaluate it for each data item in order to see if it satifies the criteria. I have managed to shave a few seconds off by removing the GETDATE() function instead storing the date in a variable and reusing it. My WHERE clause now looks like:

WHERE t1.LASTUPDATE > DATEADD(dd, - 1, @DATE)
OR t2.LASTUPDATE > DATEADD(dd, - 1, @DATE)
OR t3.LASTUPDATE > DATEADD(dd, - 1, @DATE)
OR t4.LASTUPDATE > DATEADD(dd, - 1, @DATE)
OR t5.LASTUPDATE > DATEADD(hh, - 1, @DATE)
OR t6.LASTUPDATE > DATEADD(hh, - 1, @DATE)
OR t7.LASTUPDATE > DATEADD(hh, - 1, @DATE)

However I now need an alternative to the DATEADD function which uses less resources - or even better prevent the need to use a function at all.

Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 07:15:28

Have you even checked your other duplicate topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117644

E 12°55'05.63"
N 56°04'39.26"

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 07:17:11

Yes I did sorry

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 09:20:32