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
6 1469
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 1,047
Expert 1GB
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
-
)
-
@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',
-
-
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 1,047
Expert 1GB
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
@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 1,047
Expert 1GB
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... ;)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: laurenq uantrell |
last post by:
I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:
One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName =...
|
by: johntarr |
last post by:
I hope I am not asking about something that has been done before, but I
have searched and cannot find an answer. What I am trying to do is to
run a query, and then perform some logic on the...
|
by: Eckhart |
last post by:
Dear All, Plz help me in optimising the following query,
Reduce repeatable reads from the table via select ,ythe table sare not
having referntial integrity constarints ,relations
CREATE proc...
|
by: windandwaves |
last post by:
Hi Gurus
I have the following query:
DELETE ITEM.*
FROM ITEM
LEFT JOIN EXTRA_DETAILS ON
ITEM.item_code = EXTRA_DETAILS.object_code
WHERE EXTRA_DETAILS.field_value IS NULL;
|
by: kenneth |
last post by:
I was trying to use multiple thread to optimize my following code, but
met some problems, anyone can help me?
k are initialized.
int computePot() {
int i, j;
for( i=0; i<500; i++ ) {
for(...
|
by: kshirsagar007 |
last post by:
friends,
I would like to optimize the following query....as its taking 2 minutes to get the records.
select a.CODE "Code",
ud.Name "Name",
a.SchemeId "SchemeID"
from (MASTER sh,...
|
by: bravo |
last post by:
hi
i need to optimize the join query, which joins three tables
say table1 ,table2 , table3 each having huge volume of records...
the query is as
select table1.id,table2.time,table3.Status
from...
|
by: rajeev14th |
last post by:
How can i modify the following query in DB2
(CAST(SUBS_REFILL_COUPONNO_CHANNELID AS INTEGER) (FORMAT '9(16)') (CHAR(16)) )
where SUBS_REFILL_COUPONNO_CHANNELID is the column name of my table...
|
by: David Goldstraw |
last post by:
I have two databases and I want to automate a process to find duplicates. I originally linked the table from one into the other but I have now gone down the route of importing the table from one db...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |