September 15, 2008 at 3:42 pm
...and again (like Grant was trying to say) - if you were to replace #AD In Lynn Pettis' example above with the query you use to create it, you wouldn't need the temp table (your original request).
The big question in my mind is - why? Sometimes building a temp table and immediately using it, then tearing it down when done, is much much faster than trying to execute one single, huge and highly complicated query that chokes the server. If the query is complicated enough, chances are damn good that SQL will create worktables (i.e. temp tables all over again) of the interim results anyway.
It's a matter of testing to see which one is better. You may find that one query is NOT better than 3.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2008 at 2:15 pm
Lynn, thanks for the great feedback. I am considering this question answered, but I wanted to give Matt and Grant a word...
Matt Miller (9/15/2008)
...and again (like Grant was trying to say) - if you were to replace #AD In Lynn Pettis' example above with the query you use to create it, you wouldn't need the temp table (your original request).
Both you and Grant are extremely accomplished, so I figure I am missing something here.
From where I'm sitting, you both are correcting answering the wrong question. If I needed to remove all three temp tables (#AD, #AFD, and #TempTable) from the queries in both code blocks, I would need to replace #AD and #AFD with the query used to populate them.
In my case, I am not really creating temp tables #AD and #AFD and populating them with a query. The only reason I presented the first query to create and populate those two temp tables was for demonstration purposes, so that the kind folks who answer my post would be able to test their recommended query against a setup: test tables with test data. That is why I labeled the code blocks in my original post as (THIS SETUP SHOULD HELP YOU HELP ME WITH MY QUESTION) and (MY QUERY STARTS HERE).
As I am writing this, I realize now that should have reversed the two code blocks. In the future, I plan to present the code block with my questionable query first, and then follow it up with the setup for the query. That change should help my future posts be more clear.
Matt Miller (9/15/2008)
The big question in my mind is - why? Sometimes building a temp table and immediately using it, then tearing it down when done, is much much faster than trying to execute one single, huge and highly complicated query that chokes the server. If the query is complicated enough, chances are damn good that SQL will create worktables (i.e. temp tables all over again) of the interim results anyway.
Excellent question, Matt. I am using the query inside a stored procedure, which is referenced in the SelectCommand in a .NET TableAdapter object. Because the Configure... wizard (in VS2005 Designer) cannot resolve temp tables, I needed to replace the one temp table named #TempTable with something else that would also do the job. If I could not do this, I would need to hack the Designer code, which would create a maintenance time bomb -- any developer using the user-friendly Designer after me would have to somehow *just know* that using the Configure... wizard would remove references to all stored procedures without alerting you.
Or you can read: blah blah blah application developer stuff. 😀
Paul DB
November 26, 2008 at 3:29 pm
Paul DB (11/26/2008)Excellent question, Matt. I am using the query inside a stored procedure, which is referenced in the SelectCommand in a .NET TableAdapter object. Because the Configure... wizard (in VS2005 Designer) cannot resolve temp tables, I needed to replace the one temp table named #TempTable with something else that would also do the job. If I could not do this, I would need to hack the Designer code, which would create a maintenance time bomb -- any developer using the user-friendly Designer after me would have to somehow *just know* that using the Configure... wizard would remove references to all stored procedures without alerting you.
Or you can read: blah blah blah application developer stuff. 😀
Or, you could try using SET FMTONLY OFF; in your stored procedure. One thing the TableAdapter is probably doing is running the stored procedure with format only to return the metadata (i.e. columns).
Setting the format only off option - you force the procedure to always run to completion to return metadata. One caveat - if the procedure takes a long time to run it will take a long time to validate the TableAdapter.
If this works - make sure you put some good documentation in the procedure so the person following you doesn't remove it later.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 27, 2008 at 5:45 am
Jeffrey Williams (11/26/2008)
Or, you could try using SET FMTONLY OFF; in your stored procedure. One thing the TableAdapter is probably doing is running the stored procedure with format only to return the metadata (i.e. columns).
Setting the format only off option - you force the procedure to always run to completion to return metadata. One caveat - if the procedure takes a long time to run it will take a long time to validate the TableAdapter.
If this works - make sure you put some good documentation in the procedure so the person following you doesn't remove it later.
I know I focused on the wrong thing and wasn't helpful in initially answering the question, but I'd like to re-emphasize this point. I've worked with data access frameworks that whittle away the ability to use common objects within TSQL queries and procedures such as the temporary tables mentioned above. While I understand and agree with making developers lives easier and the development process faster and easier, doing it by sacrificing functionality on the database seems more than a little bit foolish. My experience has been less than rewarding in this space. Our particular framework couldn't handle temporary tables, table aliases, output parameters and a couple of other things I forget at the moment. Maintaining database code became much more difficult. More importantly, performance tuning was made more difficult and time consuming. Cost savings in development were lost in the long run.
I wasn't aware of this setting prior to now, but this is one I can take back to the office and put to work. I'd strongly suggest this be the solution, not the elimination of the use of temporary tables in all queries.
"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
December 1, 2008 at 7:58 am
Grant Fritchey (11/27/2008)
Jeffrey Williams (11/26/2008)
Or, you could try using SET FMTONLY OFF; in your stored procedure. One thing the TableAdapter is probably doing is running the stored procedure with format only to return the metadata (i.e. columns).
Setting the format only off option - you force the procedure to always run to completion to return metadata. One caveat - if the procedure takes a long time to run it will take a long time to validate the TableAdapter.
If this works - make sure you put some good documentation in the procedure so the person following you doesn't remove it later.
I know I focused on the wrong thing and wasn't helpful in initially answering the question, but I'd like to re-emphasize this point. I've worked with data access frameworks that whittle away the ability to use common objects within TSQL queries and procedures such as the temporary tables mentioned above. While I understand and agree with making developers lives easier and the development process faster and easier, doing it by sacrificing functionality on the database seems more than a little bit foolish. My experience has been less than rewarding in this space. Our particular framework couldn't handle temporary tables, table aliases, output parameters and a couple of other things I forget at the moment. Maintaining database code became much more difficult. More importantly, performance tuning was made more difficult and time consuming. Cost savings in development were lost in the long run.
I wasn't aware of this setting prior to now, but this is one I can take back to the office and put to work. I'd strongly suggest this be the solution, not the elimination of the use of temporary tables in all queries.
Grant and Jeffrey,
In my position, I am responsible for not only applications but also the accuracy and performance of the stored procedures they call. So I wear multiple hats. Naturally I very much appreciate your design feedback. 😎
I am a little puzzled though... I read the online ms documentation for SET FMTONLY. By only returning the metadata, calling SET FMTONLY OFF; before running a stored procedure appears to do nothing more than save time. In other words, why would the returned metadata be different if data is also returned?
Well, I can search online as well as the next developer. So here goes... the first link from the Google search [SET FMTONLY OFF temp tables] reveals this useful article on one of my favorite sites; http://www.sqlservercentral.com/articles/FMTONLY/64130/[/url]. This well rated article by Renato Buda details "How SSIS and Other Tools Obtain Metadata", gives three usual workarounds and one clever unusual workaround.
So thanks to you all my question is answered. Thanks again to all for your excellent feedback. 🙂
Paul DB
December 1, 2008 at 11:25 am
Paul DB (11/26/2008)
Lynn, thanks for the great feedback. I am considering this question answered, but I wanted to give Matt and Grant a word...Matt Miller (9/15/2008)
...and again (like Grant was trying to say) - if you were to replace #AD In Lynn Pettis' example above with the query you use to create it, you wouldn't need the temp table (your original request).Both you and Grant are extremely accomplished, so I figure I am missing something here.
From where I'm sitting, you both are correcting answering the wrong question. If I needed to remove all three temp tables (#AD, #AFD, and #TempTable) from the queries in both code blocks, I would need to replace #AD and #AFD with the query used to populate them.
In my case, I am not really creating temp tables #AD and #AFD and populating them with a query. The only reason I presented the first query to create and populate those two temp tables was for demonstration purposes, so that the kind folks who answer my post would be able to test their recommended query against a setup: test tables with test data. That is why I labeled the code blocks in my original post as (THIS SETUP SHOULD HELP YOU HELP ME WITH MY QUESTION) and (MY QUERY STARTS HERE).
As I am writing this, I realize now that should have reversed the two code blocks. In the future, I plan to present the code block with my questionable query first, and then follow it up with the setup for the query. That change should help my future posts be more clear.
Matt Miller (9/15/2008)
The big question in my mind is - why? Sometimes building a temp table and immediately using it, then tearing it down when done, is much much faster than trying to execute one single, huge and highly complicated query that chokes the server. If the query is complicated enough, chances are damn good that SQL will create worktables (i.e. temp tables all over again) of the interim results anyway.Excellent question, Matt. I am using the query inside a stored procedure, which is referenced in the SelectCommand in a .NET TableAdapter object. Because the Configure... wizard (in VS2005 Designer) cannot resolve temp tables, I needed to replace the one temp table named #TempTable with something else that would also do the job. If I could not do this, I would need to hack the Designer code, which would create a maintenance time bomb -- any developer using the user-friendly Designer after me would have to somehow *just know* that using the Configure... wizard would remove references to all stored procedures without alerting you.
Or you can read: blah blah blah application developer stuff. 😀
Oh - I am a developer as well, which is confusing me all the more. My experience with the wizards is that once I hook them up to their own stored procs, they get left alone (meaning - the stored proc STAYS the source for the data); the only way for that to happen would be for someone to go in a write a direct table access query in its stead. Meaning - the level of effort it takes would then be kind which would never prevent ANYTHING from getting to them.
But I guess it's all in how you use the designer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 1, 2008 at 1:49 pm
Matt Miller (9/15/2008)
The big question in my mind is - why? Sometimes building a temp table and immediately using it, then tearing it down when done, is much much faster than trying to execute one single, huge and highly complicated query that chokes the server. If the query is complicated enough, chances are damn good that SQL will create worktables (i.e. temp tables all over again) of the interim results anyway.
Paul DB (11/26/2008)
Excellent question, Matt. I am using the query inside a stored procedure, which is referenced in the SelectCommand in a .NET TableAdapter object. Because the Configure... wizard (in VS2005 Designer) cannot resolve temp tables, I needed to replace the one temp table named #TempTable with something else that would also do the job. If I could not do this, I would need to hack the Designer code, which would create a maintenance time bomb -- any developer using the user-friendly Designer after me would have to somehow *just know* that using the Configure... wizard would remove references to all stored procedures without alerting you.Or you can read: blah blah blah application developer stuff. 😀
Matt Miller (12/1/2008)
Oh - I am a developer as well, which is confusing me all the more. My experience with the wizards is that once I hook them up to their own stored procs, they get left alone (meaning - the stored proc STAYS the source for the data); the only way for that to happen would be for someone to go in a write a direct table access query in its stead. Meaning - the level of effort it takes would then be kind which would never prevent ANYTHING from getting to them.But I guess it's all in how you use the designer.
I agree that the stored procedure will definitely change when changing the CommandType of the SelectCommand from StoredProcedure to Text and writing a direct access query in its place (and there is nothing any developer could/should do to prevent that). I was under the impression that the designer might *magically* change some code regarding the stored procedure (or any of its parameters, etc.) when the stored procedure contains temp tables because it was displaying warning messages and complaining. Since I can not validate that impression now, I could have spoke incorrectly. Perhaps I was having problems with disappearing parameters when learning about the designer and originally toying with direct access queries to retrieve my information, as I followed the Forms over Data video series. Thanks, Matt, for challenging my assumption. 😉
I had decided (see my 9/10/2008 reply to Steve Jones on page 1) to take his suggestion, so there are no temp tables to play with in my code. But this has been an educational discussion. Thanks again to all.
Paul DB
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply