Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: Can someone tell me if this execution plan looks ok?

    Hi. I'm having issues with an update query that timesout if the primay key is too fragmented. I'd just like to check that this execution plan is ok as I'm not sure what to look for.

    This is from a stored procedure in sql server management studio 2008.

    Any help is appreciated, it's a very simple query that just updates one row in a table.
    Attached Thumbnails Attached Thumbnails Execution Plan.png  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How about posting your sql instead? For starters, you should not be using
    Code:
    CONVERT(VARCHAR(11), @StartDate, 120)
    Use
    Code:
    dateadd(day, datediff(day, 0, @StartDate), 0)
    instead (unless your date values are stored as strings, in which case you have a whole other set of problems...).
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    everything is INDEX SEEK

    Can't get much better
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2009
    Posts
    89
    The sql is a very basic update procedure, that's why I didn't post it. I was more worried about the indexes that it has to look through, but it all seems ok.

    here's the sql:
    Code:
    ALTER PROCEDURE [dbo].[sp_TenancyUpdate] 
    	(@ID int=null,
    	@TenantID int = null, 
    	@LettingID int = null, 
    	@Startdate datetime = null, 
    	@Enddate datetime = null, 
    	@Rent money = null, 
    	@Deposit money = null, 
    	@RentPeriod nvarchar(255) = null, 
    	@PaymentDay int = null, 
    	@HB nvarchar(255) = null, 
    	@TDS nvarchar(255) = null, 
    	@Comments nvarchar(MAX) = null, 
    	@RentedInformation nvarchar(MAX) = null,
    	@Ended bit=null,
    	@AutoCharge bit=null,
    	@Reminder datetime = null,
    	@Controlled bit=null)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	If Exists (Select ID FROM dbo.Tenancies WHERE ID = @ID)
    	BEGIN
    		UPDATE dbo.Tenancies 
    			Set TenantID = @TenantID, 
    			 LettingID = @LettingID,
    			 ContractStartDate = CONVERT(VARCHAR(11), @Startdate, 120),
    			 ContractEndDate = CASE WHEN Coalesce(@Enddate,'') = '' THEN '' Else CONVERT(VARCHAR(11), @Enddate ,120)END,
    			 Rent = @Rent, 
    			 Deposit = @Deposit, 
    			 RentPeriod = @RentPeriod,
    			 PaymentDate = @PaymentDay, 
    			 [Housing Benefit?] = @HB, 
    			 TDS = @TDS, 
    			 Comments = @Comments, 
    			 RentedInformation = @RentedInformation,
    			 [Ended?] = @Ended ,
    			 Autocharge = @AutoCharge ,
    			 Reminder = CASE WHEN Coalesce(@Reminder,'') = '' THEN Null Else CONVERT(VARCHAR(11), @Reminder ,120)END,
    			 Controlled = @Controlled
    		WHERE ID = @ID
    	END	
    
    END
    My dates are stored as datetime, not as strings. The reason I was using CONVERT(VARCHAR(11), @Startdate, 120) was just an extra fix against regional differences (although the dates are passed to this procedure in a string like 'YYYY-MM-DD').

    I don't understand why you'd want to use datediff and dateadd. why do i need to minus today from the start date and then add it back on to it?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    got a sample execution statement?

    add this stuff

    Code:
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @rowcount int, @error int
    
    	If Exists (Select ID FROM dbo.Tenancies WHERE ID = @ID)
    	BEGIN
    		UPDATE dbo.Tenancies 
    			Set TenantID			= @TenantID 
    			 , LettingID			= @LettingID
    			 , ContractStartDate	= CONVERT(VARCHAR(11), @Startdate, 120)
    			 , ContractEndDate		= CASE WHEN Coalesce(@Enddate,'') = '' 
    										   THEN '' 
    										   ELSE CONVERT(VARCHAR(11), @Enddate ,120)
    									  END
    			 , Rent					= @Rent
    			 , Deposit				= @Deposit
    			 , RentPeriod			= @RentPeriod
    			 , PaymentDate			= @PaymentDay
    			 , [Housing Benefit?]	= @HB 
    			 , TDS					= @TDS 
    			 , Comments				= @Comments
    			 , RentedInformation	= @RentedInformation
    			 , [Ended?]				= @Ended 
    			 , Autocharge			= @AutoCharge 
    			 , Reminder				= CASE WHEN Coalesce(@Reminder,'') = '' 
    										   THEN Null 
    										   ELSE CONVERT(VARCHAR(11), @Reminder ,120)
    									  END
    			 , Controlled			= @Controlled
    		WHERE ID = @ID
    
    		SELECT @rowcount = @@ROWCOUNT, @error = @@ERROR
    
    		IF @error <> 0
    			PRINT 'ERROR: ' + CONVERT(varchar(3),@error)
    
    		IF @rowcount = 0
    			PRINT 'Now Rows Udated for ID: ' + CONVERT(varchar(3),@ID)
    
    	END
    
    END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2009
    Posts
    89
    Here's an example execute statemny:
    Code:
    EXEC dbo.sp_TenancyUpdate 
    @ID=832,
    @TenantID = 555,
    @LettingID = 473,
    @Startdate = '2009-04-01',
    @Enddate = '2009-09-30',
    @Rent = 41500,
    @Deposit = 0,
    @PaymentDay = 1,
    @HB = 'No',
    @RentPeriod = 'Monthly',
    @TDS  = 'Yes',
    @Ended  = 0,
    @Comments= '  ',
    @RentedInformation = '',
    @AutoCharge = -1,
    @Reminder = '',
    @Controlled = 0
    Added the error prints in there, now I just have to wait till it hangs again. Will this error go to access? or will access just say odbc error?

    Also, someone else mentioned that it may be to do with the ODBC connection. I've had a look, adn they're all exactly the same, except one computer is using XP with an older driver (3.x), while the others are on vista with a 6.x driver. could this be causing my timeouts?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudeboymcc View Post
    My dates are stored as datetime, not as strings. The reason I was using CONVERT(VARCHAR(11), @Startdate, 120) was just an extra fix against regional differences (although the dates are passed to this procedure in a string like 'YYYY-MM-DD').
    Regional differences only come into play when dates are treated like strings instead of datetime values, which is exactly what occurs when you convert an @Startdate string into another string of a different format which is then reinterpreted as a datetime value by the database.
    Your function does have the effect of truncating the datetime string to a whole date value, but the dateadd/datediff method is a more efficent means of doing this.
    If your @Startdate string is already a whole date value then your conversion is superfluous, as it will be implicitly converted before comparing it to the stored datetime datatype values, and this is the same implicit conversion that you are performing explicitly. In short, if it would not be interpreted correctly implicitly, it ain't gonna be interpreted explicitly by your function either.

    By this is a side issue, and is not the cause of the performance problems you are witnessing.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    WAIT

    TYour calling this from Access?

    Can you at least test this in SSMS first to see if you have a problem there?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudeboymcc View Post
    Added the error prints in there, now I just have to wait till it hangs again. Will this error go to access? or will access just say odbc error?
    Wait. You are doing this through MS Access?
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Jun 2009
    Posts
    89
    My front end is in access yes, but when the updates start to hang, i can recreate the problem by running the query on smss. So i know it's not access. Updates only stop causing timeouts when I rebuild the "Tenancies" table's index, which there's only one of on it's primary identifier key field. Problem is there are people working on thsi db so I can't have it timeing out for too long (so have little debugging time).

    I was just wondering if I would see the error straight away in access or if i'd have to rerun it in ssms.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser, 15:50 View Post
    WAIT
    TYour calling this from Access?
    Quote Originally Posted by blindman, 15:51 View Post
    Wait. You are doing this through MS Access?
    You are freakin' me out, man!
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jun 2009
    Posts
    89
    Ok so I ended up making a new table, adding new relationships and pasting in all the records again (with no errors about constraints), and the same update query crashed again in access.

    As soon as it happened, i copied the sql code from the update query that I created in access, and put it in a new query in ssms, and executed it. There was no error, it just kept executing for a few minutes, well passed my 10 second timeout that I have set on query executions. (why is this?)

    Then I found that my own pc still had access open, and as soon as I closed access, the query executed successfully.

    The form that was open in access uses the Tenancies table that I'm trying to update, but the form is based on a stored procedure.

    Could it be possible that the stored procedure that this form is based on locked the tables that it uses?

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I would run a trace to see what's going on. If you had a form open, depending on the design of the form, it could cause problems with the update. It could also be the update query itself (but you said you also you had problems with updating the table directly.) This still leads me to believe there are some kind of constraints/validation interferring but the SQL Server experts here like blindman can give you better advice. Sometimes these kind of problems can result from code running and leaving a recordset open.

    Did you run the dbcc and other commands to repair the db?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Nov 2009
    Posts
    15
    I have to convert the functions from Postgre SQL to SQL Server 2005. But I am unable to find the appropriate tool. Can anybody help me?

  15. #15
    Join Date
    Nov 2009
    Posts
    15
    Quote Originally Posted by fusimas View Post
    I accept with information:Regional differences only come into play when dates are treated like strings instead of datetime values, which is exactly what occurs when you convert an @Startdate string into another string of a different format which is then reinterpreted as a datetime value by the database. Your function does have the effect of truncating the datetime string to a whole date value, but the dateadd/datediff method is a more efficent means of doing this.
    I have already converted the whole PostgreSQL database with all constraints and other stuff, but the functions are not converted. I have seen the functions, those function are using the builtin functions of postgre. Now I am unable to convert the functions and I am in trouble.

Posting Permissions

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