[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Optimize Query

Posted on 2005-04-28
11
Medium Priority
?
234 Views
Last Modified: 2010-03-19
I have this output

contract|Product_Type|cur_Prior_Inc|cur_Next_Inc|cur_End_Date|pre_Prior_Inc|pre_Next_Inc|pre_End_Date|Daily|BackValue|settle|seq
010204FUN1|Deposit Placed|.000000|11.562510|2004-08-31 00:00:00|.000000|.000000|2003-12-31 00:00:00|11.562510|.000000|2004-08-31 00:00:00|2
010204FUN1|Deposit Placed|.000000|.000000|2003-12-31 00:00:00|.000000|.000000|2003-12-31 00:00:00|.000000|.000000|2003-12-31 00:00:00|1
010205FUN1|Deposit Taken|.000000|-370.703491|2004-08-31 00:00:00|.000000|.000000|2003-12-31 00:00:00|-370.703491|.000000|2004-08-31 00:00:00|21

Now need to have query that would insert rows in the same table with soem conditions, but will also move some columns e.g. in the newly inserted rows move cur_prior_inc to pre prior_inc, cur_next_inc to pre_next_inc etc.

I wrote this query

insert into #rk3 (contract,product_type,cur_prior_inc,cur_next_inc,cur_end_date,settle,pre_prior_inc,pre_next_inc,pre_end_Date)

select Contract,
case when [Product Type]='DEPOSIT' then
(case when  isnull([Next Inc],0) - isnull([Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else [Product Type] end ,
isnull([Prior Inc]*1000000,0),isnull([Next Inc]*1000000,0) ,[End Date],settle
,(select distinct cur_prior_inc from #rk3 T1 where
T1.cur_end_date='4/27/2005' and T1.contract=nygicspnl.contract and nygicspnl.contract is not null ),
(select distinct cur_next_inc from #rk3 T1 where
T1.cur_end_date='4/27/2005' and T1.contract=nygicspnl.contract and nygicspnl.contract is not null ),
(select distinct cur_end_date from #rk3 T1 where
T1.cur_end_date='4/27/2005' and T1.contract=nygicspnl.contract and nygicspnl.contract is not null )
from NYGICSPNL
where [end date] = '4/27/2005' order by 2,1

It works, but takes very long time, and my front end apps failing with timeouts
See how I take values of pre_prior_inc,pre_next_inc,pre_end_Date and thats cauing trouble

(select distinct cur_prior_inc from #rk3 T1 where
T1.cur_end_date='4/26/2005' and T1.contract=nygicspnl.contract and nygicspnl.contract is not null ),
(select distinct cur_next_inc from #rk3 T1 where
T1.cur_end_date='4/26/2005' and T1.contract=nygicspnl.contract and nygicspnl.contract is not null ),
(select distinct cur_end_date from #rk3 T1 where
T1.cur_end_date='4/26/2005' and T1.contract=nygicspnl.contract and nygicspnl.contract is not null )

Is there a way I can optimize it ?
0
Comment
Question by:jitganguly
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13886282
Could you give the following a try ?

select A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle,
max(T1.cur_prior_inc), max(cur_next_inc), max(cur_end_date)
from NYGICSPNL A
left outer join #rk3 T1 ON T1.contract=A.contract and T1.cur_end_date=A.[end date]
where A.[end date] = '4/27/2005' and A.contract is not null
group by A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle
order by 2,1
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13886406
if above fits your requirements, this version does roughly the same but should be faster

select A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle,
cur_prior_inc, cur_next_inc, cur_end_date
from NYGICSPNL A
left outer join (
      select contract, max(cur_prior_inc) as cur_prio_inc,
                       max(cur_next_inc) as cur_next_inc, max(cur_end_date) as cur_end_date
      from #rk3  Where cur_end_date='4/27/2005'
      group by contract
) B on A.Contract = B.Contract
where A.[end date] = '4/27/2005'
order by 2,1
0
 
LVL 20

Author Comment

by:jitganguly
ID: 13886605
Getting Invalid colum name in cur_prior_inc, cur_next_inc, cur_end_date

These columns come from #rk3 and not nygicspnl and how do I join them ? Meanign its goign to be same
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 20

Author Comment

by:jitganguly
ID: 13886646
Nope. I am getting NULL in cur_prior_inc, cur_next_inc, cur_end_date
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13886690
please try

select A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle,
B.cur_prior_inc, B.cur_next_inc, B.cur_end_date
from NYGICSPNL A
left outer join (
      select contract, max(cur_prior_inc) as cur_prio_inc,
      max(cur_next_inc) as cur_next_inc, max(cur_end_date) as cur_end_date
      from #rk3  Where cur_end_date='4/27/2005'
      group by contract
) B on A.Contract = B.Contract
where A.[end date] = '4/27/2005'
order by 2,1


0
 
LVL 20

Author Comment

by:jitganguly
ID: 13886980
That works but still 22 seconds and that could casue me a timeout on my front end app. Any other ways to optimize ?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13887081
>>still 22 seconds <<
is it better than your initial version ?

>>Any other ways to optimize ?<<
Difficult to say whithout knowing the data model

Is 22 seconds just for this part, or do the 22 seconds include the #temp table filling and/or other processes ?

An index on NYGICSPNL ([end date]) would help, most likely
If you don't have one the query might perform a full scan on the whole table ...

Can you give more details on the surrounding code ?
0
 
LVL 20

Author Comment

by:jitganguly
ID: 13887214
22 seocnds is defintely better. But it could go upto much more as the # of rows goes up. I already have e index on [end date]. I have a SP that calculates something for the entire year for each contract and each date and users gets timed out and planning to have a physical table to sore historical data and only process todays data.
Here is this SP

--drop PROCEDURE up_Summary_Pnl_ravi
--exec up_Summary_Pnl_ravi '12/10/2004'

CREATE PROCEDURE up_Summary_Pnl_ravi
@currentdate varchar(10)
AS

if object_id('tempdb..#contract', 'u') is not null drop table #contract
if object_id('tempdb..#rk3', 'u') is not null       drop table #rk3

declare @contract varchar(30)

create table #rk3
   (      contract varchar(50) null,
      Product_Type varchar(25),

      cur_Prior_Inc numeric(20,6)  ,
          cur_Next_Inc numeric(20,6),
      cur_End_Date smalldatetime,

      pre_Prior_Inc numeric(20,6)  ,
      pre_Next_Inc numeric(20,6),
      pre_End_Date smalldatetime,

      Daily numeric(20,6)  ,
          BackValue numeric(20,6),
      settle smalldatetime,
      seq int )

select distinct Contract
into #contract
from NYGICSPNL
where contract is not null


while ( select count(*) from #contract) > 0
begin

set rowcount 1
select @contract = contract from #contract

set rowcount 0

delete #contract where contract = @contract


if object_id('tempdb..#rk', 'u') is not null       drop table #rk

if object_id('tempdb..#rk2', 'u') is not null       drop table #rk2

create table #rk
   (      contract varchar(50) null,
      Product_Type varchar(25),

      cur_Prior_Inc numeric(20,6)  ,
          cur_Next_Inc numeric(20,6),
      cur_End_Date smalldatetime,
      settle smalldatetime,
      seq int identity )

      insert into #rk
      select Contract,
      case when [Product Type]='DEPOSIT' then
      (case when  isnull([Next Inc],0) - isnull([Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
      else [Product Type] end ,
      isnull([Prior Inc]*1000000,0),isnull([Next Inc]*1000000,0) ,[End Date],settle
      from NYGICSPNL
      where contract = @contract
      order by 1,5

      select       contract,
            product_type,
            cur_prior_inc,
            cur_next_inc,
            cur_end_date,
            settle,
            seq +1 seq
      into #rk2
      from #rk


insert into #rk3
select       a.contract,
      a.Product_Type,
      a.cur_Prior_Inc,
          a.cur_Next_Inc,
      a.cur_End_Date,
      0,
          a.cur_Prior_Inc,
      a.cur_End_Date,
      0,
      0,
      a.settle,
      a.seq
from #rk a
where seq = 1
      
insert into #rk3
select       a.contract,
      a.Product_Type,
      a.cur_Prior_Inc,
          a.cur_Next_Inc,
      a.cur_End_Date,
      b.cur_Prior_Inc,
          b.cur_Next_Inc,
      b.cur_End_Date,
      0,
      0,
      a.settle,
      a.seq
from #rk a, #rk2 b
where a.seq = b.seq

end

update #rk3
set       Daily = cur_Next_Inc - cur_Prior_Inc,
          BackValue = cur_Prior_Inc - pre_Next_Inc


if object_id('tempdb..#temp100', 'u') is not null
      drop table #temp100

create table #temp100
   (myProduct varchar(50) not null,Daily numeric(20,6) ,Monthly numeric(20,6),Yearly numeric(20,6),Inception numeric(20,6))

insert into #temp100
select distinct product_type ,0,0,0,0
from #rk3

update #temp100
set Daily= s.Daily
from (
select product_type ,
sum(daily + backvalue ) as Daily from #rk3
where cur_end_date= @currentdate
group by product_type ) s
where s.Product_type=#temp100.myproduct

update #temp100
set Monthly = s.Monthly
from (
select product_type ,
sum(daily + backvalue ) as Monthly from #rk3
where cur_end_date >=(select DATEADD(mm, DATEDIFF(mm,0,@currentdate), 0)) and cur_end_date<=@currentdate
group by product_type ) s
where s.Product_type=#temp100.myproduct

update #temp100
set Yearly = s.Yearly
from (
select
product_type ,sum(daily + backvalue ) as Yearly from #rk3
where cur_end_date >=(select DATEADD(yy, DATEDIFF(yy,0,@currentdate), 0)) and cur_end_date<=@currentdate
group by product_type ) s
where s.Product_type=#temp100.myproduct

update #temp100
set Inception = s.Inception
from (
select
product_type ,
sum(daily + backvalue )  as Inception from #rk3            --Inception
where cur_End_Date>=settle and cur_End_Date <=@currentdate
group by product_type) s
where s.Product_type=#temp100.myproduct


select *,@currentdate as currentdate from #temp100 order by 1

GO
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13887414
This one should be faster, but can definitely be optimized further
Can you give it a try

CREATE PROCEDURE up_Summary_Pnl_ravi
@currentdate varchar(10)
AS

if object_id('tempdb..#contract', 'u') is not null drop table #contract
if object_id('tempdb..#rk3', 'u') is not null      drop table #rk3

declare @contract varchar(30)

create table #rk3
   (     contract varchar(50) null,
     Product_Type varchar(25),

     cur_Prior_Inc numeric(20,6)  ,
         cur_Next_Inc numeric(20,6),
     cur_End_Date smalldatetime,

     pre_Prior_Inc numeric(20,6)  ,
     pre_Next_Inc numeric(20,6),
     pre_End_Date smalldatetime,

     Daily numeric(20,6)  ,
         BackValue numeric(20,6),
     settle smalldatetime,
     seq int )

select distinct Contract
into #contract
from NYGICSPNL
where contract is not null


while ( select count(*) from #contract) > 0
begin

set rowcount 1
select @contract = contract from #contract

set rowcount 0

delete #contract where contract = @contract


if object_id('tempdb..#rk', 'u') is not null      drop table #rk

if object_id('tempdb..#rk2', 'u') is not null      drop table #rk2

create table #rk
   (     contract varchar(50) null,
     Product_Type varchar(25),

     cur_Prior_Inc numeric(20,6)  ,
         cur_Next_Inc numeric(20,6),
     cur_End_Date smalldatetime,
     settle smalldatetime,
     seq int identity )

     insert into #rk
     select Contract,
     case when [Product Type]='DEPOSIT' then
     (case when  isnull([Next Inc],0) - isnull([Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
     else [Product Type] end ,
     isnull([Prior Inc]*1000000,0),isnull([Next Inc]*1000000,0) ,[End Date],settle
     from NYGICSPNL
     where contract = @contract
     order by 1,5

     select      contract,
          product_type,
          cur_prior_inc,
          cur_next_inc,
          cur_end_date,
          settle,
          seq +1 seq
     into #rk2
     from #rk


insert into #rk3
select      a.contract,
     a.Product_Type,
     a.cur_Prior_Inc,
         a.cur_Next_Inc,
     a.cur_End_Date,
     0,
         a.cur_Prior_Inc,
     a.cur_End_Date,
     0,
     0,
     a.settle,
     a.seq
from #rk a
where seq = 1
     
insert into #rk3
select      a.contract,
     a.Product_Type,
     a.cur_Prior_Inc,
         a.cur_Next_Inc,
     a.cur_End_Date,
     b.cur_Prior_Inc,
         b.cur_Next_Inc,
     b.cur_End_Date,
     0,
     0,
     a.settle,
     a.seq
from #rk a, #rk2 b
where a.seq = b.seq

end

update #rk3
set      Daily = cur_Next_Inc - cur_Prior_Inc,
         BackValue = cur_Prior_Inc - pre_Next_Inc

select product_type as myproduct,
sum(case when cur_end_date = @currentdate then daily + backvalue end) as Daily,
sum(case when cur_end_date between convert(varchar(8), @currentdate, 102)+'01' and @currentdate then daily + backvalue end) as Monthly,
sum(case when cur_end_date between datename(year,@currentdate)+ '.01.01' and @currentdate then daily + backvalue end) as Yearly,
sum(case when cur_end_date between Settle and @currentdate then daily + backvalue end) as Inception,
@currentdate as currentdate
from #rk3
group by product_type
order by 1

GO

0
 
LVL 20

Author Comment

by:jitganguly
ID: 13887556
Syntax error converting character string to smalldatetime data type.

Probably here

select product_type as myproduct,
sum(case when cur_end_date = @currentdate then daily + backvalue end) as Daily,
sum(case when cur_end_date between convert(varchar(8), @currentdate, 102)+'01' and @currentdate then daily + backvalue end) as Monthly,
sum(case when cur_end_date between datename(year,@currentdate)+ '.01.01' and @currentdate then daily + backvalue end) as Yearly,
sum(case when cur_end_date between Settle and @currentdate then daily + backvalue end) as Inception,
@currentdate as currentdate
from #rk3
group by product_type
order by 1
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 13887644
yep
should be

select product_type as myproduct,
sum(case when cur_end_date = @currentdate then daily + backvalue end) as Daily,
sum(case when cur_end_date between convert(varchar(8), cast(@currentdate as datetime), 102)+'01' and @currentdate then daily + backvalue end) as Monthly,
sum(case when cur_end_date between datename(year,@currentdate)+ '.01.01' and @currentdate then daily + backvalue end) as Yearly,
sum(case when cur_end_date between Settle and @currentdate then daily + backvalue end) as Inception,
@currentdate as currentdate
from #rk3
group by product_type
order by 1
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question