Solved

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

Posted on 2012-03-26
15
1,111 Views
Last Modified: 2012-06-21
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
Comment
Question by:OFGemini
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 2

Expert Comment

by:winston33
ID: 37766147
Are you running this query within a stored procedure from your .NET app?
0
 

Author Comment

by:OFGemini
ID: 37766150
fyi, the above is dynamic sql. I generate that in code and execute it in a DataReader.
0
 
LVL 2

Expert Comment

by:winston33
ID: 37766201
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
 
LVL 45

Expert Comment

by:Kdo
ID: 37766207
Hi Gemini,

How much data is being return?


Kent
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37766355
Try

Set arithabort off
go

sp_recompile <stored procedure>
0
 

Author Comment

by:OFGemini
ID: 37766373
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
 
LVL 45

Expert Comment

by:Kdo
ID: 37766395
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 2

Expert Comment

by:winston33
ID: 37766453
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
 

Author Comment

by:OFGemini
ID: 37766506
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
 
LVL 2

Expert Comment

by:winston33
ID: 37766606
What are you then doing with the data?
0
 

Author Comment

by:OFGemini
ID: 37766677
I'm then binding the data to a control in my WPF application.  that above code takes several seconds.
0
 
LVL 2

Expert Comment

by:winston33
ID: 37766698
Have you tried simply binding the reader to the control without looping through each row?
0
 

Accepted Solution

by:
OFGemini earned 0 total points
ID: 37851543
I updated my xaml to allow virtualization and updated my query to be more efficient.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37853930
i solved the issue on my own.
Wow!  Not even thanks for the help.  Truly amazing.
0
 

Author Closing Comment

by:OFGemini
ID: 37875036
i solved the issue on my own.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now