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).
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.
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.
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:
-- If no IsCut records found then exit procedure.
select * from SectionFlash where IsCut = 1
if (@@rowcount = 0)
insert into Section select * from SectionFlash where IsCut = 1
delete from SectionFlash where IsCut = 1
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.
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.