Solved

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

Posted on 2012-03-26
15
1,149 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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