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 ANALYTICSGoSELECT 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 |
|
lionofdezert
Aged Yak Warrior
885 Posts |
|
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 |
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-04-07 : 14:24:13
|
how do i add indeexes? |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-15 : 15:26:15
|
FirstEvery table should have clustered index otherwise you cant take advantage of nonclustered indexSecondNonClustered index create on the column which one use in the where clause as in your case LXLTYP column and so onThird Split these all tables using in inner join and make temporary tables of each table first then join themForthFor Index Scripthttp://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 ForthSixthUse DTA pass this query to Database tuning advisor ,it will create required statistics and indexes for you then execute on your databseSeventhUse in the end of the QueryOPTION(MERGE JOIN) or OPTION(LOOP JOIN)Regards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
 |
|
|