Home How to optimize the SQL query with several select queries?
Reply: 0

How to optimize the SQL query with several select queries?

user5662
1#
user5662 Published in May 22, 2018, 11:31 am

How can I make the code more optimal? Now, with 600,000 rows, it downloads data within 2 minutes. I will add that I am a beginner in sql optimization. It seems to me that I should download data using select in a different way. If anyone knows a way, I would ask for a brief example. Thank you in advance and best regards.

SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY itr.interest_id), 0) AS UniqueId,
       DATEPART(YEAR, itr.[date]) as interestDateYear,
       DATEPART(MONTH, itr.[date]) as interestDateMonth,
       ftl.funding_type_list_Id as LineNumber,
       cd.contract_number as ContractNumber,
       dd.regon as Regon,
       dd.NIP as Nip,
       dd.number_LDAP as LDAP,
       cd.dealers_from_importer_code as DealerCode,
       dd.full_name as FullName,
       cd.termination_date as TerminationDate,
       sz.zone_no as ZoneNo,
       sz.zone_admin as ZoneAdmin,
       cd.related_to_importer as RelatedToImporter,
       ftl.funding_type_name as FundingTypeName,
       --Kwota limitu MIESIĄC ROK 
       isnull((select limit_granted_money
               from limit_info li
               where li.owner_name = 'funding_type_list'
                 and li.owner_id = ftl.funding_type_list_Id
                 and li.insert_dt = (select max(li.insert_dt)
                                     from limit_info li
                                     where li.owner_name = 'funding_type_list'
                                       and li.owner_id = ftl.funding_type_list_Id
                                       and MONTH(li.insert_dt) = MONTH(itr.[date])
                                       and YEAR(li.insert_dt) = YEAR(itr.[date]))), 0) as LimitAmountForPeriod,
       --Bieżąca kwota limitu – kwota limitu przypadająca na dzień generowania raportu
       isnull((select limit_granted_money
               from limit_info li
               where li.owner_name = 'funding_type_list'
               and li.owner_id = ftl.funding_type_list_Id
               and li.insert_dt = (select max(li.insert_dt)
                                   from limit_info li
                                   where li.owner_name = 'funding_type_list'
                                     and li.owner_id = ftl.funding_type_list_Id)), 0) as CurrentLimit,
       --Prowizja przy odnowieniu – do wyjasnienia
       --DO WYJASNIENIA
       --Indeks użyty przy kalkulacji odsetek – rodzaj WIBOR 3M lub 1M 
       rr.code as IndexForCalculationOfInterest,
       --wartość indeksu na MIESIĄC ROK
       isnull((select int_rate
               from reference_rate rr
               where cd.wibor = rr.reference_rate_id
                 and MONTH(itr.[date]) = MONTH(rr.value_date)
                 and YEAR(itr.[date]) = Year(rr.value_date)), 0) as IndexValue,
       --Obrót za kredyty MIESIĄC ROK
       isnull((select SUM(amount)
               from sales sa
               where sa.contract_data_id = cd.contract_data_id
                 and MONTH(sa.sales_date) = MONTH(itr.[date])
                 and YEAR(sa.sales_date) = YEAR(itr.[date])), 0) as SalesForLoans,
       --Dynamiczne marże i obroty
       --Naliczona marża MIESIĄC ROK DZIALA
       ISNULL((select threshold_value
               from margin_threshold_def mtd
               where mtd.margin_def_id = isnull((select margin_def_id
                                                 from margin_def md
                                                 where md.margin_def_id = itr.margin_def_id
                                                   and MONTH(md.begin_date) >= MONTH(itr.[date])
                                                   and YEAR(md.begin_date) >= YEAR(itr.[date])
                                                   and MONTH(CASE WHEN md.end_date IS NULL THEN GETDATE() ELSE md.end_date END) <= MONTH(itr.[date])
                                                   and YEAR(CASE WHEN md.end_date IS NULL THEN GETDATE() ELSE md.end_date END) <= YEAR(itr.[date])), 0)), 0) as Margin
       --Naliczone odsetki MIESIĄC ROK
from interest itr
     join contract_data cd on itr.contract_data_id = cd.contract_data_id
     left join funding_type_list ftl on cd.contract_data_id = ftl.id_contract
     left join sales sa on cd.contract_data_id = sa.contract_data_id
     left join dealer_data dd on cd.dealer_id = dd.dealer_data_id
     left join dealer_zones dz on cd.dealer_id = dz.dealer_id
     left join scm_zone sz on dz.scm_zone_id = sz.zone_id  
     left join tranche_startup ts on cd.contract_data_id = ts.contract_id
     left join margin_def md on itr.margin_def_id = md.margin_def_id
     left join margin_threshold_def mtd on md.margin_def_id = mtd.margin_def_id 
     left join reference_rate rr on cd.wibor = rr.reference_rate_id
     left join limit_info li on li.owner_name = 'funding_type_list' and li.owner_id = cd.contract_data_id
group by DATEPART(YEAR, itr.[date]),
         DATEPART(MONTH, itr.[date]),
         itr.interest_id,
         itr.margin_def_id,
         ftl.funding_type_list_Id,
         cd.contract_number,
         cd.contract_data_id,
         cd.wibor,
         ts.contract_id,
         ts.tranche_amount,
         dd.regon,
         dd.NIP, 
         dd.number_LDAP,
         cd.dealers_from_importer_code,
         dd.full_name,
         cd.related_to_importer,
         ftl.funding_type_name,
         cd.termination_date,
         sz.zone_no,
         sz.zone_admin, 
         cd.available_limit,
         itr.amount,
         mtd.threshold_value,
         rr.code,
         rr.int_rate,
         sa.penetration,
         ts.tranche_startup_id,
         li.insert_dt;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.335559 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO