Home Forums SQL Server 2008 SQL Server Newbies Sql Select statement running slow RE: Sql Select statement running slow

  • December 5, 2013 at 3:13 pm

    #1671331

    Thank you so much for the reply. I need to mention that the tables application.app_key and apdoc.user1 exist on separate databases (db1.application.app_key and db2.apdoc.user1.). I am running the query from db1 using a linked server connection to db2. I created an index on db1.application with (app_key, app_dlr_fkey) and one on db2.apdoc with (vendid, user1). After updating the stats on both tables I displayed the proposed execution plan 99% of the cost was on the Inner Join as a nested loop. I got a result set back pretty quick but got 100's of duplicate rows on user1 and vendid even if I user cross join. Any ideas why?

    Select distinct d.dlr_reference_num,d.dlr_key,a.app_dlr_fkey, CONVERT(VARCHAR(30), a.app_key)AS RapAPP, c.user1, c.vendid from NJACCOUNTING01.marlin_test.dbo.apdoc c inner join dbo.application a

    on CONVERT(VARCHAR(30), a.app_key) = SUBSTRING(c.user1, 1,6) , dbo.dealer d

    where c.DocType = 'VO' and c.Crtd_DateTime > '2012-01-01 00:00:00' and c.VendId like '01%'