Changing from INSERT INTO...SELECT (with a UNION) to SELECT INTO

Question

Post reply

Changing from INSERT INTO...SELECT (with a UNION) to SELECT INTO

Viewing 3 posts - 16 through 17 (of 17 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

good point Reobert...getting back on topic....

your procedure...does it have defaults on any of the parameters? I would bet that is why it eats cpu cycles...

parameter sniffing.

an execution plan gets created when the procedure is created,as everyone probably knows.

When the values are not defaulted to null, it uses the statistics on the columns being compared to the parameters for how granular the column's values are for uniqueness, and creates a plan based on that info.

When the parameters are defaulted to NULL (or any value, technically)

the SQL engine makes an assumption that since the default values are NULL, the best execution plan should assume the NULL values are in place., and builds a plan without peeking at the statistics at all.

The problem is that when you call the proc with real parameters, the cached plan with those NULL defaults is not suitable to get the data, and SQL goes off on a tangent trying to get the data; personally i always assumed that a bad plan due to parameter sniffing ends up recursively using the bad plan on a per-row basis, so big MillionRowTables get the same plan called a million times, hence the huge time difference...whether that assumption is really true or not, the results are the same... it takes too long.

search for parameter sniffing to learn more, but the two general fixes are:

use the WITH RECOMPILE option so the proc recompiles each time it is called... or assign local variables to the actual values being passed to the procedure.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Robert Frasca (9/14/2010)
In this case, I'm simply trying to switch from a fully logged "INSERT INTO..SELECT" to a minimally logged "SELECT INTO...".

Typically, how many rows are returned by the SELECT?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 3

Chris Howarth-536003 (9/16/2010)
You may see an improvement by adding the OPTION (RECOMPILE) query hint to the problem query - this will cause the optimizer to recompile the statement with knowledge of the values of the parameters.
No guarantees that it will help in this case, but definitely worth a try if you are still suffering performance problems. I'd recommend that you try it out in a test environment first.
Chris

Winner, winner, chicken dinner. 😀

I'm afraid that I, gulp, ASSUMED, that it was getting recompiled. When I dropped that query hint in it started to fly. Well, fly as much as a POS query can fly. Lesson learned...

Thanks Chris and everyone else for your help.

"Beliefs" get in the way of learning.