?
Solved

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

Posted on 2012-03-26
15
Medium Priority
?
1,323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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