Solved

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

Posted on 2012-03-26
15
1,085 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

20 Experts available now in Live!

Get 1:1 Help Now