Can you query against a CURSOR?
-
Basically what the subject says...
I have a process that is filtering out records...
Initially it loads all the records from one table into a cursor
DECLARE row_cursor INSENSITIVE CURSOR FOR
SELECT Field1, Field2
FROM
TABLE1
WHERE
Field1 IN (7,8,15)
OPEN row_cursor
FETCH NEXT FROM row_cursor
INTO @field1, @field2
If @@feathstatus = 0
HERE I WANT TO QUERY the dataset that would have been pulled into the cursor...I am not sure I can do this...I don't think I am able to...
The reason is, otherwise I am currently going back against the original table to query for the most recent record of a subset of records (and some other logic).
The main table has around 200,000 records vs. the subset of records that would have around 40,000...
I might be way off base here too...I had also thought about a temp table...would that be more efficent?
-
1.Can you explain what ur process is trying to do (in layman's term).why do u think Cursor is the only way to do it? Looks like you are looping through the cursor and querying some other table.
2.Its better to avoid cursors and you can find ways to get a set based method to do what you want(not always).
Thanks
Sreejith
-
Lee,
You used the key words "query the dataset". Cursors contain a bunch of rows but they can only be worked on or "fetched" one agonizing row at a time. If you want to query "the dataset" and the dataset is a sub-set of a larger table, use a "derived table". A derived table is nothing more than a "sub-query" (not to be confused with a "correlated sub-query" which can be dreadfully slower than a cursor if a triangular join exists) that's encapsulated in parentheses, given an alias like any table, and used in the FROM clause as if it were a table.
Lookup "Derived Tables" in Books Online for more info.
You can also create scratchpad tables with temporary tables. Lookup "Temporary Tables" in Books Online for more info.
Of course, depending on the nature of what you want to do, a simple query with the correct criteria may be all that you need. But, as some have suggested, it's difficult to assist when we have no clue as to what it is you wish to do with the "dataset" (results) from the base query you posted, which is...
SELECT Field1, Field2
FROM
TABLE1
WHERE
Field1 IN (7,8,15)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As suggested by Jeff I believe per your post, you are looking at only a subset of your base data, and you anchor all your logic off of a Temp table by populating your temp table at the start of your stored procedure. Unless you think using cursor is mandatory to fullfil your requirement its highly recommended that you avoid using a cursor.
Prasad Bhogadi
www.inforaise.com -
Unless someone can recommend a different way to do this I think I need to use a cursor. I will break it down as simple as I can.
I have TABLE A - Has 200,000 records. I am running a select statement that will pull 8 fields and criteria to filter it down to 40,000 records.
Of these records they have a compound Primary Key. The first field is the Material Number and the second is the revision number
199 1
199 2
199 3
So for one Material Number you can have multiple rows...but each is unique because of the revision number.
The goal is to get the most recent row based on some further logic...the record can have a status of (A,D,H,I) and they want the record in that order based on the status and the most recent revision number.
I have a secondary table (created for this process) that will be used to house the information (kind of a staging table for the next process).
Rather than trying to explain the code I will just post it....
CREATE PROCEDURE bp_Procedure AS BEGIN
BEGIN DECLARE @IN_MatNum INT, @IN_VerNumMat INT, @IN_MatDesc VARCHAR(60), @IN_SpecSta VARCHAR(1), @IN_OldMatID VARCHAR(12), @IN_MatTypeCode SMALLINT, @IN_MatSubTypeCode SMALLINT, @UPDATE_Ver INT
DECLARE row_cursor INSENSITIVE CURSOR FOR SELECT MatNum, VerNumMat, MatDesc, SpecSta, OldMatID, MatTypeCode, MatSubTypeCode FROM dbo.tb_MatSpec WHERE MatTypeCode IN (7,8,15)
OPEN row_cursor
FETCH NEXT FROM row_cursor INTO @IN_MatNum, @IN_VerNumMat, @IN_MatDesc, @IN_SpecSta, @IN_OldMatID, @IN_MatTypeCode, @IN_MatSubTypeCode WHILE @@FETCH_STATUS = 0 BEGIN IF(( SELECT COUNT(*) FROM tb_MatSpec_Version WHERE MatNum = @IN_MatNum) = 0)
BEGIN
INSERT INTO tb_CARDs_MTLInformation VALUES (@IN_MatNum, @IN_VerNumMat, @IN_MatDesc, @IN_SpecSta, @IN_OldMatID, @IN_MatTypeCode, @IN_MatSubTypeCode) END ELSE BEGIN SELECT @UPDATE_Ver = NULL SELECT @IN_MatNum = MatNum, @UPDATE_Ver = VerNumMat, @IN_MatDesc = MatDesc, @IN_SpecSta = SpecSta, @IN_OldMatID = OldMatID, @IN_MatTypeCode = MatTypeCode, @IN_MatSubTypeCode = MatSubTypeCode FROM dbo.tb_MatSpec WHERE MatNum = @IN_MatNum AND SpecSta IN ('A', 'D') GROUP BY MatNum, VerNumMat, MatDesc, SpecSta, OldMatID, MatTypeCode, MatSubTypeCode HAVING VerNumMat = MAX(VerNumMat) --###################################################################################################### -------------------------------------------------------------------------------------------------------- IF (@UPDATE_Ver IS NULL) BEGIN SELECT @IN_MatNum = MatNum, @UPDATE_Ver = VerNumMat, @IN_MatDesc = MatDesc, @IN_SpecSta = SpecSta, @IN_OldMatID = OldMatID, @IN_MatTypeCode = MatTypeCode, @IN_MatSubTypeCode = MatSubTypeCode FROM dbo.tb_MatSpec WHERE MatNum = @IN_MatNum AND SpecSta IN ('H') GROUP BY MatNum, VerNumMat, MatDesc, SpecSta, OldMatID, MatTypeCode, MatSubTypeCode HAVING VerNumMat = MAX(VerNumMat) --###################################################################################################### -------------------------------------------------------------------------------------------------------- IF (@UPDATE_Ver IS NULL) BEGIN SELECT @IN_MatNum = MatNum, @UPDATE_Ver = VerNumMat, @IN_MatDesc = MatDesc, @IN_SpecSta = SpecSta, @IN_OldMatID = OldMatID, @IN_MatTypeCode = MatTypeCode, @IN_MatSubTypeCode = MatSubTypeCode FROM dbo.tb_MatSpec WHERE MatNum = @IN_MatNum AND SpecSta IN ('I') GROUP BY MatNum, VerNumMat, MatDesc, SpecSta, OldMatID, MatTypeCode, MatSubTypeCode HAVING VerNumMat = MAX(VerNumMat) --###################################################################################################### -------------------------------------------------------------------------------------------------------- END END IF (@UPDATE_Ver IS not NULL) UPDATE tb_CARDs_MTLInformation SET VerNumMat = @UPDATE_Ver MatDesc = @MatDesc, SpecSta = @SpecSta, OldMatID = @OldMatID, MatTypeCode = @MatTypeCode, MatSubTypeCode = @MatSubTypeCode WHERE MatNum = @IN_MatNum END FETCH NEXT FROM row_cursor INTO @IN_MatNum, @IN_VerNumMat, @IN_MatDesc, @IN_SpecSta, @IN_OldMatID, @IN_MatTypeCode, @IN_MatSubTypeCode END CLOSE row_cursor DEALLOCATE row_cursor END ENDI would love to figure out a different way to this problem without using a Cursor...but not sure if there is a way...
-
HI Jeff,
I shouldn't have used the term Dataset...that probably was confusing. I have this process working with the cursor...but I am always open to different and better ways to optimize a process.
I have used Derived Tables before and you are right, they work very slick...
In this case I feel like there is a extra layer of complexity which I am not getting my head around.
The fact that the records in the main table are in the format that allows for multiple Material Numbers(MatNum's) and distinct Revisions for each MatNum
199 1 I
199 2 I
199 3 A
And the fact that they want to only see the most recent revision where the status of the record is in the order of A,D,H,I.
If I were to use a derived table (which would go against the main table of 200,000 records and filter it down initally, you can see what I am talking about in the post above.)
How can you run a series of logic statements against a derived table?
If you look in the post above, you will see a series of IF statements that go through all the different status a record can be in and pulls that information...but my downfall is it is using the cursor with the filtered down records to query against the main table to get the information.
I hope this isn't too confusing...so I am going to stop babbling and see if you have questions or are just done with me
-
Hi,
unfortunately you didn't supply DDL and the cursor is uncommented, so it is quite hard to understand what you are trying to do. Could you please somehow describe what is the initial status and what is required result?
I'm pretty sure you can do it some other way - maybe it will require a temp table because of the series of logic statements (and maybe not), but cursor should not be necessary.
Are you just trying to filter the rows and display those that satisfy certain conditions? I found some INSERT INTO as well... not sure what that means.
-
This process is supposed to pull out the information from a master table...filter it down....get the most recent record based on the revision number and the status (A,D,H,I) in that order.
For example if you had the following subset of records out of the main table
199 1 I
199 2 I
199 3 H
199 4 A
In my second table I would want the record
199 4 A
Now repeat that process for 200,000 rows.
Once it is said and done, the second table winds up with around 34,000 records.
I have the extended logic in the stored procedure because I have to evaluate each possible senerio of the record status,
- First if it is an A, go find the max revision and get that row.
- Second if it is an D, go find the max revision and get that row.
- Third if it is an H, go find the max revision and get that row.
- Last if it is an I, go find the max revision and get that row.
A record can either be in A or D status, but not both.
A record can be in both A and H status, but only one of each.
SO...to hopefully get the logic across...the reason I used a cursor was to get a smaller subset of records vs. going against the whole table.
I used the select statement with filtering criteria to knock down the number of records going into the cursor.
as each record goes through the cursor it gets evaluated and only one record is chosen and put into the second table.
The derived table idea sounds great but how can I do multiple evaluations against a derived table?
Hope this makes more sense...
Lee
-
I will try to reproduce what I understood so far, please correct me if I'm wrong.
1) You have a table dbo.tb_MatSpec, which has 200k rows. Column MatNum identifies an "object"; there can be several rows for one object, numbered from 1 to n in column VerNumMat (also "revision number"). Column SpecSta in this table is status of the row.
2) Status can have 4 values, A, D, H, I.
If one of the rows for certain object has status A, there never is another row for this object with status D, and vice versa.
Each object can only have one row with status A and one with status H.
3) Desired result: display precisely one row for each "object" (MatNum). When choosing which row to display, look for rows with status A, D, H, I (in this order). If there are several rows with the same status, take the one with maximum VerNumMat.
Is that it?
-
YES
...that is it...in a nutshell. -
OK, try this as first step:
SELECT a.MatNum,
a.VerNumMat,
a.MatDesc,
a.SpecSta,
a.OldMatID,
a.MatTypeCode,
a.MatSubTypeCode
FROM tb_MatSpec a
JOIN
(select MatNum, MIN(SpecSta) as status
from tb_MatSpec
group by MatNum) as x ON a.MatNum = x.MatNum AND a.SpecSta = x.status
ORDER BY a.MatNum, a.VerNumMat DESC
This way you will still have several rows for these objects where there are several rows with the same, "highest priority" status. If that works, you can expand the query and take only the first row for each, using the same logic as when looking for the status (first selecting MAX(VerNumMat) for each NumMat in the derived table then joining again to the base table to pull entire row).
PS. I'm at home now (no SQL Server at hand) and can't verify the SQL. There can be some stupid mistake since I was typing the code directly into the browser without any checks... well, lets hope there isn't, but I wanted to warn you 🙂
-
Looks good...I think this might work. I am going to have to test it out on some senerios and see what it turns up !!
Thanks again for the logic
Lee
-
I am not clear but I assume you were you trying to do something like this.
Select MatNum,VerNumMat,MatDesc,SpecSta,OldMatID,MatTypeCode,MatSubTypeCode
FROM dbo.tb_MatSpec tb_MatSpec INNER JOIN
(
Select MatNum, Max(VerNumMat) as VerNumMat,'A-D' as SpecSta from dbo.tb_MatSpec
where SpecSta IN ('A', 'D')
Group by MatNum
Union all
Select MatNum, Max(VerNumMat)as VerNumMat,SpecSta from dbo.tb_MatSpec
where SpecSta IN ('H','I')
Group by MatNum,SpecSta
) as MaxVerNumMat On tb_MatSpec.MatNum = MaxVerNumMat.MatNum
and tb_MatSpec.VerNumMat = MaxVerNumMat.VerNumMat
WHERE tb_MatSpec.MatTypeCode IN (7,8,15)
Please let me know if this helps.
Thanks
Sreejith
-
Interesting coincidence... this weekend I decided to look at a stored procedure I wrote some 2 years ago. Original version of this procedure I inherited was unusable, because it ran more than 5 hours (if I remember correctly, it was using about 10 temporary tables and several cursors). In my 2-year old version without cursors, I managed to cut the time down below 30 minutes, which was OK because it runs only 4 times a year, and always during time when there are no users connected, so I didn't optimise further. Well, and now as the amount of data in our DB is growing, I realized it begins to be slow again, and decided to look wheter it can be optimised. Guess what was the reason of poor performance? Correlated subquery (see Jeff's post). I replaced it on two places with a derived table - very much in the way of my posted solution to your problem - added one missing index, and... the procedure takes less than 5 minutes to execute.
BTW, let us know whow you are doing and whether the code is working well!
-
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply