Tuning

Question

Tuning

mike 57299

SSCrazy

Points: 2529
More actions
June 23, 2010 at 3:00 pm

#226063

Hi All:
I am trying to use the Database Tuning Advisor on a set of statements. The set goes something like this:
SELECT order_hdr_id, customer_id, order_date, amount, city, state, zip INTO #Open From Order WHERE ship is null
SELECT customer_name, customer_id,
order_hdr_id, order_num
FROM #Open
inner join Customer on #open.customer_id = Customer.customer_id
The Database Tuning Advisor can't figure it out. It seems to have an issue with temporary tables.
Any ideas?
Thanks,
Mike

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 1

mike 57299 (6/23/2010)SELECT order_hdr_id, customer_id, order_date, amount, city, state, zip INTO #Open From Order WHERE ship is null

Assuming script is creating #Open temp table, how about...

insert into #Open

select order_hdr_id, customer_id, order_date, amount, city, state, zip

from Order

where ship is null

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

mike 57299 (6/23/2010)
Hi All:
I am trying to use the Database Tuning Advisor on a set of statements. The set goes something like this:
SELECT order_hdr_id, customer_id, order_date, amount, city, state, zip INTO #Open From Order WHERE ship is null
SELECT customer_name, customer_id,
order_hdr_id, order_num
FROM #Open
inner join Customer on #open.customer_id = Customer.customer_id
The Database Tuning Advisor can't figure it out. It seems to have an issue with temporary tables.
Any ideas?
Thanks,
Mike

Correct... the Tuning Advisor cannot see temp tables because they're usually created in another session. You'll need to make the table materialize while you're trying to do the analysis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

mike 57299 SSCrazy Points: 2529 More actions · Answer 3

Jeff Moden (6/23/2010)
Correct... the Tuning Advisor cannot see temp tables because they're usually created in another session. You'll need to make the table materialize while you're trying to do the analysis.

How do you make the table materialize? Does this mean making it a real table? If so, I have a problem because I use two temp tables through out the script I would like to optimize.

Mike

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Yes, convert them to real tables or don't use DTA (second option may be best). If it's a small set of queries, ask here. http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

DTA is usually overly enthusiastic about indexes and stats. If you do use it, please check the effectiveness of each recommendation and only apply the ones that really do help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

mike 57299 (6/23/2010)
Jeff Moden (6/23/2010)
Correct... the Tuning Advisor cannot see temp tables because they're usually created in another session. You'll need to make the table materialize while you're trying to do the analysis.
How do you make the table materialize? Does this mean making it a real table? If so, I have a problem because I use two temp tables through out the script I would like to optimize.
Mike

Like Gail said, "Yes"... turn them into permanent tables BUT... only temporarily. If you change "#" to "TempDB.dbo." using search'n'replace, you should be ok and, of course, that's pretty easy to change back to a "#" when you're done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

mike 57299 SSCrazy Points: 2529 More actions · Answer 6

I have been doing some performance testing and I am beginning to think temp tables aren't so hot. I have one area where I insert into the table 1 record. I wanted to see what was in it and I did a "select * from #I". That line took 4 seconds to execute. I think it might be a caching issue. Anyone else have issues with temp tables?

Mike

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 7

I have had issues in the past, but I don't see a lot of issues reported in the last few versions of SQL Server.

tempdb is a shared resource, and the disks underlying it, and other uses do impact your use. Depending on how your load is on the server, it may or may not be an issue. It sounds like it is on your system.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com