how to optimize the sql query the following query is running slow as i have used the

[lsauravbi]

the following query is running slow as i have used the abs function in it

WITH SubFolders(dataid,name,SubType,lvl)
AS
( 

  -- Anchor Member (AM)
  SELECT dataid, Name,subtype, 0
  FROM llirimsuser.DTree (nolock)
  WHERE dataid = 28264085
  UNION ALL 
  -- Recursive Member (RM)
  SELECT D.DataID,D.Name,D.SubType,M.lvl+1
  FROM llirimsuser.DTree (nolock)  AS D
    JOIN SubFolders  AS M
      ON abs(D.ParentID)= M.dataid
)
SELECT * FROM SubFolders where SubType=412

any idea how to optimize it I have to use the abs function it as i am getting negative value
plz help

[Rabbit]

Why not fix the data? Update the negative values to positive.

But I suspect that's not the real cause of the slowness. It's more likely the recursive joins.

[Luuk]

I hope you have the correct indexes....

Try replacing lines #11..#14 with:

SELECT * FROM (
 SELECT D.DataID,D.Name,D.SubType,M.lvl+1
  FROM llirimsuser.DTree (nolock)  AS D
    JOIN SubFolders  AS M
      ON D.ParentID= M.dataid
 UNION
 SELECT D.DataID,D.Name,D.SubType,M.lvl+1
  FROM llirimsuser.DTree (nolock)  AS D
    JOIN SubFolders  AS M
      ON -D.ParentID= M.dataid
)

[lsauravbi]

@lucck I tried to follow yours approach and replaced the code
with the following

WITH SubFolders(dataID,ParentID,Name,Comment,SubType,Unit,lvl)
AS
( 
  -- Anchor Member (AM)
  SELECT DataID ,ParentID,Name,DComment,
    Subtype,(select name from llirimsuser.DTree(nolock) where dataid=ownerid) as 'Unit', 
   0
  FROM llirimsuser.DTree (nolock)
  WHERE DataID =  28264085
  UNION ALL 
  -- Recursive Member (RM)
      SELECT * FROM (
 SELECT D.DataID,D.ParentID,D.Name,D.SubType,M.lvl+1
  FROM llirimsuser.DTree (nolock)  AS D
    JOIN SubFolders  AS M
      ON D.ParentID= M.dataid
 UNION 
 SELECT D.DataID,D.ParentID,D.Name,D.SubType,N.lvl+1
  FROM llirimsuser.DTree (nolock)  AS D
    JOIN SubFolders  AS N
      ON -D.ParentID= N.dataid
)

on line 20 error is coming as 'Recursive member of common table expression has multiple recursive referense' can you help me?
Index is correct the problem of coming of executing query slowly arnd 2 min is coming only when we are using abs() function otherwise its running fine completing 0.02 sec

[Luuk]

Wait....

This is about MySQL?

MySQL does not support 'WITH..' (line#1)

It is hard to find the problem without the same tables, and some sample data, but basically try to rewrite your original query in a way that it will return the data you want

you can alsow trye to replace

JOIN SubFolders  AS M
      ON abs(D.ParentID)= M.dataid

with:

JOIN SubFolders  AS M
      ON D.ParentID= M.dataid OR -D.ParentID= M.dataid

[lsauravbi]

@Luuk--thnks 4 reply this query is for sql server

I have used

JOIN SubFolders  AS M
          ON D.ParentID= M.dataid OR -D.ParentID= M.dataid

as suggested but it is taking same time to execute as with using abs function previously.;-)

[Luuk]

ok,

@Rabbit: Could you move this topic to mssql server? ;-)

@Isauravbi: I'm not good at mssql server, but you should look at the execution plan of your query, and decide wif you have the proper indexes* on the tables involved.

*) 1 Index , 2 Indexes ??...... English is hard... ;)