incomplete scan of tables with cursor

incomplete scan of tables with cursor

Post by John Penne » Thu, 13 Feb 1997 04:00:00



The code that follows is a procedure I use to parse an address field
into its component parts.  
The problem I'm having is:  
It only makes it up to 2000 records, then it stops parsing.  There are
two stored procedures that it calls, which are included at the end of
the code.  If anyone can look this over and figure out why it stops at
2000 rows, I would be ever so grateful.

Thanks.

John Penney
INPHO, Inc.

The table definition includes the following fields, which are all
char(255) fields:
        BuyerBorrower_Street_Address,
        Unit,
        Document_Date,
        Sales_Price,
        Street_Number,
        Street_Number_2,
        Pystname,
        Pysttype,
        Pystdir,
        Parse_Error,
        Pycondo,
        Unit_Number,
        Selling_Month,
        Selling_Year,
        Selling_MthYear,
        Selling_Price

/*  Parse_Error field will be marked with codes as follows:

        blank  
        0  Probably shouldn't be in database
        1  Probably not much we can do
        2  Probably worth looking at
        3
        4
        5  
        6  
        7  

*/
DEALLOCATE ctrw
declare @bbsa char(255), @bbsatemp char(255),
        @token1 char(255), @token2 char(255),
        @pattern1 char(255), /* string used for pattern matching,
                               eg: to determine if a string is
                               a number */
        @szCheckString char(100),
        @blank_ind tinyint,
        @t1isnmb float,
        @rowscount float,
        @result smallint,
        @tokenCount tinyint,
        @backCount tinyint,
        @FirstRecordFlag tinyint,
        @tiTypPos tinyint,
        @temp char(255),
        @tiLpCnt tinyint,
        @backCountLimit tinyint,
        @szPreString char(255),

/* The fields in this script    */
/*                                      */
/*                                      */
  @Unit char(255),
  @Document_Date char(8),
  @Street_Number char(255),
  @Street_Number_2 char(255),
  @Pystname char(255),
  @Pysttype char(255),
  @Pystdir char(255),
  @Unit_Number char(255),
  @Parse_Error char(255),
  @Pycondo char(255),
  @Selling_Month char(255),
  @Selling_Year char(255),
  @Selling_MthYear char(255),
  @Selling_Price char(255),
  @Sales_Price char(255)

/*                                      */
/*                                      */
/*______________________________________*/

/* an "*" as the first non-blank char indicates a blank address */
select @t1isnmb=0
select @rowscount=0
select @blank_ind = ascii("*")

select @Street_Number=""
select @Street_Number_2=""
select @Pystname=""
select @Pysttype=""
select @Pystdir=""
select @Unit_Number=""
select @Parse_Error=""
select @Pycondo="N"
select @Selling_Month = ""
select @Selling_Year = ""
select @Selling_MthYear =""
select @Selling_Price=""
select @Sales_Price=""

select @FirstRecordFlag=1

/* declare cursor to allow parsing on single pass */

declare ctrw cursor for
select
   BuyerBorrower_Street_Address, Unit, Document_Date, Sales_Price
from
   _91001Mailered
for update of
        Street_Number,
        Street_Number_2,
        Pystname,
        Pysttype,
        Pystdir,
        Parse_Error,
        Pycondo,
        Unit_Number,
        Selling_Month,
        Selling_Year,
        Selling_MthYear,
        Selling_Price

set nocount on

open ctrw
fetch next from ctrw
into @bbsa,@Unit, @Document_Date, @Sales_Price

while ( @@FETCH_STATUS=0 )
begin
  if @FirstRecordFlag = 0
    begin

      /* Remove leading zeros from numbers */

      while substring(@Street_Number,1,1)="0"
        begin
          select
@Street_Number=substring(@Street_Number,2,datalength(@Street_Number))
        end

      while substring(@Street_Number_2,1,1)="0"
        begin
          select
@Street_Number_2=substring(@Street_Number_2,2,datalength(@Street_Number_2))
        end

      select @Selling_Price=@Sales_Price
      while substring(@Selling_Price,1,1)="0"
        begin
          select
@Selling_Price=substring(@Selling_Price,2,datalength(@Selling_Price))
        end

      select @Selling_MthYear = "19" + substring(@Document_Date,7,2) +
substring(@Document_Date,1,2)

      /* Transfer the variable values into the database fields  */
      update _91001Mailered
      set Street_Number=@Street_Number
      where current of ctrw

      update _91001Mailered
      set Street_Number_2=@Street_Number_2
      where current of ctrw

      update _91001Mailered
      set Pystname=@Pystname
      where current of ctrw

      update _91001Mailered
      set Pysttype=@Pysttype
      where current of ctrw

      update _91001Mailered
      set Pystdir=@Pystdir
      where current of ctrw

      update _91001Mailered
      set Unit_Number=@Unit_Number
      where current of ctrw

      update _91001Mailered
      set Parse_Error=@Parse_Error
      where current of ctrw

      update _91001Mailered
      set Pycondo=@Pycondo
      where current of ctrw

      update _91001Mailered
      set Selling_Month=@Selling_Month
      where current of ctrw

      update _91001Mailered
      set Selling_Year=@Selling_Year
      where current of ctrw

      update _91001Mailered
      set Selling_MthYear=@Selling_MthYear
      where current of ctrw

      update _91001Mailered
      set Selling_Price=@Selling_Price
      where current of ctrw

      fetch next from ctrw
      into @bbsa,@Unit, @Document_Date, @Sales_Price
      if @@FETCH_STATUS<>0 break
    end
  else select @FirstRecordFlag = 0

  select @Street_Number=""
  select @Street_Number_2=""
  select @Pystname=""
  select @Pysttype=""
  select @Pystdir=""
  select @Unit_Number=""
  select @Parse_Error=""
  select @Pycondo="N"
  select @Selling_Month = ""
  select @Selling_Year = ""
  select @Selling_MthYear =""
  select @Selling_Price = ""

  /* put address into working variable that we can mess with */
  /* without having to reselect from the cursor */
  select @bbsa=ltrim(rtrim(@bbsa))
  select @Pystname=@bbsa

  /* Mark records containing " box ", " cio ", " svl " */

  if upper(@bbsa) like "% BOX %" or upper(@bbsa) like "% SVL %" or
upper(@bbsa) like "% CIO %"
    begin
      select @Parse_Error="7"
      continue
    end

  /* Mark records containing " OFC " as "shouldn't be here" */

  if upper(@bbsa) like "% OFC %"
    begin
      select @Parse_Error="1"
      continue
    end

  select @Selling_Month=substring(@Document_Date,1,2)

  select @Selling_Month =
    CASE @Selling_Month
      WHEN '01' then 'January'
      WHEN '02' then 'February'
      WHEN '03' then 'March'
      WHEN '04' then 'April'
      WHEN '05' then 'May'
      WHEN '06' then 'June'
      WHEN '07' then 'July'
      WHEN '08' then 'August'
      WHEN '09' then 'September'
      WHEN '10' then 'October'
      WHEN '11' then 'November'
      WHEN '12' then 'December'
      ELSE ''
    END

  if ltrim(rtrim(@Selling_Month)) is null
    begin
      select @Parse_Error="5"
      continue
    end

  select @Selling_Year=substring(@Document_Date,7,2)

  if ltrim(rtrim(@Selling_Year)) is null
        begin
        select @Parse_Error="6"
        continue
        end

/* Year 2000 problem here */
  select @Selling_Year="19" + @Selling_Year

  select @rowscount=@rowscount+1
  if (ltrim(rtrim(@Pystname))=null) or (@Pystname=null)
    begin
      select @Parse_Error = "0"
      continue
    end

  /* count the tokens in the string */

  select @tokenCount=0
  exec _3rd_parse_string @Pystname, @token1 output, @Pystname output    

  while not ((@token1 is null) and (@Pystname is null))
  /* The procedure written by Russ at 3rd Millenium sets */
  /* both return values to null if the string is empty */
    begin
      select @tokenCount=@tokenCount+1
      exec _3rd_parse_string @Pystname, @token1 output, @Pystname output
    end
  select @Pystname=@bbsa
  /* Now @tokenCount should equal the number of tokens in the */
  /* address string, 0 if the string is empty.  And, @Pystname */
  /* should contain the address string as selected from the database */

  /* get the first token */
  select @token1=""
  exec _3rd_parse_string @Pystname, @token1 output, @bbsatemp output

  exec @result=_3rd_string_is_integer @token1
  if @result=0 /* the first token is a number, so it's probably the
street number */
    begin
      select @Street_Number=@token1
      select @tokenCount = @tokenCount - 1
      select @Pystname = @bbsatemp
      exec _3rd_parse_string @Pystname, @token1 output, @bbsatemp output
      if @token1 = "&" begin
        exec _3rd_parse_string @bbsatemp, @token1 output, @bbsatemp
output
        exec @result=_3rd_string_is_integer @token1
        if @result=0 begin
          select @Street_Number_2 = @token1
          if @Street_Number_2 = @Street_Number begin
            exec _3rd_parse_string @bbsatemp, @token1 output, @token2
output
            if @token1 = "1/2" begin
              select @Street_Number_2 = @Street_Number_2 + ".5"
              select @Pystname = @token2
              select @tokenCount = @tokenCount - 3
            end
            else begin
              select @Pystname = @bbsatemp
              select @tokenCount = @tokenCount - 2
            end
          end
          else begin
            select @Pystname = @bbsatemp
            select @tokenCount = @tokenCount - 2
          end
        end
        else select @Parse_Error = "2" /* '&' at left w/ no number
after. */
      end
      else if @token1 = "1/2" begin
        select @Street_Number = @Street_Number + ".5"
        select @Pystname = @bbsatemp
        select @tokenCount = @tokenCount - 1
      end
    end
    else
    begin
      /* Check for dashed numbers here */
      exec _3rd_parse_string @token1, @token1 output, @token2 output,
"-"
      /* Now, if the second token is a number, it becomes
street_number_2 */
      /* If it is not a number, it could be a streetname or ? */

      if not (@token2 is null)  /* a dash was found */
        begin
          exec @result=_3rd_string_is_integer @token1
          if @result=0 /* the first token is a number, so it's probably the
street number */
            begin
              select @Street_Number=@token1
              exec @result=_3rd_string_is_integer @token2
              if @result=0 or datalength(@token2) = 1
                begin
                  select @Street_Number_2 = @token2
                  select @tokenCount = @tokenCount - 1
                  select
...

read more »

 
 
 

incomplete scan of tables with cursor

Post by Michael R. Pallese » Thu, 13 Feb 1997 04:00:00


That's a lot code to review but I would be concerned about size
considerations.  With a bunch of varchar(255) fields it is easy to
imagine tempdb filling up or ...  Have you tried making the fields
smaller and seeing if the failure point changes?  There are a number of
limits to SQL Server which are kind of documented in SQL Server Books
Online.


> The code that follows is a procedure I use to parse an address field
> into its component parts.
> The problem I'm having is:
> It only makes it up to 2000 records, then it stops parsing.  There are
> two stored procedures that it calls, which are included at the end of
> the code.  If anyone can look this over and figure out why it stops at
> 2000 rows, I would be ever so grateful.

> Thanks.

> John Penney
> INPHO, Inc.


 
 
 

1. Table scan, Table scan, Table scan

Again I got a table scan. It was again between two primarykeys.
It's a insert with a select between 5 tables where the condition
is on the first table. The third table contains 10 000 000 records
and when the "query optimizer" thinks that a table scan is
faster than using the primary key the query takes 30 seconds
instead off 0.5.

I have sometimes used SET FORCEPLAN ON but it does not work
well when doing insert or update. The table that is used for
insert/update is taken as first table and that is not optimal
if the condition is on another table in the select statement.

Does anyone know if there is a way to give the complete execution
order for a query?

/Bj?rn Linde

2. Problems with multi-threading ADO/C++ code

3. TABLE SCAN Vs INDEX SCAN/SEEK

4. Problem using the Send Mail script

5. Index scan vs Full table scan

6. Error trapping with VB

7. Clustered Index Scan vs. Table Scan

8. Reporting Oracle - Microsoft Word: SQLWord 2.1.5 now available

9. Table Scan - Scans more records than are there

10. Table scans with large table joined to small table

11. way to extract table from incomplete backup

12. incomplete returns on simple queries to a large table