1

I am trying to join 2 of the following queries

select * from MapCountries where IndexName='SPX Index' and date(ModifiedDate)='2015-04-24';

select DRegion,DCountry,GICS1,GICS2,LRY,BBGLableValues from CompanyDetails where IndexName='SPX Index' and CompanyTicker='A UN' and date(ModifiedDate)='2015-04-24';

the second query is fired from the "while(reader.read())" of the first query. This is taking up a lot of CPU time. SO is there a way to join these 2 queries to reduce the CPU usage? 

while (reader.Read())
{
    var regionModelTest = new RegionModelTest();
    ExtractCompanyDetails(Index, regionModelTest);
    ...
}

ExtractCompanyDetails(Index, regionModelTest)
{
   second query;
}

the CompanyTicker for the 2nd query comes from one of the fields of the first query.

Thank you for any help.

Jenish Rabadiya
  • 6,708
  • 6
  • 33
  • 62
Jay Nirgudkar
  • 426
  • 4
  • 18
  • 1
    Can you show us the relevant information of the tables, please? How are they linked together? – Uooo Apr 24 '15 at 07:22
  • Read about WITH operator. You can make first table result as a TEMP TABLE and later use it in second select. – MajkeloDev Apr 24 '15 at 07:26
  • You have IndexName column in both tables. So, you can create a query with joining both tables. Anyway, if you want us to write the query for you, you need to provide both table structure and logic. – anbuj Apr 24 '15 at 07:27
  • Thanks guys.. @MajkeloDev is it really necessary to create a TEMP table to reduce CPU usage? – Jay Nirgudkar Apr 24 '15 at 09:10

1 Answers1

2

You could try a simple left join:

select * from MapCountries mc
left join CompanyDetails cd on mc.IndexName = cd.IndexName and mc.ModifiedDate = cd.ModifiedDate
where mc.IndexName='SPX Index' and date(mc.ModifiedDate)='2015-04-24' and cd.CompanyTicker='A UN'
Indomitable
  • 808
  • 9
  • 9