Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Question Unanswered: never seen a query like this before

    I "inherited" a system that uses VB6 as a frontend to a MSSQL7 database. I am fairly new to MSSQL and would like an explaination of what the following couple lines do (from a stored procedure).

    Code:
    insert into Section select top 1000 * from SectionFlash where IsCut = 1
    
    delete SectionFlash from (
        select top 1000 * from SectionFlash where IsCut = 1) as t1
        where SectionFlash.SectionKey = t1.SectionKey
    The code works on two tables Section and SectionFlash. I know "insert" statement moves data from the SectionFlash table to the Section table. The statement I don't really understand is the "delete" statement, I've never seen one that looked like that before. It is supposede to delete the records that have been moved using the "insert" statement.

    Anybody have any ideas???

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Wow....

    I...Wow...

    How have you not lost data, yet?

    You are right about your predecessors logic. He is putting 1000 rows into section, and deleting them from sectionflash. It's the equivalent of moving the rows. The problem is, when you use TOP with no order by, you get the first however many rows SQL Server has handiest. When you get into merry-go-round scans, then you see major problems with this sort of thing. I am not sure if SQL 7.0 introduced merry-go-round scans or not.

    I gotta wonder why he felt he had to stop at 1000 rows, too. It smacks of test code.

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    We are tracking down problems and we have it pretty much narrowed down to this procedure. Whether or not we are loosing data is a good question, from our best guess the system has been running like this for years and noone has noticed anything. Probably because it's always been loosing data so noone saw a difference.

    This stored procedure is ran every 10 seconds from the VB app. There are only around 80 records every 6 minutes that IsCut is set to 1 via another app monitoring a PLC (all of the records are set at the same time so there are either 0 or 80 that IsCut = 1). I have no clue why TOP 1000 is coded in there. I'd think the following would work just fine:

    Code:
    -- If no IsCut records found then exit procedure.
    select * from SectionFlash where IsCut = 1
    if (@@rowcount = 0)
    begin
        return(0)
    end
    
    
    insert into Section select * from SectionFlash where IsCut = 1
    
    delete from SectionFlash where IsCut = 1
    Last edited by RandyRiegel; 02-10-06 at 12:36.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Slightly easier (but probably performace equivalent) would be

    Code:
    if exists (select * from SectionFlash where IsCut = 1)
      begin
       return 0
      end
    ...
    If sectionflash is a large table, where only a few records have IsCut = 1, you may be able to index IsCut, but that would best be done on a test system.

  5. #5
    Join Date
    Sep 2003
    Posts
    22
    SectionFlash is a fairly small table, no more than 1000 records at the most at any given time (2 hours of data). We have a test system that we will definately try this on first.

    What are the chances of loosing data between the "insert" and "delete" statements? I just thought of that.

    Just wondering about triggers. Would an Insert trigger on the Section table that did:

    Code:
    delete from SectionFlash where SectionId in (select SectionId from Inserted)
    be safer?
    or something similar?

    Thanks for your help,
    Randy

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Putting the insert and the delete in one transaction should be a good safeguard against losing any extra inserts to sectionflash.

    A table that small probably would not need an index. If you don't mind my asking, how did you arrive at this procedure as a performance problem?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What are the roles of the Section and SectionFlash tables? What purpose do they serve, and why is the data being moved? Is SectionFlash just a staging table for data to be loaded into Section?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2003
    Posts
    22
    MCrowley,

    I didn't mean to say that this procedure was the problem. I was looking for a VB problem and it was calling this procedure around the problem area of code and decided to look at this stored procedure and the "delete" statement made me wonder if something wrong was going on there. I didn't know for sure or think for sure it was the problem.

    blindman,

    Yes, SectionFlash is sort of a "staging area". The data in SectionFlash is used by some applications and it is archived into Section table after a few hours. I'm assuming, since i didn't create the system, that this is done because Sections table is VERY big and would take forever to get info from, currently has about 1.2 million records in it (3 months of data). Anything older than 3 months archived into another table on a different database. If I could find the person who created this system I'd probably end up in prison ;-) The DB setup is alot less confusing than their VB code.

    Thanks for your help guys,
    Randy

Posting Permissions

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