SQL Optimising - 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)

SQL Optimising

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2009-01-12 : 11:08:26

I have the following query:

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(dd, - 1, GETDATE())
OR t2.LASTUPDATE > DATEADD(dd, - 1, GETDATE())
OR t3.LASTUPDATE > DATEADD(dd, - 1, GETDATE())
OR t4.LASTUPDATE > DATEADD(dd, - 1, GETDATE())
OR t5.LASTUPDATE > DATEADD(dd, - 1, GETDATE())
OR t6.LASTUPDATE > DATEADD(dd, - 1, GETDATE())
OR t7.LASTUPDATE > DATEADD(dd, - 1, GETDATE())

ORDER BY MTRLCODE ASC

However it takes 1:30 to execute! Is there anything obvious I can do in order to speed it up?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 11:15:08

do you have proper indexes on your tables?

eddy556
Starting Member

36 Posts

Posted - 2009-01-12 : 11:16:33

I have autonumber primary keys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 11:28:01

i was asking about indexes? what does execution plan show?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-12 : 11:30:20

Do your queries ever search for particular autonumber values? If not you can still use the identity column to maintain uniqueness but perhaps you should look at creating a different clustered index based on column(s) often used in WHERE and / or ORDER statements.

eddy556
Starting Member

36 Posts

Posted - 2009-01-12 : 11:31:19

Very sorry I'm really new to this, the execution plan shows the highest cost (80%) in the join between activities and consider

What do you mean by indexes?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-12 : 11:52:28

Indexes explained:
http://www.sqlteam.com/article/sql-server-indexes-the-basics

You could try creating new indexes for the joining field (mtrlcode) on those tables.
(Non-clustered indexes will be more straight-forward for you to create but clustered may give better performance)
Test query after you have added index. If no improvement then remove as indexes have an overhead.
If these are very large or very busy tables then you may want to add indexes at quiet time, ideally test on a test copy of the database on a test server.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-12 : 12:40:01

quote:
Originally posted by eddy556

Very sorry I'm really new to this, the execution plan shows the highest cost (80%) in the join between activities and consider

What do you mean by indexes?

You can rough idea of missing indexes after you run it by :

sys.dm_db_missing_index_details

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 04:39:07

quote:
Originally posted by sodeep

quote:
Originally posted by eddy556

Very sorry I'm really new to this, the execution plan shows the highest cost (80%) in the join between activities and consider

What do you mean by indexes?

You can rough idea of missing indexes after you run it by :

sys.dm_db_missing_index_details

How do I do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 05:13:09

This maybe?

SELECT		MtrlCode,
		MAX(MatCon) AS MatCon
FROM		(
			SELECT	MtrlCode,
				MatCon,
				LastUpdate,
				1 AS t
			FROM	Material

			UNION ALL

			SELECT	MtrlCode,
				NULL,
				LastUpdate,
				2 AS t
			FROM	Material_Hazard

			UNION ALL

			SELECT	MtrlCode,
				NULL,
				LastUpdate,
				3 AS t
			FROM	Material_Risks

			UNION ALL

			SELECT	MtrlCode,
				NULL,
				LastUpdate,
				4 AS t
			FROM	Activities

			UNION ALL

			SELECT	MtrlCode,
				NULL,
				LastUpdate,
				5 AS t
			FROM	Activity_Symbols

			UNION ALL

			SELECT	MtrlCode,
				NULL,
				LastUpdate,
				6 AS t
			FROM	Consider

			UNION ALL

			SELECT		m.MtrlCode,
					NULL,
					s.LastUpdate,
					7 AS t
			FROM		Supplier AS s
			INNER JOIN	Material AS m ON m.SuppCode = s.SuppCode
		) AS d
GROUP BY	MtrlCode
HAVING		MAX(LastUpdate) > DATEADD(dd, - 1, GETDATE())
		AND COUNT(DISTINCT t) = 7

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

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 09:19:46

Many thanks I have no solved this - Peso your post was of much help, thank you very much.

Here is my finished code:

DECLARE @DATENOW datetime
SET @DATENOW = GETDATE()

DECLARE @DATETHEN datetime
SET @DATETHEN = DATEADD(hh, - 1, @DATENOW)

SELECT MTRLCODE, MATCON, LASTUPDATE
FROM MATERIAL
WHERE MTRLCODE IN(

SELECT MTRLCODE
FROM Material_Hazard
WHERE LASTUPDATE > @DATETHEN

UNION

SELECT MTRLCODE
FROM Material_Risks
WHERE LASTUPDATE > @DATETHEN

UNION

SELECT MTRLCODE
FROM Activities
WHERE LASTUPDATE > @DATETHEN

UNION

SELECT MTRLCODE
FROM Activity_Symbols
WHERE LASTUPDATE > @DATETHEN

UNION

SELECT MTRLCODE
FROM Consider
WHERE LASTUPDATE > @DATETHEN

UNION

SELECT MTRLCODE
FROM Supplier
WHERE LASTUPDATE > @DATETHEN

)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 09:21:57

How fast is the query now?
It took 90 seconds before.

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

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 09:35:45

One second! lol the problem is I have very limited experience with T-SQL :-( Actually since your here and I know this will be simple can you help me?

How do I use a varible in place of the "dd", or "hh" as I would like to pass the SP the amount of time to look back. Heres an example:

ALTER PROCEDURE [dbo].[CHSP_OR_UPDATES_001] @Interval VARCHAR(5) = "dd"

AS

DECLARE @DATETHEN datetime
SET @DATETHEN = DATEADD(@Interval, - 1, @DATENOW)

The DateAdd function won't work. If you can't help I will open another thread?

Once again many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 10:17:47

quote:
Originally posted by eddy556

One second! lol the problem is I have very limited experience with T-SQL :-( Actually since your here and I know this will be simple can you help me?

How do I use a varible in place of the "dd", or "hh" as I would like to pass the SP the amount of time to look back. Heres an example:

ALTER PROCEDURE [dbo].[CHSP_OR_UPDATES_001] @Interval VARCHAR(5) = "dd"

AS

DECLARE @DATETHEN datetime
SET @DATETHEN = DATEADD(@Interval, - 1, @DATENOW)

The DateAdd function won't work. If you can't help I will open another thread?

Once again many thanks!

i've answered it in new thread

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 10:20:31

90 times faster?
Not bad...

No, there are no shortcuts for the interval parameter.

But 1 hour is 3600 seconds, 1 day is 86400 seconds.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources