Why is my CTE very slow ? - 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 2005 Forums

Transact-SQL (2005)

Why is my CTE very slow ?

Author

Topic

strela
Starting Member

3 Posts

Posted - 2008-05-27 : 11:41:23

Hello,

I have 4000 record in my table employee. it takes 13 sec to get data. It this normal ? What is wrong ?

Thanks

Code Snippet

CREATE FUNCTION [dbo].[VrniStrukturo] (@id_sod int)
RETURNS TABLE
AS
RETURN
(
WITH tree(id, parent_id, naziv, nivo) AS
(
-- Base case
SELECT
id,
parent_id,
naziv,
1 as nivo
FROM employee
WHERE id = @id_sod

UNION ALL

-- Recursive step
SELECT
e.id,
e.parent_id,
e.naziv,
eh.nivo + 1 AS nivo
FROM employee e
INNER JOIN tree eh ON
e.parent_id = eh.id
)

--SELECT *
SELECT id
FROM tree
--ORDER BY nivo, priimek, ime
);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 11:49:59

What all indexes you have on your table?Have a had a look at execution plan to see what are costly steps?

strela
Starting Member

3 Posts

Posted - 2008-05-27 : 11:56:41

TABLE employee
int id (primary key)
int parent_id
varchar naziv

I also added indeks on id and parent id

HERE IS CODE :

CREATE TABLE [dbo].[sodelavec] (
[id] int NOT NULL,
[parent_id] int NULL,
[naziv] varchar(25) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [sodelavec_pk] PRIMARY KEY CLUSTERED ([id])
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [sodelavec_idx] ON [dbo].[sodelavec]
([id], [parent_id])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 11:59:18

Your non-clustered index (id, parent_id) will not be used by your query since all you are passing the recursive join is parent_id.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 12:03:04

If you were working your way DOWN the hierarchy starting with an ultimate parent and ending with the last child employee then you would be make use of the primary key (ON employee.id = tree.parentid)

Since you are going the other direction you would need an index on just (parent_id)
(ON e.parent_id = tree.id)

Be One with the Optimizer
TG

strela
Starting Member

3 Posts

Posted - 2008-05-27 : 12:23:21

JUHU !
Query OK (219 ms) instead of 13 sec.

I added index only on parent_id as you told me to.
"Since you are going the other direction you would need an index on just (parent_id) (ON e.parent_id = tree.id) "

Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources