help with join query

data1025 · #1 (**permalink**) 11-30-07, 15:38

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

amthomas · #2 (**permalink**) 11-30-07, 18:41

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"

Pat Phelan · #3 (**permalink**) 12-01-07, 01:55

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

data1025 · #4 (**permalink**) 12-04-07, 07:25

Thank you very much. That worked as needed.

Internet Services

Web Development

Digital Marketing

Consumer Tech