November 25, 2008 at 9:50 am
I have a function like this:
-- determine if there is already a page with sequence one, i.e. whether the pages need to be shifted
DECLARE @oneCount int
SELECT @oneCount = COUNT(pageID) FROM pageList WHERE folderID = @foundFolderID AND pageSequence = 1
IF @oneCount > 0
BEGIN
-- resequence the pages in the folder by adding one to all of them
UPDATE pageList SET pageSequence = pageSequence + 1 WHERE folderID = @foundFolderID
-- move the page into the found folder and give it a sequence of 1
UPDATE pageList SET folderID = @foundFolderID, pageSequence = 1 WHERE pageID = @pageID
END
For some reason, it is ALWAYS executing the UPDATE commands even though @oneCount is not always > 0. I have double-checked the @oneCount to verify that it's either 0 or greater than 0. However, the 2 UPDATEs run regardless.
Do I have my IF statement setup wrong?
Thanks!
November 25, 2008 at 10:15 am
Looks OK. I can't see a logic problem.
The count is not necessary and it's expensive, doubly so since you're doing a count of the column, not count(*)
Try changing the count to an exists.
IF EXISTS (SELECT 1 FROM pageList WHERE folderID = @foundFolderID AND pageSequence = 1)
BEGIN
-- resequence the pages in the folder by adding one to all of them
UPDATE pageList SET pageSequence = pageSequence + 1 WHERE folderID = @foundFolderID
-- move the page into the found folder and give it a sequence of 1
UPDATE pageList SET folderID = @foundFolderID, pageSequence = 1 WHERE pageID = @pageID
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2008 at 10:27 am
Thanks...I replaced my count with the exists statement, while working, it still runs the 2 UPDATE statements regardless of the results of the EXIST statement.
I even checked manually in query analyzer like this:
IF EXISTS(SELECT 1 FROM pageList WHERE folderID = 1999677137 AND pageSequence = 1)
BEGIN
PRINT 'hi'
END
And that statement is working. Sometimes it will print 'hi' when it exists, other times it won't if it doesn't exist.
However, in the stored procedure, it ALWAYS runs the 2 UPDATES.
Thank you!
November 25, 2008 at 10:53 am
Exists doesn't return false results.
Are you sure that the variables are such that rows may not exist? Maybe put a statement just above that to insert the rows that the exiosts checks into a table for debugging purposes. Or put print statements to check the variables.
Can you maybe post the entire procedure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2008 at 11:14 am
It's kind of long, but here it goes:
/**
* Shifts a page up within the folder that contains it by changing its pageSequence value.
*
* @param pageID the ID of the page that should be moved down.
* @param userID the ID of the user that is executing the move.
*/
CREATE PROCEDURE [dbo].[testsp_ShiftPageDown]
@pageID int,
@userid int
AS
/* The algorithm for shifting a page down is as follows:
IF (the page is being moved within the folder in which it already resides, i.e. there is one or more page below it in the same folder) THEN
swap pagesequences with the page immediately below it
ELSE IF (the page is being moved outside of a folder, i.e. there are no pages below it in the same folder) THEN
look for the next folder down in the module that the current user has editing access to
IF (no such folder exists) THEN abort the shift
find the minimum pagesequence in the found folder
IF (the folder contains no pages) THEN consider the maximum sequence to be 0
at the same time: [change the folderID of the page to the found folder;
change the pageSequence of the page to 1;
shift other pages in the folder up]
END IF
*/
SET NOCOUNT ON
DECLARE @currentFolderID int -- the folder that contains the page to be moved
DECLARE @currentSequence int -- the sequence of the page to be moved
SELECT @currentFolderID = folderID, @currentSequence = pageSequence FROM pageList WHERE pageID = @pageID
-- determine if the page to be moved is the last page in the current folder
DECLARE @countBelow int -- the number of pages in this folder whose sequence is greater than the current page
SELECT @countBelow = COUNT(@pageID) FROM pageList WHERE folderID = @currentFolderID AND pageSequence > @currentSequence
IF @countBelow > 0
BEGIN
-- page will be moved WITHIN the current folder
-- get the page with the next pagesequence and swap sequences with it
DECLARE @swapPageID int
DECLARE @swapPageSequence int
DECLARE @placeholderSequence int --placeholder for use in swapping
SELECT @swapPageID = pageID, @swapPageSequence = pageSequence
FROM pageList
WHERE folderID = @currentFolderID AND pageSequence IN (SELECT MIN(pageSequence) FROM pageList WHERE folderID = @currentFolderID AND pageSequence > @currentSequence)
BEGIN TRAN
SET @placeholderSequence = @currentSequence
UPDATE pageList SET pageSequence = @swapPageSequence WHERE pageID = @pageID
UPDATE pageList SET pageSequence = @placeholderSequence WHERE pageID = @swapPageID
COMMIT TRAN
END
ELSE
BEGIN
-- page will be moved to next accessible folder above
DECLARE @currentModuleID int
DECLARE @currentFolderSequence int
DECLARE @loopFolderID int -- holds folderid during looping
DECLARE @foundFolderID int -- holds the "found" folder, if any, or -1 if none found
-- find the nearest accessible folder to this one
SELECT @currentModuleID = moduleID, @currentFolderSequence = folderSequence FROM folderList WHERE folderID = @currentFolderID
SET @foundFolderID = -1
DECLARE cFolders CURSOR FORWARD_ONLY READ_ONLY
FOR SELECT folderID FROM folderList WHERE moduleID = @currentModuleID AND folderSequence > @currentFolderSequence ORDER BY folderSequence ASC
OPEN cFolders
FETCH NEXT FROM cFolders INTO @loopFolderID
WHILE @@FETCH_STATUS = 0
BEGIN
IF dbo.fn_canEditFolder(@loopFolderID, @userid) = 1
BEGIN
SET @foundFolderID = @loopFolderID
BREAK -- found it
END
FETCH NEXT FROM cFolders INTO @loopFolderID
END
CLOSE cFolders
DEALLOCATE cFolders
-- at this point, if @foundFolderID is -1, then we never found an accessible folder above this one. if it is populated, then move the page into that folder
IF @foundFolderID = -1 RETURN
-- determine if there is already a page with sequence one, i.e. whether the pages need to be shifted
IF EXISTS(SELECT 1 FROM pageList WHERE folderID = @foundFolderId AND pageSequence = 1)
BEGIN
-- resequence the pages in the folder by adding one to all of them
UPDATE pageList SET pageSequence = pageSequence + 1 WHERE folderID = @foundFolderID
-- move the page into the found folder and give it a sequence of 1
UPDATE pageList SET folderID = @foundFolderID, pageSequence = 1 WHERE pageID = @pageID
END
END
GO
Thanks!
November 25, 2008 at 11:29 am
Try putting a check just before the exists, run the proc from management studio and see what you get back.
SELECT @foundFolderId
SELECT * FROM pageList WHERE folderID = @foundFolderId AND pageSequence = 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2008 at 11:40 am
I added that to the SP and it prints the folderId and the results from the query.
Thank you
November 25, 2008 at 11:51 am
It prints results on each execution? Means that the exists is correct, there are always rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2008 at 11:58 am
Well, if a page sequence of one does exist, then it prints the results and executes the 2 UPDATEs. If I manipulate the web app such that there is no page with a page sequence of 1, then those results don't print...however the 2 UPDATEs are still executed.
I am not sure why the 2 UPDATES continue to be executed....
thank you
November 25, 2008 at 12:05 pm
Stick a print or select inside the IF block, see if that prints out, or if the update is coming from somewhere else.
You can also trace the exact steps that the proc runs using profiler and the sp_stmtcompleted events.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2008 at 1:30 pm
Im sorry...I'm using SQL Server 2000...what is sp_stmtcompleted?
Thanks!
November 25, 2008 at 3:11 pm
November 26, 2008 at 7:55 am
Magy (11/25/2008)
Im sorry...I'm using SQL Server 2000...what is sp_stmtcompleted?
It's a profiler event. Under the Stored Procedures group. It traces each statement within a proc. It can get intensive so make sure to filter to your username or your pc's name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 7:55 am
Yes, the stored proc is being run via an ASP website.
Thank you!
November 26, 2008 at 8:17 am
Gail, I'm in SQL profiler, and I ran it while executing the stored proc. Is there anything I should be looking for besides stmtCompleted? I see a bunch of queries, but I am not sure how to debug using this.
Thanks!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply