Just fetches one record in 10 lakhs

  • Hi ,

    When i run this query i am getting only one record .The one with the comented policy id DLKB6663 alone comes here . I have almost 1o lakhs of records . How to make all the records to be displayed by satisfying the below conditons . As outer apply is causing a performace issue i tried this but not able to find the root cause why it fetches one record only . Could you please help how to get all the records to be displayed . Your help is highly appreciated .

    SELECT DISTINCT

    dbo.Systems.SiteNumber,

    dbo.PolicyAudit.ID,

    dbo.PolicyAudit.PolicyID,

    CONVERT(VARCHAR(8), dbo.PolicyAudit.LastModifiedDate, 112),

    dbo.PolicyAudit.CoverStartDate,

    dbo.PolicyAudit.EmployerName,

    dbo.PolicyAudit.Branch,

    dbo.PolicyAudit.Workgroup,

    dbo.PolicyAudit.ProductCode,

    dbo.PolicyAudit.Lob,

    DATEDIFF(day,dbo.PolicyAudit.CoverStartDate,GETDATE()),

    dbo.PolicyAudit.ERNExemptFlag

    FROM

    dbo.Systems RIGHT OUTER JOIN dbo.PolicyAudit ON (dbo.PolicyAudit.SourceSystemID=dbo.Systems.ID)

    INNER JOIN (

    SELECT Top 1 pa.HasERN as HasERN ,

    pa.id as id,

    pa.PolicyID as PolicyID ,

    pa.LastModifiedDate as DateSent ,

    ERNExemptFlag as ERNExemptFlag

    FROM PolicyAudit pa

    where pa.Status=1

    Order By pa.ID desc

    ) Derived_Table3 ON (Derived_Table3.id=dbo.PolicyAudit.ID and Derived_Table3.PolicyID=dbo.PolicyAudit.PolicyID and Derived_Table3.DateSent=dbo.PolicyAudit.LastModifiedDate)

    WHERE

    (

    dbo.PolicyAudit.Status = 1

    AND

    dbo.PolicyAudit.HasERN = 0

    AND

    CASE WHEN dbo.Systems.FileTypeIndicator= 'C' THEN 'Current' WHEN dbo.Systems.FileTypeIndicator= 'H' THEN 'Historic' WHEN dbo.Systems.FileTypeIndicator= 'V' THEN 'Voidance' ELSE '' END IN ( 'Current','Voidance' )

    AND

    Derived_Table3.HasERN IN ( 0 )

    --AND

    --dbo.PolicyAudit.PolicyID='DLKB6663'

    )

  • Try this alternative to your query. The significant change is removing the right join. Most folks avoid them - a mix of right joins and left joins in the same query is a real head-scratcher. Settle for left joins and adjust your query to accommodate the change.

    SELECT --DISTINCT

    s.SiteNumber,

    p.ID,

    p.PolicyID,

    CONVERT(VARCHAR(8), p.LastModifiedDate, 112),

    p.CoverStartDate,

    p.EmployerName,

    p.Branch,

    p.Workgroup,

    p.ProductCode,

    p.Lob,

    DATEDIFF(day,p.CoverStartDate,GETDATE()),

    p.ERNExemptFlag

    FROM dbo.PolicyAudit p

    LEFT JOIN dbo.Systems s

    ON p.SourceSystemID = s.ID

    INNER JOIN (

    SELECT Top 1

    pa.HasERN as HasERN ,

    pa.id as id,

    pa.PolicyID as PolicyID ,

    pa.LastModifiedDate as DateSent ,

    ERNExemptFlag as ERNExemptFlag

    FROM PolicyAudit pa

    where pa.Status = 1

    Order By pa.ID desc

    ) d

    ON d.id = p.ID

    and d.PolicyID = p.PolicyID

    and d.DateSent = p.LastModifiedDate

    WHERE p.Status = 1

    AND p.HasERN = 0

    AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'

    AND d.HasERN = 0

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks for your info . When i just run the query i am still getting one record alone . Why i am not getting all the policy records in the table . I really dont know why it happens ? Is any key work i need to input to fetch all the poicy records.

  • mbavabohrude (4/26/2016)


    Thanks for your info . When i just run the query i am still getting one record alone . Why i am not getting all the policy records in the table . I really dont know why it happens ? Is any key work i need to input to fetch all the poicy records.

    There are several filters in the WHERE clause of your query. One of them is this

    AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'

    move it from the WHERE clause into the join for the System table, like this:

    LEFT JOIN dbo.Systems s

    ON p.SourceSystemID = s.ID

    AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'

    and try again

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • To add with it is taking the inner query output (policy id) and returing the one row only . I really dont understand why the other policyid is coming into picture .Could you please make me to understand better .

  • I assume that the ID columns in PolicyAudit and Systems are unique? If so, you're only going to get one row because you're joining on them to the single-row result set of a subquery. Incidentally, the third line of your WHERE clause turns your LEFT JOIN into an INNER JOIN.

    John

  • Comment out the derived table, then selectively remove and restore the filters from the WHERE clause. Observe what happens. How many rows are in table dbo.PolicyAudit with p.Status = 1 and p.HasERN = 0?

    SELECT --DISTINCT

    s.SiteNumber,

    p.ID,

    p.PolicyID,

    CONVERT(VARCHAR(8), p.LastModifiedDate, 112),

    p.CoverStartDate,

    p.EmployerName,

    p.Branch,

    p.Workgroup,

    p.ProductCode,

    p.Lob,

    DATEDIFF(day,p.CoverStartDate,GETDATE()),

    p.ERNExemptFlag

    FROM dbo.PolicyAudit p

    LEFT JOIN dbo.Systems s

    ON p.SourceSystemID = s.ID

    AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'

    /*

    INNER JOIN (

    SELECT Top 1

    --pa.HasERN as HasERN ,

    pa.id as id,

    pa.PolicyID as PolicyID ,

    pa.LastModifiedDate as DateSent--,

    -- ERNExemptFlag as ERNExemptFlag

    FROM PolicyAudit pa

    where pa.Status = 1

    AND d.HasERN = 0

    Order By pa.ID desc

    ) d

    ON d.id = p.ID

    and d.PolicyID = p.PolicyID

    and d.DateSent = p.LastModifiedDate

    */

    WHERE p.Status = 1

    AND p.HasERN = 0

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I just commented and ran it fetches around 1074733 in 9 mins .When i run the inner query it fetches one row

    when i run the inner join seperately

    INNER JOIN (

    SELECT Top 1

    pa.HasERN as HasERN ,

    pa.id as id,

    pa.PolicyID as PolicyID ,

    pa.LastModifiedDate as DateSent ,

    ERNExemptFlag as ERNExemptFlag

    FROM PolicyAudit pa

    where pa.Status = 1

    Order By pa.ID desc

    ) d

    it fetches one record and this record details alone is coming and i am totally confused .

  • mbavabohrude (4/26/2016)


    I just commented and ran it fetches around 1074733 in 9 mins .When i run the inner query it fetches one row

    when i run the inner join seperately

    INNER JOIN (

    SELECT Top 1

    pa.HasERN as HasERN ,

    pa.id as id,

    pa.PolicyID as PolicyID ,

    pa.LastModifiedDate as DateSent ,

    ERNExemptFlag as ERNExemptFlag

    FROM PolicyAudit pa

    where pa.Status = 1

    Order By pa.ID desc

    ) d

    it fetches one record and this record details alone is coming and i am totally confused .

    It looks like you're trying to get the "first policy row", but do you want that by ID or by DateSent? It's not much of a change to get this to work.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • By id i am testing the latest record and testing the condition . It is actually a two procedure that needs to be combine to get the single query output and find below the details

    PROCEDURE [dbo].[spERNExceptionReport]

    @Month int, -- Should be a value from 1 to 12 (Not being used now)

    @Year int -- Should be 4 digit year number (Not being used now)

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT Distinct Systems.SiteNumber AS 'SiteNumber',

    PolicyAudit.PolicyID AS 'PolicyID',

    PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',

    CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'

    FROM Systems

    LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID

    WHERE --MONTH(PolicyAudit.LastModifiedDate) = @Month AND

    --YEAR(PolicyAudit.LastModifiedDate) = @Year AND

    PolicyAudit.Status = 1 AND

    PolicyAudit.HasERN = 0 AND

    Systems.FileTypeIndicator in ('C','V')

    END

    We iterate through all the records which we receive from above query. Then for each policy, we fetch “HasERN” field as per following procedure logic

    PROCEDURE [dbo].[spHelperProcedureERNExceptionReport]

    @PolicyID varchar (30)

    AS

    BEGIN

    SELECT Top(1) [HasERN],

    PolicyAudit.PolicyID AS 'PolicyID',

    CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent',

    Systems.SiteNumber AS 'SiteNumber',

    PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag'

    FROM PolicyAudit

    Left Join [Systems]

    on PolicyAudit.SourceSystemID = [Systems].ID

    WHERE rtrim(ltrim(PolicyID)) = @PolicyID

    And Systems.FileTypeIndicator in ('C','V')

    And [Status] = '1'

    Order By PolicyAudit.ID desc

    END

    Hope i am clear in explaining the details and kindly let me know for any clarifications

  • Have you tried this?

    SELECT Distinct Systems.SiteNumber AS 'SiteNumber',

    PolicyAudit.PolicyID AS 'PolicyID',

    PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',

    CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'

    FROM Systems

    LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID

    CROSS APPLY (

    SELECT Top(1) [HasERN],

    PolicyAudit.PolicyID AS 'PolicyID',

    CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent',

    Systems.SiteNumber AS 'SiteNumber',

    PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag'

    FROM PolicyAudit

    Left Join [Systems]

    on PolicyAudit.SourceSystemID = [Systems].ID

    WHERE rtrim(ltrim(PolicyID)) = PolicyAudit.PolicyID

    And Systems.FileTypeIndicator in ('C','V')

    And [Status] = '1'

    Order By PolicyAudit.ID desc

    ) x

    WHERE --MONTH(PolicyAudit.LastModifiedDate) = @Month AND

    --YEAR(PolicyAudit.LastModifiedDate) = @Year AND

    PolicyAudit.Status = 1 AND

    PolicyAudit.HasERN = 0 AND

    Systems.FileTypeIndicator in ('C','V')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • If CROSS APPLY option is too slow (as I understood from the previous post) you could try this one:

    SELECT DISTINCT

    S.SiteNumber,

    PA.ID,

    PA.PolicyID,

    CONVERT(VARCHAR(8), PA.LastModifiedDate, 112),

    PA.CoverStartDate,

    PA.EmployerName,

    PA.Branch,

    PA.Workgroup,

    PA.ProductCode,

    PA.Lob,

    DATEDIFF(day,PA.CoverStartDate,GETDATE()),

    PA.ERNExemptFlag

    FROM dbo.Systems S

    RIGHT OUTER JOIN dbo.PolicyAudit PA ON PA.SourceSystemID=S.ID

    INNER JOIN (

    SELECT ROW_NUMBER() OVER (PARTITION BY ipa.ID, ipa.PolicyID, ipa.LastModifiedDate ORDER BY ipa.ID DESC) AS rn,

    ipa.HasERN as HasERN ,

    ipa.id as id,

    ipa.PolicyID as PolicyID ,

    ipa.LastModifiedDate as DateSent ,

    ERNExemptFlag as ERNExemptFlag

    FROM PolicyAudit ipa

    where ipa.Status=1 ) DT3 ON DT3.id=PA.ID

    AND DT3.PolicyID=PA.PolicyID

    AND DT3.DateSent=PA.LastModifiedDate

    AND DT3.rn = 1

    WHERE PA.Status = 1

    AND PA.HasERN = 0

    AND S.FileTypeIndicator IN( 'C', 'V')

    AND DT3.HasERN IN ( 0 )

    --AND PA.PolicyID='DLKB6663'

    ;

    Any reason for using DISTINCT?

    If the problems continue, post DDL for tables and indexes. Also post actual execution plan.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • INNER JOIN (

    SELECT Top 1

    that will return one , single row, not one row per policy number, right?

    so the other table is limited to match one row as well.

    maybe you need to use something like row_number, and join on the "latest" record instead?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I bet I'm not the only one around here who sits on the next potential iteration of the OP's query waiting for responses before posting. Here's one of those:

    SELECT --DISTINCT

    s.SiteNumber AS [SiteNumber],

    p.PolicyID AS [PolicyID],

    p.ERNExemptFlag AS [ERNExemptFlag],

    CONVERT(VARCHAR(8), p.LastModifiedDate, 112) AS [DateSent]

    FROM Systems s

    INNER JOIN PolicyAudit p

    ON s.ID = p.SourceSystemID

    CROSS APPLY (

    SELECT TOP(1)

    pa.[HasERN]

    FROM PolicyAudit pa

    INNER JOIN [Systems] si

    ON pa.SourceSystemID = si.ID

    WHERE pa.PolicyID = p.PolicyID -- outer reference

    AND si.FileTypeIndicator IN ('C','V')

    AND pa.[Status] = '1'

    ORDER BY pa.ID DESC

    ) x

    WHERE --MONTH(PolicyAudit.LastModifiedDate) = @Month AND

    --YEAR(PolicyAudit.LastModifiedDate) = @Year AND

    p.[Status] = 1

    AND p.HasERN = 0

    AND s.FileTypeIndicator IN ('C','V')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Is it not possible to rank over the table once, and get everything you ever need from that table, instead of PolicyAudit self join (select top 1 PolicyAudit )?

    Top sets a row goal so could force the optimiser into doing silly things.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply