Query taking too long to run - 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

General SQL Server Forums

New to SQL Server Administration

Query taking too long to run

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2011-04-06 : 10:03:09

This has been running for over 15 mins what can i do to make it more efficent?

Use ANALYTICS

Go

SELECT NWPFFS AS HUB,LCRGN AS REG,LCDST AS DIST,ILLCN AS LOC,LCNAM AS [LOC NAME],
LXLTYP AS LOCTYPE, IXPBSN AS B, IXPGRP AS G, IXPDPT AS D,ITITMN AS ITEM,ITMFGN AS MFG#,
ITDSC1 AS DESC1,ITDSC2 AS DESC2, ITINVM AS [STK CD], ITVND AS VEND#,ITHGT AS VENDOR,
ILOHNQ AS [ON HAND],ILCMTP AS COMPURCH,ILCMTC AS COMSALE,ILYSLQ AS [SLS YTD QTY],
ILLYSQ AS [SLS LY QTY], ILINVV AS [INV VAL]

FROM (dbo.Item_Location_Master INNER JOIN dbo.Item_Master ON ILITMN=ITITMN)
INNER JOIN dbo.Location_Master ON ILLCN = LCLCN

WHERE ((LXLTYP='R')OR(LXLTYP='T'))
AND ((ITBOMT <> 'T')AND(ITBOMT <> 'S'))
AND ((ILOHNQ<>0) OR(ILCMTP<>0))
AND (IXPBSN <> 9)
AND ((ILITMN<>21542) OR (ILITMN<>21750))
AND (NWPFFS<>9900)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-06 : 12:42:05

What does the execution plan show? Show us your indexes for the tables in the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-07 : 03:50:32

If there are indexes, still exclusion search conditions like <> are not helpful for an optimizer to use an index.
http://connectsql.blogspot.com/2011/02/sql-server-mind-your-search-conditions.html?utm_source=BP_recent

dtrivedi
Posting Yak Master

153 Posts

Posted - 2011-04-07 : 08:53:10

it seems there are no indexes for the table...what can i do

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-07 : 12:31:18

Add indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2011-04-07 : 14:24:13

how do i add indeexes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-07 : 14:27:05

The SSMS GUI can do it, or CREATE INDEX. Please check Books Online for help.

I'd start by indexing the join conditions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-07 : 14:30:30

Do you at least have primary keys defined?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 15:26:15

First

Every table should have clustered index otherwise you cant take advantage of nonclustered index

Second

NonClustered index create on the column which one use in the where clause as in your case LXLTYP column and so on

Third

Split these all tables using in inner join and make temporary tables of each table first then join them

Forth

For Index Script
http://aureus-salah.com/2010/09/15/sql-server-generate-auto-indexes/

Fifth

Use dynamic management view
sys.dm_db_missing_index_details as using in the script in the Forth

Sixth

Use DTA pass this query to Database tuning advisor ,it will create required statistics and indexes for you then execute on your databse

Seventh

Use in the end of the Query

OPTION(MERGE JOIN) or OPTION(LOOP JOIN)

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources