Query slow... using CTE

• nithiyanandam-447806

  Ten Centuries

  Points: 1197

  More actions

  December 21, 2011 at 7:31 am

  #252385

  the following qry is very slow. qry have CTE command with union all...

  In my environment we need to find telephone no with two different tables. both tables have more than 5 crore records..

  the qry template...

  WITH telephoneresult AS

  (

  SELECT telephoneno, telephoneid, telephoneaddress

  FROM telephoneregion1 t1

  join telephoneaddresscommon tc on t1.telephoneno = tc.telephoneno

  .....

  .....

  WHERE t1.telephoneno = @telephoneno

  UNION ALL

  SELECT telephoneno, telephoneid, telephoneaddress

  FROM telephoneregion2 t1

  join telephoneaddresscommon tc on t1.telephoneno = tc.telephoneno

  .....

  .....

  WHERE t1.telephoneno = @telephoneno

  )

  SELECT *

  FROM telephoneresult

  now this query returns result with in 5 seconds...

  we have checked indexes and other parts... all are ok.

  we need to get result quickly. is any option instead of CTE?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 21, 2011 at 7:41 am

  #1424236

  Please read this article (http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/) on how best to post performance problem questions. Help us to help you!![/url] 😉

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• LutzM

  SSC Guru

  Points: 107049

  More actions

  December 21, 2011 at 9:44 am

  #1424340

  How did you check indexes and how did you arrive at the conclusion "all are ok"?

  Another option would be to build the UNION ALL cte based on telephoneregion1 and telephoneregion2 only and join the result to telephoneaddresscommon.

  I would have provided the syntax for it but I don't know which column belongs to which table (due to incomplete column reference)...

  Other than that: please provide more information as already stated by Cadavre

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  December 24, 2011 at 2:09 pm

  #1425365

  nithiyanandam-447806 (12/21/2011)

  ---

  the following qry is very slow. qry have CTE command with union all...

  In my environment we need to find telephone no with two different tables. both tables have more than 5 crore records..

  the qry template...

  WITH telephoneresult AS

  (

  SELECT telephoneno, telephoneid, telephoneaddress

  FROM telephoneregion1 t1

  join telephoneaddresscommon tc on t1.telephoneno = tc.telephoneno

  .....

  .....

  WHERE t1.telephoneno = @telephoneno

  UNION ALL

  SELECT telephoneno, telephoneid, telephoneaddress

  FROM telephoneregion2 t1

  join telephoneaddresscommon tc on t1.telephoneno = tc.telephoneno

  .....

  .....

  WHERE t1.telephoneno = @telephoneno

  )

  SELECT *

  FROM telephoneresult

  now this query returns result with in 5 seconds...

  we have checked indexes and other parts... all are ok.

  we need to get result quickly. is any option instead of CTE?

  MR missing code

  MR not

  MR2 CM "....."

  IB MR missing code

  😀

  If the "....." represent missing code, then please post your entire query and the items previously requested so we can actually help with this. Also, make sure that all of the columns in your query use the correct table alias so we can tell which columns belong to which tables. 😉

  --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)
  Intro to Tally Tables and Functions