May 31, 2016 at 2:59 pm
I just noticed that query I run in the Query Designer runs faster than when I run it in a query window in SSMS. From my observation, the query in the Query Designer took less than a minute to run while the same query took almost three minutes to run in a query window.
Has anyone observed this?
Thanks,
MC
May 31, 2016 at 3:44 pm
I never use query designer, because I hate the code that it writes.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 31, 2016 at 4:01 pm
No. All things being equal, there would be no reason why it run faster in either location. So, the question is, what's different? Are you capturing execution plans or client statistics or something like that in the query window? Do you have different ANSI settings for the connection you're testing from?
I too don't use the query designer at all. The query it creates executes within the normal SSMS window, so there has to be something else going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 31, 2016 at 4:13 pm
Isn't the number of rows restricted in query designers?
Igor Micev,My blog: www.igormicev.com
June 1, 2016 at 8:03 am
Both queries returned the same number of records (136458). I captured the actual execution plan of both queries as well. At a quick glance, they both look identical. Files attached. The query execution in the query window still takes over two minutes and 30 seconds to complete while the Query designer takes about one minute.
What am I missing?
Thanks,
MC
June 1, 2016 at 8:11 am
I normally do not use the Query Designer. I am only using it because I am in the process of moving an Access database to SQL and was looking for a way to make it easier for the user/designer of the Access database to easily transition to SQL and using SSMS. I used SQL Server Migration Assistance for Access in converting the Access Database to SQL. It converted some to the queries from Access to views. I want the owner of the database to be able to re-write the queries not converted (mostly update and delete queries) themselves. So I started looking into the Query Designer because it gives them some of the point and click functionality in Access.
In testing some of the queries, I noticed that this particular one completes faster in the Query Designer window than in SSMS query window.
Thanks,
MC
June 1, 2016 at 8:40 am
Hi,
There are differences in the execution plans: Degree of parallelism, Memory Grant, Set options.
Igor Micev,My blog: www.igormicev.com
June 1, 2016 at 8:50 am
Igor Micev (6/1/2016)
Hi,There are differences in the execution plans: Degree of parallelism, Memory Grant, Set options.
Other than arithabort, the remaining differences - as far as I can see - are attributable to one of them being an estimated plan, the other actual.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 1, 2016 at 9:00 am
Thanks Igor for the reply. I noticed the differences as well and this is where I need help and a bit confused. Does this mean that SQL is using different execution plan when the same query is run from the designer and query window? The SSMS query window plan has Degree of Parallelism and Memory Grant and Query Designer plan does not have it with the other differences you pointed out. I was expecting the query executed in the query window to be faster and it is not.
Thanks,
MC
June 1, 2016 at 9:07 am
With one being estimated and one being actual, I'm inclined to say these plans are identical, but I'd want to be sure before I went there. Frankly, I'm not sure what's going on. I'm also a little confused. When I use the Query Designer in my instance of 2014, it doesn't run the query. It spits out to a regular SSMS query window where I can run the query. There's no real difference between running this query and writing a query by hand and then running it. I'm missing something. Is there an extra step or program that I'm not seeing?
All my tests, the results are the same. The query, if run after being built by the Designer, or the query run after being copied and pasted into a new query window, will run exactly the same. In fact, it reuses the execution plan on my instance. I just validated all this (which, is expected behavior). So... what the heck am I missing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2016 at 9:30 am
There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL pane).
Starting a while ago (I think maybe in 2008?), that option is always grayed out.
You can still use that functionality by going to some table, doing the "Edit top..", and then right-clicking and bringing in all the panes. The Query Designer opened that way lets you execute the SQL as mentioned above.
Assuming that's what is being done, the reported durations are still odd, as I seem to recall the overhead of bringing results there being even worse than Results to Grid.
I'd probably first use sys.dm_exec_query_stats to see if there is any difference in the actual work done by each query (CPU and reads), and if not I'd then set up an XEvents session to capture wait stats for each of the two sessions being used to run the queries.
That should be enough to point to the difference.
Cheers!
June 1, 2016 at 9:37 am
Jacob Wilkins (6/1/2016)
There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL pane).
I am running SSMS 2014 and you can still use the "Execute Query" option, which is what I am doing. I will look at using sys.dm_exec_query_stats to see what I find out.
Thanks,
MC
June 1, 2016 at 10:30 am
Jacob Wilkins (6/1/2016)
There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL pane).Starting a while ago (I think maybe in 2008?), that option is always grayed out.
You can still use that functionality by going to some table, doing the "Edit top..", and then right-clicking and bringing in all the panes. The Query Designer opened that way lets you execute the SQL as mentioned above.
Assuming that's what is being done, the reported durations are still odd, as I seem to recall the overhead of bringing results there being even worse than Results to Grid.
I'd probably first use sys.dm_exec_query_stats to see if there is any difference in the actual work done by each query (CPU and reads), and if not I'd then set up an XEvents session to capture wait stats for each of the two sessions being used to run the queries.
That should be enough to point to the difference.
Cheers!
OK. Never used that GUI, so I'm not surprised I don't know it that well. Thanks.
It still doesn't make sense. Except for variation on a single ANSI setting, which could cause problems if it lead to a different execution plan, but it's not, there's no other indication of what's up.
I'm with you, let's see the wait statistics to understand what's happening here. In the mean time, I'm going to go do a little testing, just to see if I can spot a difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2016 at 12:30 pm
Jacob Wilkins (6/1/2016)
There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL pane).Starting a while ago (I think maybe in 2008?), that option is always grayed out.
You can still use that functionality by going to some table, doing the "Edit top..", and then right-clicking and bringing in all the panes. The Query Designer opened that way lets you execute the SQL as mentioned above.
Assuming that's what is being done, the reported durations are still odd, as I seem to recall the overhead of bringing results there being even worse than Results to Grid.
I'd probably first use sys.dm_exec_query_stats to see if there is any difference in the actual work done by each query (CPU and reads), and if not I'd then set up an XEvents session to capture wait stats for each of the two sessions being used to run the queries.
That should be enough to point to the difference.
Cheers!
I was able to test this. I reversed the results. 121k rows returned in 22 seconds using the Designer and less than a second (600ms) using the query window. I tried on both 2014 and 2016. There has to be something else going on. The Designer was exceedingly slow in returning data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2016 at 12:46 pm
Grant Fritchey (6/1/2016)
Jacob Wilkins (6/1/2016)
There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL pane).Starting a while ago (I think maybe in 2008?), that option is always grayed out.
You can still use that functionality by going to some table, doing the "Edit top..", and then right-clicking and bringing in all the panes. The Query Designer opened that way lets you execute the SQL as mentioned above.
Assuming that's what is being done, the reported durations are still odd, as I seem to recall the overhead of bringing results there being even worse than Results to Grid.
I'd probably first use sys.dm_exec_query_stats to see if there is any difference in the actual work done by each query (CPU and reads), and if not I'd then set up an XEvents session to capture wait stats for each of the two sessions being used to run the queries.
That should be enough to point to the difference.
Cheers!
I was able to test this. I reversed the results. 121k rows returned in 22 seconds using the Designer and less than a second (600ms) using the query window. I tried on both 2014 and 2016. There has to be something else going on. The Designer was exceedingly slow in returning data.
Yeah, I was pretty sure I remembered the overhead pulling results to the designer being absurdly high, which is saying something since Results to Grid isn't exactly light.
Good to know my memory's not completely shot 🙂
Cheers!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply