By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,448 Members | 866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,448 IT Pros & Developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. WITH SubFolders(dataid,name,SubType,lvl)
  2. AS
  3.  
  4.   -- Anchor Member (AM)
  5.   SELECT dataid, Name,subtype, 0
  6.   FROM llirimsuser.DTree (nolock)
  7.   WHERE dataid = 28264085
  8.   UNION ALL 
  9.   -- Recursive Member (RM)
  10.   SELECT D.DataID,D.Name,D.SubType,M.lvl+1
  11.   FROM llirimsuser.DTree (nolock)  AS D
  12.     JOIN SubFolders  AS M
  13.       ON abs(D.ParentID)= M.dataid
  14. )
  15. 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
Mar 28 '14 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Mar 28 '14 #2

Expert 100+
P: 1,043
I hope you have the correct indexes....

Try replacing lines #11..#14 with:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM (
  2.  SELECT D.DataID,D.Name,D.SubType,M.lvl+1
  3.   FROM llirimsuser.DTree (nolock)  AS D
  4.     JOIN SubFolders  AS M
  5.       ON D.ParentID= M.dataid
  6.  UNION
  7.  SELECT D.DataID,D.Name,D.SubType,M.lvl+1
  8.   FROM llirimsuser.DTree (nolock)  AS D
  9.     JOIN SubFolders  AS M
  10.       ON -D.ParentID= M.dataid
  11. )
  12.  
Mar 28 '14 #3

P: 8
@lucck I tried to follow yours approach and replaced the code
with the following
Expand|Select|Wrap|Line Numbers
  1. WITH SubFolders(dataID,ParentID,Name,Comment,SubType,Unit,lvl)
  2. AS
  3.   -- Anchor Member (AM)
  4.   SELECT DataID ,ParentID,Name,DComment,
  5.     Subtype,(select name from llirimsuser.DTree(nolock) where dataid=ownerid) as 'Unit', 
  6.  
  7.   FROM llirimsuser.DTree (nolock)
  8.   WHERE DataID =  28264085
  9.   UNION ALL 
  10.   -- Recursive Member (RM)
  11.       SELECT * FROM (
  12.  SELECT D.DataID,D.ParentID,D.Name,D.SubType,M.lvl+1
  13.   FROM llirimsuser.DTree (nolock)  AS D
  14.     JOIN SubFolders  AS M
  15.       ON D.ParentID= M.dataid
  16.  UNION 
  17.  SELECT D.DataID,D.ParentID,D.Name,D.SubType,N.lvl+1
  18.   FROM llirimsuser.DTree (nolock)  AS D
  19.     JOIN SubFolders  AS N
  20.       ON -D.ParentID= N.dataid
  21. )
  22.  
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
Mar 31 '14 #4

Expert 100+
P: 1,043
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
Expand|Select|Wrap|Line Numbers
  1. JOIN SubFolders  AS M
  2.       ON abs(D.ParentID)= M.dataid
with:
Expand|Select|Wrap|Line Numbers
  1. JOIN SubFolders  AS M
  2.       ON D.ParentID= M.dataid OR -D.ParentID= M.dataid
Apr 1 '14 #5

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

I have used
Expand|Select|Wrap|Line Numbers
  1. JOIN SubFolders  AS M
  2.           ON D.ParentID= M.dataid OR -D.ParentID= M.dataid
  3.  
as suggested but it is taking same time to execute as with using abs function previously.;-)
Apr 1 '14 #6

Expert 100+
P: 1,043
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... ;)
Apr 1 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.