Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: Any clue to make this query run faster

    Any help would be really appreciated....
    My stored procedure...

    Code:
    
    CREATE PROCEDURE business3rd7
    @Fromdate DATETIME,
    @ToDate DATETIME
    AS
    
    
    
    select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVERT(VARCHAR(10),Receipts.Companynumber2) as co ,
    Receipts.Premium1+Receipts.Premium2 as Premium,
    "CAN"=case when Receipts.transactiontype='CAN' 
    then  (receipts.premium1+receipts.premium2) 
    else 0
    end,
    "NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN' 
    then  (receipts.premium1+receipts.premium2) 
    else 0
    end,
    
    "#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else 
    0 end,
    
    -------
    "$NEW"=
    case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2) 
    else 0 end,
    "#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else 
    0 end,
    "$RENEW"=
    case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2) 
    else 0 end,
    "#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0 
    end,
    "$AP"=
    case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else 
    0 end,
    "#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(policy.policynumber) end,
    
    "%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0 
    end  =0 then 0 
    when case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(policy.policynumber) else 0 end=0 then 0
    else
    case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0 
    end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(policy.policynumber) else 0 end end,
    
    
    "Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(clubamount) end,
    "Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate) 
    AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
    "#AA"=count(receipts.clubamount),
    "$AA"=sum(receipts.clubamount)
    
    FROM Receipts,Policy 
    where Receipts.Agencyid=Policy.Agentid
    group by 
    Receipts.CompanyNumber1,Receipts.CompanyNumber2,
    Receipts.Premium1,Receipts.Premium2,
    Receipts.TransactionType,policy.Renewalofferdate,
    Receipts.Agencyid
    
    
    
    GO
    Query plan...
    Code:
    ----------------------Query Plan
    
      |--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
           |--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
                |--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
                     |--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
                          |--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
                               |--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
                                    |--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
                                    |--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))
    \

    The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
    Any way to make it faster...
    Last edited by rudra; 10-03-06 at 15:46.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well distinct sucks.

    you have 2 table scans. I always use the graphical execution plan, but if these are big query costs, you may want to index the columns involved.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Thrasymachus
    well distinct sucks.

    you have 2 table scans. I always use the graphical execution plan, but if these are big query costs, you may want to index the columns involved.
    Thank you Thrasymachus.My guess was right...yea one of the culprit is that two table scan let me create some index on those columns and I have to think over that distinct again.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - I didn't even know you could use distinct and group by in a single query. In any case - this makes distinct doubly unnecessary.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Yea,thats my mistake ..I overlooked that...well after creating the index on the columns not a significant change has resulted.
    ....

    Any clue...
    Last edited by rudra; 10-03-06 at 17:26.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A where clause?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by pootle flump
    A where clause?
    I didnt get you,I can't use where clause with @fromdate and @todate ,bcoz I am doing the calculation of previous year also.
    well, I think I have to consider the whole query in a different approach...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    where did you create your indices?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I mean that it is usually pretty rare to need every record from one table and all related records from another. If you need them all then you need them all but point was - do you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by pootle flump
    I mean that it is usually pretty rare to need every record from one table and all related records from another. If you need them all then you need them all but point was - do you?
    No,I don't need all records of one table only those records which satisfy the datefrom clause, and then I want some records of other table which is related to this table by agentid.
    Clear?
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Thrasymachus
    where did you create your indices?
    I have created the clustered and non clustered index on the columns on both tables which are invloved in this query.ok?
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    ummm, are they on the columns causing your scans? are your scans a large percentage of your query cost? how big are the tables involved?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Thrasymachus
    ummm, are they on the columns causing your scans? are your scans a large percentage of your query cost? how big are the tables involved?
    well, I checked my estiimated execution plan and found that the sort is taking the 96% cost...I mean the grouping cost 96%.
    Any clue?
    The tables are having data of 13349 and 97032 respectively,its a testing databse so few data are there...
    Last edited by rudra; 10-04-06 at 18:19.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I've taken some liberties trying to get to what I think you want. Some columns are just plain missing because they made no sense to me. The result set is quite differently arranged in that what was once one row has become many rows based on transaction type. This isn't an "apples to apples" comparison, but I think it does what I think you really want, and will actually be easier for you to use!
    Code:
    SELECT
       Policy.AgentID
    ,  Receipts.CompanyNumber1
    ,  Receipts.CompanyNumber2
    ,  Receipts.transactiontype
    ,  Count(*) AS c
    ,  Sum(Receipts.premium1 + Receipts.premium2) AS dollars
    ,  Count(Receipts.clubamount) AS "AA#"
    ,  Sum(Receipts.clubamount) AS "AA$"
       FROM Receipts
       JOIN Policy
          ON (policy.agentID = Receipts.AgentID)
       WHERE  policy.renewalofferdate BETWEEN @Fromdate AND @ToDate
       GROUP BY Policy.AgentID
    ,     Receipts.Company1
    ,     Receipts.Company2
    ,     Receipts.TransactionType
    -PatP

  15. #15
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    This query still not working ....
    I am trying all things but for some strange reason its not working...
    SEE the modified code which contains the crosstab operations....

    The two tables are Policy and Receipts tables....

    Code:
    CREATE TABLE [Policy] (
    	
    	[PolicyNumber] [nvarchar] (10) ,
    	[PolicySequence] [smallint] NULL ,
    	[Name] [nvarchar] (40)  ,
    
    	 ------there are many columns here and I am  are not mentioning those 
    
    	[AgentID] [int] NULL ,
    	[RenewalOfferDate] [smalldatetime] NULL
    	
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    
    
    CREATE TABLE [Receipts] (
    	[RecordType] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[AgencyID] [int] NULL ,
    	[ContractNumber] [bigint] NULL ,
    	[MonthlyPayment] [money] NULL ,
    	[PaymentType] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PolicyNumber] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PolicySequence] [smallint] NOT NULL ,
    	[ReceiptNumber] [bigint] NULL ,
    	[TransactionType] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[InsuredName] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[DownPayment] [money] NULL ,
    	[ClubAmount] [money] NULL ,
    	[ServiceFee] [money] NULL ,
    	[MVRFee] [money] NULL ,
    	[SR22Fee] [money] NULL ,
    	[AgentInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ProcessedInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[QuotedInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Source] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[CompanyNumber1] [int] NULL ,
    	[PolicyNumber1] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Premium1] [money] NULL ,
    	[CompanyNumber2] [int] NULL ,
    	[PolicyNumber2] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Premium2] [money] NULL ,
    	[TotalAmount] [money] NULL ,
    	[HowPaid] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[HowPaidCode] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[AmountTendered] [money] NULL ,
    	[Change] [money] NULL ,
    	[DateEntered] [datetime] NULL ,
    	[Void] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[VoidDate] [datetime] NULL ,
    	[VoidBy] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[RequestVoidBy] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ReasonVoid] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]


    Here is the code which ran really fast...

    Code:
    @Fromdate DATETIME,
    @ToDate DATETIME
    set @fromdate='1/1/2006'
    set @todate='12/1/2006'
    
    
    SELECT
       Policy.AgentID
    ,  Receipts.CompanyNumber1
    ,  Receipts.CompanyNumber2
    ,  Receipts.transactiontype
    ,  Count(*) AS c
    ,  Sum(Receipts.premium1 + Receipts.premium2) AS dollars
    ,  Count(Receipts.clubamount) AS "AA#"
    ,  Sum(Receipts.clubamount) AS "AA$"
    
     FROM Receipts
       JOIN Policy
          ON (Policy.Agentid=Receipts.Agencyid)
       WHERE  policy.renewalofferdate BETWEEN @Fromdate AND @ToDate
       GROUP BY Policy.AgentID
    ,     Receipts.CompanyNumber1
    ,     Receipts.CompanyNumber2
    ,     Receipts.TransactionType
    This is the modified code which is very slow one...

    Code:
    declare
    @Fromdate DATETIME,
    @ToDate DATETIME
    set @fromdate='1/1/2006'
    set @todate='12/1/2006'
    
    
    SELECT
       Policy.AgentID
    ,  Receipts.CompanyNumber1
    ,  Receipts.CompanyNumber2
    ,  Receipts.transactiontype
    ,  Count(*) AS c
    ,  Sum(Receipts.premium1 + Receipts.premium2) AS dollars
    ,  Count(Receipts.clubamount) AS "AA#"
    ,  Sum(Receipts.clubamount) AS "AA$",
    
    -------------- These calculations are needed here
    
    "CAN"=case when Receipts.transactiontype='CAN' 
    then  (receipts.premium1+receipts.premium2) 
    else 0
    end,
    "NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN' 
    then  (receipts.premium1+receipts.premium2) 
    else 0
    end,
    
    "#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else 
    0 end,
    
    -------
    "$NEW"=
    case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2) 
    else 0 end,
    "#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else 
    0 end,
    "$RENEW"=
    case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2) 
    else 0 end,
    "#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0 
    end,
    "$AP"=
    case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else 
    0 end,
    "#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(policy.policynumber) end,
    
    "%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0 
    end  =0 then 0 
    when case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(policy.policynumber) else 0 end=0 then 0
    else
    case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0 
    end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(policy.policynumber) else 0 end end,
    
    
    "Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then 
    count(clubamount) end,
    "Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate) 
    AND DateAdd(year, -1, @ToDate) then count(clubamount) end
    
    ------------
    
    
       FROM Receipts
       JOIN Policy
          ON (Policy.Agentid=Receipts.Agencyid)
       WHERE  policy.renewalofferdate BETWEEN @Fromdate AND @ToDate
       GROUP BY Policy.AgentID
    ,     Receipts.CompanyNumber1
    ,     Receipts.CompanyNumber2
    ,     Receipts.TransactionType
    
    -------- three(3) more group by added 
    
    ,     Receipts.Premium1
    ,     Receipts.Premium2
    ,     Policy.Renewalofferdate
    I have created neccessary Indices on the columns which are involed and are grouped.
    I have created an index comprises of all the columns involed in the group except Policy.Renewalofferdate.


    Any clue?
    Last edited by rudra; 10-07-06 at 00:42.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •