0

I have 3 tables; A with 10000 rows, B with 392540 rows, and C with 200189 rows.

If i query it like this,

SELECT * FROM A a
LEFT JOIN B b 
ON b.Id = a.Id

I will have the result in a minute. Also, if i query like this,

SELECT * FROM A a
CROSS APPLY(SELECT *
            FROM C c
            WHERE c.Id = a.Id
           ) com

It also returns the data in a minute or less. But when i combine the three,

SELECT * FROM A a
LEFT JOIN B b
   ON b.Id = a.Id
CROSS APPLY (SELECT * 
             FROM C c
             WHERE c.Id = a.Id 
            ) com

The query is still running after 30 mins. Note that i used CROSS APPLY instead of INNER JOIN because INNER JOIN takes much longer. I already tried replacing the LEFT JOIN with an OUTER APPLY but nothing seems to work. Can someone suggest a better way to join these tables? Thanks.

EDIT Tables are provided by clients. I added logic to table B only. For A and C, i get it directly from tables they provided. Here is what i did to table B

DECLARE @B AS TABLE(
        [Id] nvarchar(max)
,       [Name] nvarchar(max)
,       [ProductId] nvarchar(max)
,       [ProductName] nvarchar(max)
)
INSERT INTO @B
  SELECT 
        a.[Id]
    ,   a.[Name]
    ,   a.[ProductId]
    ,   a.[ProductName]
  FROM ( SELECT
              [Id]
          ,   [Name]
          ,   [ProductId]
          ,   [ProductName]
          ,   ROW_NUMBER() OVER(PARTITION BY [Id] ORDER BY [Name] DESC) num
          FROM tableBbyClient 
          WHERE [Description] = 'AS'
        ) a
  WHERE a.num=1
Chester Lim
  • 459
  • 7
  • 19
  • All table Ids are primary key or what else – yatin parab Jul 14 '16 at 17:53
  • @yatinparab All these tables are variable tables made in sql. ( DECLARE @A as TABLE etc) – Chester Lim Jul 14 '16 at 17:55
  • what is the point of joining them? do you have any parameter or reporting structure for which you need the results? Sometimes it is better to figure out what you need in the end, trim the tables down and then join them. – jswan Jul 14 '16 at 18:08
  • @jswan Hello, i joined table B because i need a specific column that is only in table B. For C, same reason with B and i need to get the top 1 when there are duplicates from the data so i used a temp table using a ROW_NUMBER filter for C. Then i joined the three. – Chester Lim Jul 14 '16 at 18:12
  • @ChesterLim What are you trying to accomplish? The cross join is a very expensive join because it takes the first tables rows and multiplies it by the number of rows in the second table. https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx – cal5barton Jul 14 '16 at 18:12
  • @cal5barton Hello, i need the data from table C where C.Id is present in a.Id. Note that the source for the tables are different sources handled by the clients, so i can't just make my own table with all the data needed. Well, i resorted to CROSS APPLY because INNER JOIN takes much longer. – Chester Lim Jul 14 '16 at 18:15
  • Why `cross apply`? Currently it looks like a regular `inner join`. And attach your execution plans. – Ivan Starostin Jul 14 '16 at 18:15
  • @IvanStarostin Yup, it is a normal INNER JOIN and INNER JOIN works when i join A and C only. But when i LEFT JOIN B, CROSS APPLY seems to be faster than INNER JOIN. – Chester Lim Jul 14 '16 at 18:17
  • @sstan Tables are given by the clients. I just use DECLARE @A AS TABLE ( column1 nvarchar(max) etc). Then INSERT INTO @A (SELECT column1, column2 FROM (tableByClient)). So i made 3 different table variables for three different sources. They want to store the data in one table using stored proc. Primary key is the Id. Sorry for a bad explanation. – Chester Lim Jul 14 '16 at 18:21
  • 2
    The problem here is you are using table variables with far too many rows to be effective. Table variables have no statistics and cannot be indexed. Why do you need table variables with that many rows in the first place? I suspect a rethinking of your process here would reap huge rewards in performance. – Sean Lange Jul 14 '16 at 18:39
  • Here is a great answer that goes into some of the details about why you should NOT be using table variables with this much data. http://stackoverflow.com/a/13777841/3813116 – Sean Lange Jul 14 '16 at 18:42
  • @SeanLange Since i have no choice but to join those 3, i used table variables so that i can do the filters for each table to lessen the rows first before joining everything instead of joining all then apply the filters. I will try a different approach. Thank you for your suggestion! – Chester Lim Jul 14 '16 at 18:44
  • 1
    A better approach would be to use ctes. As you have it right now the first thing you are doing is copying all that data into another table. There is no need to do that at all. – Sean Lange Jul 14 '16 at 18:46
  • @SeanLange Thank you for the suggestion. I'll check out ctes and see if it can help reduce the query time. Sorry, i'm new to sql especially on optimizing scripts. – Chester Lim Jul 14 '16 at 18:52
  • No need to apologize. We were all new once. :) That is why we ask questions and people answer. It helps all of us get better. Once you start down the path of not using table variables post another question if you need help. – Sean Lange Jul 14 '16 at 19:19

0 Answers0