• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1438
  • Last Modified:

Query executes quickly in SQL Server Management Studio, but slow in .NET application

Hi Experts,


I have a query that executes quickly in SQL Server Management Studio, but slow in my .NET application.


How can I get the query to execute in my .NET application as quickly as in SSMS?

below is my query.  I execute this and it is quick on SSMS.


   declare   @UserGuid uniqueIdentifier
   declare   @RoleGuid uniqueIdentifier
   declare   @Guid uniqueIdentifier
   declare @SubjectType tinyint
   set @UserGuid = '6e4ba8ab-0141-41a6-ac13-e5bfd58d4396'
   set @RoleGuid = '23d2a48f-558d-4014-84b1-91eed5898535'
   set @Guid = '4aeee189-1062-4134-ad7c-e118f0cff115'
   set @SubjectType = 1
   SELECT DISTINCT [Guid],                                   
   [TargetGuid],                                      
   [Created],                                        
   [Modified],                                        
   [OriginalName],                                    
   [Lft],                                             
   [Rgt],                                             
   [Length],                                          
   [Pages],                                           
   [IsLatestVersion],                                 
   [Title]                                       
   FROM [dbo].[FsDisplayNodes] d                     
   LEFT JOIN [dbo].[SecFsViewRestrictions] r       
   ON (r.OnFsNode = d.Guid                                 
   AND r.OnTrustee = @UserGuid)                  
   LEFT JOIN [dbo].[SecFsViewRestrictions] r2      
   ON (r2.OnFsNode = d.Guid                                  
   AND r2.OnTrustee = @RoleGuid)              
   WHERE [ParentGuid] = @Guid                          
   AND [SubjectType] & @SubjectType <> 0              
   AND r.[OnTrustee] is NULL                          
   AND r2.[OnTrustee] is NULL                   

Open in new window


Thanks,
OFGemini
0
OFGemini
Asked:
OFGemini
  • 6
  • 5
  • 2
  • +2
1 Solution
 
winston33Commented:
Are you running this query within a stored procedure from your .NET app?
0
 
OFGeminiAuthor Commented:
fyi, the above is dynamic sql. I generate that in code and execute it in a DataReader.
0
 
winston33Commented:
Have you monitored SQL Server using SQL Server Profiler to determine how long your query is actually running on the server? Is the SQL Server remote from the website or running on the same machine?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Gemini,

How much data is being return?


Kent
0
 
TempDBACommented:
Try

Set arithabort off
go

sp_recompile <stored procedure>
0
 
OFGeminiAuthor Commented:
winston33,
Same machine.  Do you mean when the query when it runs from the app or in SSMS?  from SSMS its  225ms for 5000 rows.  in SSMS in my app its 9 seconds 5000 rows.

Kdo,
Its depends and the parameters I have in, but I can get as few rows or up to 5000-6000 results w/ this query.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Gemini,

Could it be a parallel/serial issue?  

When you run SSMS, I'm assuming that it's on the same network or even the same machine as the database.  Moving 5,000 rows on a 1GB network is pretty fast.

When you run the application, are you running on your intranet or are the results going out to the internet?  Returning 5,000 rows to a .NET application on the internet could easily take several seconds.


Kent
0
 
winston33Commented:
As Kent mentions, the lag in speed is likely .NET processing the rows. Running SQL Server Profiler on the query (when run from the app) will let you know exactly how long SQL Server is taking to process the query.

Is the 9 second time you reference the time it takes for the page to load? Which control are you loading the results into? The primary reason for slow processing in ASP.NET is the page size you are attempting to load. There are many ways to limit that once we determine the real issue here.
0
 
OFGeminiAuthor Commented:
9 seconds is the time it takes the datareader to finish reading 5000 rows.  I populate some business objects during that time.

here's a code snippet of what I'm doing.

            using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {

                    if (!bulkHintCalculated)
                    {
                        bulkHint = (int)((reader.GetInt64(6) - reader.GetInt64(5) + 1) >> 2); //rgt - lft
                        bulkHintCalculated = true;
                    }
                    var node = new FileSystemNodeInfoEx(
                            reader.GetGuid(0), //guid
                            reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1), //targetGuid
                            types, //subjectType

                            reader.GetDateTime(2), //created
                            reader.GetDateTime(3), // modified
                            reader.IsDBNull(4) ? string.Empty : reader.GetString(4), //originalFileName

                            bulkHint, //BulkHit
                            reader.IsDBNull(7) ? 0 : reader.GetInt64(7), //size
                            reader.IsDBNull(8) ? 0 : reader.GetInt32(8), //pageCount

                            reader.IsDBNull(9) ? true : reader.GetBoolean(9), //islatest
                            reader.GetString(10)); //Title
                    children.Add(node);
                }
            }

Open in new window

0
 
winston33Commented:
What are you then doing with the data?
0
 
OFGeminiAuthor Commented:
I'm then binding the data to a control in my WPF application.  that above code takes several seconds.
0
 
winston33Commented:
Have you tried simply binding the reader to the control without looping through each row?
0
 
OFGeminiAuthor Commented:
I updated my xaml to allow virtualization and updated my query to be more efficient.
0
 
Anthony PerkinsCommented:
i solved the issue on my own.
Wow!  Not even thanks for the help.  Truly amazing.
0
 
OFGeminiAuthor Commented:
i solved the issue on my own.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now