Very slow query with only 2 joins
-
Hi,
First of all, here are my tables :
CREATE TABLE [dbo].[Rank]([R_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[R_U_id] [int] NOT NULL,
[R_I_id] [int] NOT NULL,
[R_T_id] [int] NOT NULL,
[R_rank] [int] NULL,
CONSTRAINT [PK_Rank] PRIMARY KEY CLUSTERED
(
[R_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Tag]([A_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[A_value] [nvarchar](128) NULL,
[A_flag_cat] [bit] NULL,
[A_lang] [nchar](2) NULL,
CONSTRAINT [PK__Tag__03317E3D] PRIMARY KEY CLUSTERED
(
[A_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[TopicTag]([TT_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TT_A_id] [int] NOT NULL,
[TT_T_id] [int] NOT NULL,
[TT_U_id] [int] NOT NULL,
CONSTRAINT [PK_TopicTag] PRIMARY KEY CLUSTERED
(
[TT_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TopicTag] WITH NOCHECK ADD CONSTRAINT [FK_TOPICTAG_REFERENCE_TAG] FOREIGN KEY([TT_A_id])
REFERENCES [dbo].[Tag] ([A_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TopicTag] CHECK CONSTRAINT [FK_TOPICTAG_REFERENCE_TAG]
GO
ALTER TABLE [dbo].[TopicTag] WITH NOCHECK ADD CONSTRAINT [FK_TOPICTAG_REFERENCE_TOPIC] FOREIGN KEY([TT_T_id])
REFERENCES [dbo].[Topic] ([T_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TopicTag] CHECK CONSTRAINT [FK_TOPICTAG_REFERENCE_TOPIC]
CREATE TABLE [dbo].[Topic]([T_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[T_value] [nvarchar](1000) NULL,
[T_language] [char](2) NULL,
[T_count] [bigint] NULL,
[T_lastModified] [datetime] NULL,
CONSTRAINT [PK__Topic__014935CB] PRIMARY KEY CLUSTERED
(
[T_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
I try to execute that simple query :
SELECT r_t_id, a_value
FROM rank
INNER JOIN topic on r_t_id = t_id
INNER JOIN topictag on t_id = tt_t_id
INNER JOIN tag on tt_a_id = a_id
WHERE t_id = 256
That query takes 16 seconds for 921954 lines returned. I have indexes on join criterias for each table. When I look a the execution plan, here is it :
In the INNER JOIN (94%) I have the following warning : NO JOIN PREDICATE. Why ?
Is there a way to optimize that query ?
Thanks in advance
-
Please post execution plan as a .sqlplan file as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Can you edit that query and qualify the join columns with their table names? Sounds like you've managed to create an accidental cross join. That's what the warning's saying.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hello, and thank you for your answer.
Here is the modified query with qualified tables and columns :
SELECT r.r_t_id, tg.a_value
FROM rank r
INNER JOIN topic tp on r.r_t_id = tp.t_id
INNER JOIN topictag tt on tp.t_id = tt.tt_t_id
INNER JOIN tag tg on tt.tt_a_id = tg.a_id
WHERE tp.t_id = 256
And I join the execution plan too, in .sqplan format.
Thank you very much for your help.
-
According to the execution plan there is no join predicate on the rank table to the others.
If you fully qualify the columns in the joins you should be able to identify the fault.
-
You have attached SQLPlan as per your old query. Use qualify names in the query and check performance & generate the execution plan.
Thanks
-
What plan do you get for
SELECT r_t_id--, a_valueFROM rank
INNER JOIN topic on rank.r_t_id = topic.t_id
--INNER JOIN topictag on t_id = tt_t_id
--INNER JOIN tag on tt_a_id = a_id
WHERE t_id = 256
-
frharkonnen (9/13/2010)
In the INNER JOIN (94%) I have the following warning : NO JOIN PREDICATE. Why ?The top input of the inner join contains an index seek on Topic.t_id = 256. The bottom input contains an index seek on Rank.r_t_id = 256. So there's is no need for a join predicate. To put in in SQL, your query plan looks something like
SELECT r_t_id, a_value
FROM rank
CROSS JOIN topic
INNER JOIN topictag on t_id = tt_t_id
INNER JOIN tag on tt_a_id = a_id
WHERE t_id = 256
AND r_t_id = 256
Peter
-
Hi frharkonnen (maybe Feyd-Rautha?),
this is how I understand your schema:
Tag and Topic are master tables with a N:N relationship, TopicTag is the table that sustains this relationship. Rank is a table linked to Topic.
I feel your schema lacks on indexes. If TopicTag is a relationship table it needs a Topic+Tag index, or a Tag+Topic index, or both; for your query I feel you need an index by TT_T_id + TT_A_id.
Looking at table Rank you need also an index by R_T_id.
In your post the table Rank is unnecessary (I suppose you oversimplified your original query). That's your original query:
SELECT r_t_id, a_valueFROM rank
INNER JOIN topic on r_t_id = t_id
INNER JOIN topictag on t_id = tt_t_id
INNER JOIN tag on tt_a_id = a_id
WHERE t_id = 256
Here you select only a column from the Rank table, and this column belongs also to Topic table, so your query could be simplified as
SELECT t_id, a_valueFROM topic
INNER JOIN topictag on t_id = tt_t_id
INNER JOIN tag on tt_a_id = a_id
WHERE t_id = 256
I feel that implementing these indexes in your TopicTag table will improve your query execution.
Regards,
Francesc
-
frharkonnen (9/13/2010)
That query takes 16 seconds for 921954 lines returnedReturned to WHERE???? Find ANY table with almost a million lines in it and see how long it takes to return them to the screen. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply