Indexes - 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 2000 Forums

SQL Server Development (2000)

Indexes

Author

Topic

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-23 : 11:20:13

I hvae several queries that look like the follwing but use differant parameters. For optimization purposes which fields in CKIS should be indexed?

SELECT KEKO.MATNR, SUM(p.WERTN) AS MATERIALCOST
FROM KEKO INNER JOIN
CKIS AS p ON KEKO.KALNR = p.KALNR AND KEKO.KADKY = p.KADKY
WHERE (p.KADKY =
(SELECT MAX(KADKY) AS Expr1
FROM CKIS AS x
WHERE (p.KALNR = KALNR))) AND (p.WERKS = '0010') AND (p.KADKY NOT IN ('00000000')) AND (p.TYPPS = 'M') AND (p.KZANW NOT IN ('X')) AND
(p.ELEMT = '003')
GROUP BY KEKO.MATNR

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-23 : 11:22:09

Also should it be individual indexes or a single group index?

MCP, MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-23 : 12:01:54

Did you do an explain?

Please post the DDL, and try formatting


     SELECT K.MATNR
	  , SUM(p.WERTN) AS MATERIALCOST
      FROM KEKO K
INNER JOIN CKIS AS p 
	ON K.KALNR = p.KALNR 
       AND K.KADKY = p.KADKY
     WHERE p.KADKY = (SELECT MAX(KADKY) AS Expr1
			FROM CKIS AS x
		       WHERE p.KALNR = KALNR) 
       AND p.WERKS  = '0010' 
       AND p.KADKY <> '00000000'
       AND p.TYPPS  = 'M' 
       AND p.KZANW <> 'X' 
       AND p.ELEMT  = '003'
  GROUP BY K.MATNR

AS EXPR1????? is his access?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-23 : 12:23:59

SQL 2000

SELECT KEKO.MATNR, SUM(p.WERTN) AS MATERIALCOST
FROM KEKO INNER JOIN
CKIS AS p ON KEKO.KALNR = p.KALNR AND KEKO.KADKY = p.KADKY
WHERE (p.KADKY =
(SELECT MAX(KADKY) AS Expr1
FROM CKIS AS x
WHERE (p.KALNR = KALNR))) AND (p.WERKS = '0010') AND (p.KADKY NOT IN ('00000000')) AND (p.TYPPS = 'M') AND (p.KZANW NOT IN ('X')) AND
(p.ELEMT = '003')
GROUP BY KEKO.MATNR

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-23 : 12:24:44

I posted it formatted - don't know why the browser is un-formatting it????

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-23 : 12:27:27

I ecperimented by:
KALNR
WERKS
KADKY
TYPPS
ELEMT
KZANW
I made a single grouped index and it appears to be working nicely.
I thnak I may have solved my own issue.

MCP, MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources