[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

stored procedure executes quickly from SSMS, but slow from .NET application

Hi Experts,

I have a stored procedure that executes quickly from SSMS (< 1sec), but slow from my .NET application (about 20 secs).

I think this has something to deal with the query execution plan that SSMS and my application use.  I know how to see the query execution plan from SSMS but I'm not quite sure what the plan means.  Also even if I could understand what the execution plan does, how do I make my .NET app use that better execution plan?

Thanks,
OFGemini
0
OFGemini
Asked:
OFGemini
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you make sure that SET ANSI_NULLS = ON in the stored procedure


or run the sp like this


SET ANSI_NULLS = ON
GO
ALTER PROCEDURE ....
0
 
Kent OlsenData Warehouse Architect / DBACommented:
The execution plan of the stored procedure won't change just because it's being called from a .NET application.

More likely, is that there is a significant data volume that is being put on the network for your application to manage.


Kent
0
 
OFGeminiAuthor Commented:
aneeshattingal, I tried it and there was no improvement.

kdo, I don't think its a significant data volume on the network... I retrieved several more times data almost with the same sproc.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Aneesh RetnakaranDatabase AdministratorCommented:
can you put the entire stored procedure here
0
 
OFGeminiAuthor Commented:
USE [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5]
GO

/****** Object:  StoredProcedure [dbo].[FsDocuments_Sec]    Script Date: 09/12/2012 14:29:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[FsDocuments_Sec]  
@UserGuid UNIQUEIDENTIFIER,  
@Guid     UNIQUEIDENTIFIER AS  

BEGIN        
    SET ANSI_NULLS ON
SELECT
[guid],              
[targetguid],              
[created],              
[modified],              
[originalname],              
[lft],              
[rgt],              
[length],              
[pages],              
[islatestversion],              
[title]      
FROM  
            (SELECT *              
            FROM [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[FsDisplayNodes_Documents]  
            WHERE  [parentguid] = @Guid) d  
            LEFT JOIN (SELECT *              
                              FROM   [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[secfsviewrestrictions] r    
                              WHERE  r.ontrustee = @UserGuid) r          
                              ON ( r.onfsnode = d.guid )  
                              WHERE  r.[ontrustee] IS NULL   END

GO
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
also run sql profiler and then the application, see how long the sp takes to run while calling from the application. could be some thing else is slowing down, not the sp call
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[FsDocuments_Sec]  
@UserGuid UNIQUEIDENTIFIER,  
@Guid     UNIQUEIDENTIFIER AS  

BEGIN        
SELECT
[guid],              
[targetguid],              
[created],              
[modified],              
[originalname],              
[lft],              
[rgt],              
[length],              
[pages],              
[islatestversion],              
[title]      
 
FROM [dbo].[FsDisplayNodes_Documents]  d
LEFT JOIN [dbo].[secfsviewrestrictions] r    ON ( r.onfsnode = d.guid )
AND parentguid = @Guid and r.ontrustee = @UserGuid
WHERE  r.[ontrustee] IS NULL  
END

GO
0
 
OFGeminiAuthor Commented:
aneeshattingal,

that modification seems to work.  why does that work better then the original?
0
 
OFGeminiAuthor Commented:
also, its definitely was the SPROC.
0
 
OFGeminiAuthor Commented:
aneeshattingal, your query isn't returning the same results
0
 
Julian HansenCommented:
Try doing a simple call to the DB from your code - something like

SELECT 1;

See if this is significantly quicker - if it is not might be something to do with how your app is establishing the connection. Either way the above should give you a baseline to measure the performance of your stored proc through .Net relative to SSMS
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>that modification seems to work.  why does that work better then the original?
 This one is more optimal that yours, and yours may be faster when you call from ssms, probbaly because it was actually fetching it from the cache.
0
 
OFGeminiAuthor Commented:
Ok, so I looked at the query plan when it executes quickly from SSMS and compared it to the slow one from the .NET application and they are totally different. Then I made the query use the quick query plan and it works.  

I removed the statements b/c it was too much, but below is what I did:

USE [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5]
GO
/****** Object:  StoredProcedure [dbo].[FsDocuments_Sec]    Script Date: 09/12/2012 16:48:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[FsDocuments_Sec]  
@UserGuid UNIQUEIDENTIFIER,  
@Guid     UNIQUEIDENTIFIER AS  
BEGIN        
       

SELECT
[guid],              
[targetguid],              
[created],              
[modified],              
[originalname],              
[lft],              
[rgt],              
[length],              
[pages],              
[islatestversion],              
[title]      
FROM  
(SELECT *              
FROM [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[FsDisplayNodes_Documents]  
WHERE  [parentguid] = @Guid) d  
LEFT JOIN (SELECT *              
                  FROM   [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[secfsviewrestrictions] r    
                  WHERE  r.ontrustee = @UserGuid) r          
                  ON ( r.onfsnode = d.guid )  
                  WHERE  r.[ontrustee] IS NULL                     
                  OPTION (USE PLAN N'
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
          .................
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
')
END
0
 
OFGeminiAuthor Commented:
depending on the parameters of my SPROC the HINT makes the Query execute fast and sometimes not.  I know when I want to use the hint or not use the Hint , so would this be OK? is there a better way of doing this?


USE [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5]
GO

/****** Object:  StoredProcedure [dbo].[FsDocuments_Sec]    Script Date: 09/12/2012 17:54:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[FsDocuments_Sec]  
@UserGuid UNIQUEIDENTIFIER,  
@Guid     UNIQUEIDENTIFIER AS  
BEGIN        
       
if ((SELECT count(*) FROM [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[FsNodes] WHERE  [parentguid] = @Guid) >= 1000)
BEGIN
            
            SELECT
            [guid],              
            [targetguid],              
            [created],              
            [modified],              
            [originalname],              
            [lft],              
            [rgt],              
            [length],              
            [pages],              
            [islatestversion],              
            [title]      
            FROM  
            (SELECT *              
            FROM [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[FsDisplayNodes_Documents]  
            WHERE  [parentguid] = @Guid) d  
            LEFT JOIN (SELECT *              
                              FROM   [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[secfsviewrestrictions] r    
                              WHERE  r.ontrustee = @UserGuid) r          
                              ON ( r.onfsnode = d.guid )  
                              WHERE  r.[ontrustee] IS NULL                     
                              OPTION (USE PLAN N'
            <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
              <BatchSequence>
                  <Batch>
                    <Statements>
                        .................
                    </Statements>
                  </Batch>
              </BatchSequence>
            </ShowPlanXML>
            ')
             
END
ELSE
BEGIN
            SELECT
            [guid],              
            [targetguid],              
            [created],              
            [modified],              
            [originalname],              
            [lft],              
            [rgt],              
            [length],              
            [pages],              
            [islatestversion],              
            [title]      
            FROM  
            (SELECT *              
            FROM [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[FsDisplayNodes_Documents]  
            WHERE  [parentguid] = @Guid) d  
            LEFT JOIN (SELECT *              
                              FROM   [DokmeeDMS_9fd46203200a45058ed82fb737b9aea5].[dbo].[secfsviewrestrictions] r    
                              WHERE  r.ontrustee = @UserGuid) r          
                              ON ( r.onfsnode = d.guid )  
                              WHERE  r.[ontrustee] IS NULL  
END
END

GO
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
it depends, i dont really do that untill i have a production issue.
0
 
OFGeminiAuthor Commented:
aneeshattingal,

When you have a production issue, you Alter your stored procedure to use a HINT like I did in the above post?  Is there any other way to tell a query to use a HINT that you know of?

-OFG
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
those servers are really fast, and you rarely notice these issues unless its a really huge database having 1000s of transactions per second.At this stage, i would say, with proper indexes, that query should be fine..

Aneesh
0
 
OFGeminiAuthor Commented:
Found solution on my own
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now