How can I combine the three queries to one and possibly improve performance

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 12, 2018 at 9:36 am

  #2016113

  First, ask yourself, could I solve the problem if all I have is what you have given us.  Remember, we don't know what you are trying to accomplish, we don't see what you see, so all we have is what you tell us.  You have cut it down to the simplest and most generic code you can.  This means there is actually a loss in meaning making it harder for us to figure out what you want.  You provided minimal sample data but left out several of the most important things.  One, working code that could be cut/paste/executed in an empty sandbox database to setup a working environment.  Two, expected results based on the sample data provided.

  With that, here is my shot at your problem.
  
create table [dbo].[table1]
(
  [id] int
, [region] int
, [typeid] int
);

  insert into [dbo].[table1]
(
  [id]
, [region]
, [typeid]
)
values
(1, 2, 1)
, (2, 3, 1)
, (3, 4, 2)
, (4, 1, 2)
, (5, 1, 2)
, (6, 2, 4);

  create table [dbo].[table2]
(
  [id] int
, [type] varchar(10)
);
insert into [dbo].[table2]
(
  [id]
, [type]
)
values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D');
go

  with BaseData as (
select [t1].[id]
  , [t1].[region]
  , [t1].[typeid]
  --, [t2].[id]
  , [t2].[type]
  , [cnt] = count(*) over (partition by [t2].[type])
from
[dbo].[table1] as [t1]
inner join [dbo].[table2] as [t2]
  on [t1].[typeid] = [t2].[id]
)
select
*
from
[BaseData] as [bd]
where
[bd].[cnt] > 2;
go

• PasLe Choix

  Say Hey Kid

  Points: 665

  More actions

  December 12, 2018 at 7:37 pm

  #2016171

  Lynn Pettis - Wednesday, December 12, 2018 9:36 AM

  First, ask yourself, could I solve the problem if all I have is what you have given us.  Remember, we don't know what you are trying to accomplish, we don't see what you see, so all we have is what you tell us.  You have cut it down to the simplest and most generic code you can.  This means there is actually a loss in meaning making it harder for us to figure out what you want.  You provided minimal sample data but left out several of the most important things.  One, working code that could be cut/paste/executed in an empty sandbox database to setup a working environment.  Two, expected results based on the sample data provided.

  With that, here is my shot at your problem.
  
create table [dbo].[table1]
(
  [id] int
, [region] int
, [typeid] int
);

  insert into [dbo].[table1]
(
  [id]
, [region]
, [typeid]
)
values
(1, 2, 1)
, (2, 3, 1)
, (3, 4, 2)
, (4, 1, 2)
, (5, 1, 2)
, (6, 2, 4);

  create table [dbo].[table2]
(
  [id] int
, [type] varchar(10)
);
insert into [dbo].[table2]
(
  [id]
, [type]
)
values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D');
go

  with BaseData as (
select [t1].[id]
  , [t1].[region]
  , [t1].[typeid]
  --, [t2].[id]
  , [t2].[type]
  , [cnt] = count(*) over (partition by [t2].[type])
from
[dbo].[table1] as [t1]
inner join [dbo].[table2] as [t2]
  on [t1].[typeid] = [t2].[id]
)
select
*
from
[BaseData] as [bd]
where
[bd].[cnt] > 2;
go

  Thank you very much Lynn. 

  And my apology to everyone here replied here, the simplification does lost some important meaning here like Lynn said. As policy in place, it is unfortunate that sometimes people can not simply post their original query here, and, in my case, the original query contains too complex business logic in it which will confuse people even more.

  Anyway, my sincere thanks to everyone.

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  December 12, 2018 at 8:00 pm

  #2016174

  PasLe Choix - Wednesday, December 12, 2018 7:37 PM

  Lynn Pettis - Wednesday, December 12, 2018 9:36 AM

  First, ask yourself, could I solve the problem if all I have is what you have given us.  Remember, we don't know what you are trying to accomplish, we don't see what you see, so all we have is what you tell us.  You have cut it down to the simplest and most generic code you can.  This means there is actually a loss in meaning making it harder for us to figure out what you want.  You provided minimal sample data but left out several of the most important things.  One, working code that could be cut/paste/executed in an empty sandbox database to setup a working environment.  Two, expected results based on the sample data provided.

  With that, here is my shot at your problem.
  
create table [dbo].[table1]
(
  [id] int
, [region] int
, [typeid] int
);

  insert into [dbo].[table1]
(
  [id]
, [region]
, [typeid]
)
values
(1, 2, 1)
, (2, 3, 1)
, (3, 4, 2)
, (4, 1, 2)
, (5, 1, 2)
, (6, 2, 4);

  create table [dbo].[table2]
(
  [id] int
, [type] varchar(10)
);
insert into [dbo].[table2]
(
  [id]
, [type]
)
values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D');
go

  with BaseData as (
select [t1].[id]
  , [t1].[region]
  , [t1].[typeid]
  --, [t2].[id]
  , [t2].[type]
  , [cnt] = count(*) over (partition by [t2].[type])
from
[dbo].[table1] as [t1]
inner join [dbo].[table2] as [t2]
  on [t1].[typeid] = [t2].[id]
)
select
*
from
[BaseData] as [bd]
where
[bd].[cnt] > 2;
go

  Thank you very much Lynn. 

  And my apology to everyone here replied here, the simplification does lost some important meaning here like Lynn said. As policy in place, it is unfortunate that sometimes people can not simply post their original query here, and, in my case, the original query contains too complex business logic in it which will confuse people even more.

  Anyway, my sincere thanks to everyone.

  You're welcome but I'd still like to know why the requirement to preserve the interim temporary tables exists.  It does not seem logical.

  --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)
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 13, 2018 at 6:53 am

  #2016212

  PasLe Choix - Wednesday, December 12, 2018 7:37 PM

  Lynn Pettis - Wednesday, December 12, 2018 9:36 AM

  First, ask yourself, could I solve the problem if all I have is what you have given us.  Remember, we don't know what you are trying to accomplish, we don't see what you see, so all we have is what you tell us.  You have cut it down to the simplest and most generic code you can.  This means there is actually a loss in meaning making it harder for us to figure out what you want.  You provided minimal sample data but left out several of the most important things.  One, working code that could be cut/paste/executed in an empty sandbox database to setup a working environment.  Two, expected results based on the sample data provided.

  With that, here is my shot at your problem.
  
create table [dbo].[table1]
(
  [id] int
, [region] int
, [typeid] int
);

  insert into [dbo].[table1]
(
  [id]
, [region]
, [typeid]
)
values
(1, 2, 1)
, (2, 3, 1)
, (3, 4, 2)
, (4, 1, 2)
, (5, 1, 2)
, (6, 2, 4);

  create table [dbo].[table2]
(
  [id] int
, [type] varchar(10)
);
insert into [dbo].[table2]
(
  [id]
, [type]
)
values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D');
go

  with BaseData as (
select [t1].[id]
  , [t1].[region]
  , [t1].[typeid]
  --, [t2].[id]
  , [t2].[type]
  , [cnt] = count(*) over (partition by [t2].[type])
from
[dbo].[table1] as [t1]
inner join [dbo].[table2] as [t2]
  on [t1].[typeid] = [t2].[id]
)
select
*
from
[BaseData] as [bd]
where
[bd].[cnt] > 2;
go

  Thank you very much Lynn. 

  And my apology to everyone here replied here, the simplification does lost some important meaning here like Lynn said. As policy in place, it is unfortunate that sometimes people can not simply post their original query here, and, in my case, the original query contains too complex business logic in it which will confuse people even more.

  Anyway, my sincere thanks to everyone.

  What you didn't say is if the code I provided was helpful or at least pointed you in a better direction.  You also didn't answer Jeff's question(s).

• PasLe Choix

  Say Hey Kid

  Points: 665

  More actions

  December 13, 2018 at 7:58 am

  #2016228

  Jeff Moden - Wednesday, December 12, 2018 8:00 PM

  PasLe Choix - Wednesday, December 12, 2018 7:37 PM

  Lynn Pettis - Wednesday, December 12, 2018 9:36 AM

  First, ask yourself, could I solve the problem if all I have is what you have given us.  Remember, we don't know what you are trying to accomplish, we don't see what you see, so all we have is what you tell us.  You have cut it down to the simplest and most generic code you can.  This means there is actually a loss in meaning making it harder for us to figure out what you want.  You provided minimal sample data but left out several of the most important things.  One, working code that could be cut/paste/executed in an empty sandbox database to setup a working environment.  Two, expected results based on the sample data provided.

  With that, here is my shot at your problem.
  
create table [dbo].[table1]
(
  [id] int
, [region] int
, [typeid] int
);

  insert into [dbo].[table1]
(
  [id]
, [region]
, [typeid]
)
values
(1, 2, 1)
, (2, 3, 1)
, (3, 4, 2)
, (4, 1, 2)
, (5, 1, 2)
, (6, 2, 4);

  create table [dbo].[table2]
(
  [id] int
, [type] varchar(10)
);
insert into [dbo].[table2]
(
  [id]
, [type]
)
values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D');
go

  with BaseData as (
select [t1].[id]
  , [t1].[region]
  , [t1].[typeid]
  --, [t2].[id]
  , [t2].[type]
  , [cnt] = count(*) over (partition by [t2].[type])
from
[dbo].[table1] as [t1]
inner join [dbo].[table2] as [t2]
  on [t1].[typeid] = [t2].[id]
)
select
*
from
[BaseData] as [bd]
where
[bd].[cnt] > 2;
go

  Thank you very much Lynn. 

  And my apology to everyone here replied here, the simplification does lost some important meaning here like Lynn said. As policy in place, it is unfortunate that sometimes people can not simply post their original query here, and, in my case, the original query contains too complex business logic in it which will confuse people even more.

  Anyway, my sincere thanks to everyone.

  You're welcome but I'd still like to know why the requirement to preserve the interim temporary tables exists.  It does not seem logical.

  I have no idea why I inherited such a non-logical work here, no document, original developer left, that's what I have now

  Not funny at all, isn't it?

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  December 13, 2018 at 9:08 pm

  #2016278

  PasLe Choix - Thursday, December 13, 2018 7:58 AM

  Jeff Moden - Wednesday, December 12, 2018 8:00 PM

  PasLe Choix - Wednesday, December 12, 2018 7:37 PM

  Lynn Pettis - Wednesday, December 12, 2018 9:36 AM

  First, ask yourself, could I solve the problem if all I have is what you have given us.  Remember, we don't know what you are trying to accomplish, we don't see what you see, so all we have is what you tell us.  You have cut it down to the simplest and most generic code you can.  This means there is actually a loss in meaning making it harder for us to figure out what you want.  You provided minimal sample data but left out several of the most important things.  One, working code that could be cut/paste/executed in an empty sandbox database to setup a working environment.  Two, expected results based on the sample data provided.

  With that, here is my shot at your problem.
  
create table [dbo].[table1]
(
  [id] int
, [region] int
, [typeid] int
);

  insert into [dbo].[table1]
(
  [id]
, [region]
, [typeid]
)
values
(1, 2, 1)
, (2, 3, 1)
, (3, 4, 2)
, (4, 1, 2)
, (5, 1, 2)
, (6, 2, 4);

  create table [dbo].[table2]
(
  [id] int
, [type] varchar(10)
);
insert into [dbo].[table2]
(
  [id]
, [type]
)
values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D');
go

  with BaseData as (
select [t1].[id]
  , [t1].[region]
  , [t1].[typeid]
  --, [t2].[id]
  , [t2].[type]
  , [cnt] = count(*) over (partition by [t2].[type])
from
[dbo].[table1] as [t1]
inner join [dbo].[table2] as [t2]
  on [t1].[typeid] = [t2].[id]
)
select
*
from
[BaseData] as [bd]
where
[bd].[cnt] > 2;
go

  Thank you very much Lynn. 

  And my apology to everyone here replied here, the simplification does lost some important meaning here like Lynn said. As policy in place, it is unfortunate that sometimes people can not simply post their original query here, and, in my case, the original query contains too complex business logic in it which will confuse people even more.

  Anyway, my sincere thanks to everyone.

  You're welcome but I'd still like to know why the requirement to preserve the interim temporary tables exists.  It does not seem logical.

  I have no idea why I inherited such a non-logical work here, no document, original developer left, that's what I have now

  Not funny at all, isn't it?

  If that's the case, I'm thinking that the original developer didn't know what they were doing and used the temporary tables because they didn't know any better.  If you do a search in sys.sql_modules, you'll likely not find the interim tables anywhere. 

  Because you don't know and there's no one to ask, you're stuck, IMHO, with perpetuating a really bad idea.  Break the mold.  Rename the old code and use new code to get straight to the answer.  If something breaks in the next month, you'll be able to get back to the original stuff quickly.  If it doesn't break, then you've made better code that saves on a shedload of memory, disk space, and performance.

  --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)
  

• ScottPletcher

  SSC Guru

  Points: 101103

  More actions

  December 14, 2018 at 8:58 am

  #2016335

  If you wanted to always keep the original temp table, that's fine.  I don't have a problem at all with that.

  But write the q that way then. 

  Instead, you explicitly wrote that you wanted to combine the three queries. We did that.  Then  you complain that you didn't want the first query combined.  That's confusing and frustrating.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".