Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BobRoberts
Posting Yak Master
109 Posts |
 Posted - 2012-08-17 : 17:14:04
|
| I'm using SQL version 9.0.5000I have a query that reads a table of 1,200,000+ rows and does LEFT OUTER JOINS to 4 other tables. If I right-click the view, click Design, click New Query, copy and paste the code into a new query and execute it - it takes around 24 seconds to run.But if I instead right-click the view, click Design, and click the Execute SQL button (the red exclamation mark), it takes over 5 minutes to return the same number of rows.When I execute the query from the pasted code (where it takes only 24 seconds), it says "Executing Query" on the left. But when I execute the query directly it says "Retrieving Data" on the right, and on the left it says "1 of xxxxxx", with the number on the right incrementing like an odometer counter.I suppose all queries may be like this - I'm not implying this is the only one where this happens - this is really the first one I've paid attention to. But why the extreme difference? I know the person using the query is complaining of it being slow, but hasn't given any exact time that it takes to run - I assume this person is encountering the 5 minutes rather than the 24 seconds. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-08-17 : 17:16:44
|
| Never execute your queries through there, always do it in a new query window.And are you really returning all of those rows? Or how many is it returning? Can you post the query?Check the execution plan in a new query window. Also check statistics IO and time. I would be complaining about a 24-second query for sure. My systems require sub-second response time. I've got one that requires 99% of queries to complete in less than 300 milliseconds.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2012-08-17 : 17:46:42
|
No. No. No. Re-read what I wrote. They are complaining about 5 minutes, not 24 seconds. They execute it however they execute it - I can't change that. As I stated, both return the same number of rows. Execution plan says nothing - what's it supposed to say? What magical fix could it offer? I ran an analysis and created recommended indexes - that had no effect on the execution time no matter how I ran it.quote:
Originally posted by tkizer
Never execute your queries through there, always do it in a new query window.And are you really returning all of those rows? Or how many is it returning? Can you post the query?Check the execution plan in a new query window. Also check statistics IO and time. I would be complaining about a 24-second query for sure. My systems require sub-second response time. I've got one that requires 99% of queries to complete in less than 300 milliseconds.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-08-17 : 18:29:26
|
| I did read it, and you missed my point. I would be complaining even at 24 seconds. 5 minutes is an absolute eternity, 24 seconds is not at all good either.Run SQL Profiler and add the showplan XML to it. While it's running, reproduce both scenarios and compare the execution plans. Show us the query if you really want us to help. If we can get it to be efficient, which it currently is not at even 24 seconds, then it should be okay even executing it the "bad way".And what do you mean by the execution plan saying nothing? I'm not sure that you understand what I am saying when I say execution plan as it'll always show something.Add this to your new query script: SET STATISTICS IO ON SET STATISTICS TIME ONShow us the output after you execute it. We don't need to see the data, I need the stats information. I am sure you've got very high reads due to missing indexes or inefficient code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-17 : 18:30:24
|
| Tara offers solid advice...Your users are executing through SSMS design view? That seems odd.I resolve SQL Performance problems for a living, and Execution Plans say a lot.-Chad |
|
|
|
komkrit
Yak Posting Veteran
60 Posts |
Posted - 2012-08-18 : 00:11:00
|
| To BobRoberts,When you execute sql in the view-design pane, it return result in editable mode.But execute in the "New Query" window, it return result in readonly mode.The difference is that SSMS have to build UI for editable(you can modify data at any grid cell) so it have a lot of thing to do that and also consume hugh resource.You are good luck that your data has only million rows.Imagine when your data has billion row, how would it be?So do not try to execute in view-design pane unless your view is small enough.In your case, the right execution time would be 24 seconds in "New Query" window in SSMS.But as Tara said(and I greee 24 seconds is too long).I assume that your client does not need to read row by row of million of records.If your client do that, they would take hours of human to read it all.Try to put SELECT TOP or WHERE CLAUSE to filter out what exatly they need to read.But if your client need execatly all of records, let it be, 24 seconds is fair time for reading million of records.Good LuckKomkrit- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2012-08-20 : 09:10:46
|
Thanks for all the responses. The client clarified, and said that it took 15 minutes when they ran the view as part of a query with a date filter that returns only 90 rows. When I ran the view to test it on my development server, it was in Design mode, and that must have slowed it down. I incorrectly assumed that the slowness I encountered was of the same nature as the slowness the client reported. They then gave me the exact query they ran, and at that point I should have run it on the production server as a basis for comparison, but instead ran a script to create keys (which the Database Engine Tuning Advisor recommended). Then I ran their query on the production server, and it took 4 seconds! So maybe it's how they are connecting or something else. I am logging in as sa, but they have a login that gives them only read permissions - I wonder if that would cause the difference. Anyway, I took a screen shot of the query results and time spent and they will have to report if they are still experiencing the delay - so it's a big mystery at this point.quote:
Originally posted by komkrit
To BobRoberts,When you execute sql in the view-design pane, it return result in editable mode.But execute in the "New Query" window, it return result in readonly mode.The difference is that SSMS have to build UI for editable(you can modify data at any grid cell) so it have a lot of thing to do that and also consume hugh resource.You are good luck that your data has only million rows.Imagine when your data has billion row, how would it be?So do not try to execute in view-design pane unless your view is small enough.In your case, the right execution time would be 24 seconds in "New Query" window in SSMS.But as Tara said(and I greee 24 seconds is too long).I assume that your client does not need to read row by row of million of records.If your client do that, they would take hours of human to read it all.Try to put SELECT TOP or WHERE CLAUSE to filter out what exatly they need to read.But if your client need execatly all of records, let it be, 24 seconds is fair time for reading million of records.Good LuckKomkrit- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
|
|
|
|
komkrit
Yak Posting Veteran
60 Posts |
Posted - 2012-08-20 : 11:51:01
|
| Returning 90 rows from a million of rows, it should be executed in milliseconds if proper index(es) have been created.Could you please run following command in SSMS query window and copy result to us.It will capture execution plan and reveal the part of query that make it slow.----------------------SET SHOWPLAN_XML ONGO<paste your query , e.g. SELECT * FROM yourview where datecolumn = 'xx/xx/xxxx'>----------------------run your query and click the result, it will popup window, post back XML plan here.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulWelcome for all questions. Let us know if our solution solved your problem. |
|
|
|
|
|
|
|
|
Execution Time - Difference Between View and Query