Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    19

    Unanswered: help with join query

    I am writing a query to find missing numbers. The query works, but is very very slow due to the number of records it is currently pulling.

    I want to limit the records to only search where "records.event_year = 2007" but when I try to insert that after the "from dbo.records" it gives me an error.

    Where exactly do I need to add this and is there anything else I can do to make the query run faster? The event_year, state_file_number, and isactive are all indexed.


    ================================================== =======
    ALTER procedure [Migrate].[Chk_Missing_SFN]


    @beg as int,
    @end as int


    AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    select BeforeSkip+1 as gapStart, NextValue-1 as gapEnd from (
    select
    a.state_file_number as BeforeSkip,
    min(b.state_file_number) as NextValue
    from dbo.records A join dbo.records b
    on a.state_file_number < b.state_file_number

    where a.state_file_number between @beg and @end and a.isactive = 'T' and b.isactive = 'T'

    group by a.state_file_number
    having min(b.state_file_number) > a.state_file_number + 1
    ) X

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    what error are you getting?

    I imagine that the "A.event_year = 2007" needs to go after the "on A.state_file_number < b.state_file_number"
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Remember that this snippet creates a million row test set before you judge speed.
    Code:
    --  ptp  20071130  See http://www.dbforums.com/showthread.php?t=1624988
    
    CREATE TABLE patp (
       patpId	INT		NOT NULL
       CONSTRAINT XPKpatp
          PRIMARY KEY CLUSTERED (patpId)
       )
    
    INSERT INTO patp (
       patpID
       ) SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
       UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    
    INSERT INTO patp (
       patpID
       ) SELECT d0.patpID + d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
       FROM patp AS d0
       CROSS JOIN (SELECT     10 * patpId AS patpID FROM patp) AS d1
       CROSS JOIN (SELECT    100 * patpId AS patpID FROM patp) AS d2
       CROSS JOIN (SELECT   1000 * patpId AS patpID FROM patp) AS d3
       CROSS JOIN (SELECT  10000 * patpId AS patpID FROM patp) AS d4
       CROSS JOIN (SELECT 100000 * patpId AS patpID FROM patp) AS d5
       WHERE 0 < d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
       ORDER BY 1
    
    DECLARE @i	INT
    SET @i = 1
    
    WHILE @i < (SELECT Max(patpID) FROM patp)
       BEGIN
          DELETE FROM patp WHERE patpID = @i
          SET @i = 2 * @i
       END
    
    SELECT a.patpID AS block_begin
    ,  (SELECT Min(b.patpID)
          FROM patp AS b
          WHERE a.patpID <= b.patpID
             AND NOT EXISTS (SELECT *
                FROM patp AS c
                WHERE c.patpID = 1 + b.patpID)) AS block_end
       FROM patp AS a
       WHERE NOT EXISTS (SELECT *
          FROM patp AS b
          WHERE b.patpId = a.patpID - 1)
    
    DROP TABLE patp
    -PatP

  4. #4
    Join Date
    Jan 2007
    Posts
    19
    Thank you very much. That worked as needed.

Posting Permissions

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