Rediculous plan for simple insert!

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rediculous plan for simple insert!

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-15 : 10:31:54

I'm doing an INSERT INTO...SELECT statement and even though both tables are indexed like they should I get a freakishly long execution plan! What's going on here? Is this normal?? ->

Insert-->
INSERT INTO sometable (UserID, MarketID, CompID)
SELECT @UserID, MarketID, CompID
FROM someothertable WITH (NOLOCK) 
WHERE MarketID = @MarketID

--> Indexes
sometable     : UserID, MarketID, CompID - clustered
someothertable: MarketID, TableID, CompID

--> Execution plan
|--Assert(WHERE:(If (NOT([Pass1019]) AND ([Expr1018] IS NULL)) then 0...
   |--Nested Loops(Left Semi Join, WHERE:...
      |--Nested Loops(Left Semi Join, WHERE:...
      |  |--Nested Loops(Left Semi Join, WHERE:...
      |  |  |--Clustered Index Insert(OBJECT:...
      |  |  |  |--Top(ROWCOUNT est 0)
      |  |  |     |--Compute Scalar(DEFINE:([Expr1003]=getidentity(988634665, 7, NULL)))
      |  |  |        |--Clustered Index Seek(OBJECT:...
      |  |  |--Row Count Spool
      |  |     |--Clustered Index Seek(OBJECT:...
      |  |--Row Count Spool
      |     |--Clustered Index Seek(OBJECT:...
      |--Row Count Spool
         |--Index Seek(OBJECT:...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-15 : 10:47:35

remember that indexes on the destination table actually slow down INSERTS. Especially clustered indexes since the entire table must be moved around during the insert because the clustered index dictates how the entire table is physically stored.

you need to determine the bottleneck -- it is the SELECT or the INSERT. Just do a standard SELECT with your SQL and see how fast it returns the rows to be inserted (i.e., lose the INSERT INTO part). Then compare that to the SELECT with the INSERT.

- Jeff

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-15 : 10:54:58

What I pasted here is the actual insert/select...there is nothing more to it and doing the select only returns a single clustered index seek. I'm confused...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-15 : 11:27:15

Again, how long does the SELECT alone take versus the INSERT/SELECT.

Do you understand that indexes on a destination table causing things to take LONGER because they must be updated/maintained during the insert? You need to determine the bottleneck of why this is slow. It can be the SELECTing of the records, or it can be the INSERTing. Does this make sense?

- Jeff

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-15 : 11:29:43

Oh...sorry for misunderstanding. The tables are empty so I'm just checking the plans..developing some brand new stuff and I haven't filled the tables with data yet.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 01:45:30

"Especially clustered indexes since the entire table must be moved around during the insert because the clustered index dictates how the entire table is physically stored."

In my experience SQL adds an ORDER BY, to the plan, to get the data presented in the PK order - so the clustered index insert is not too hard (but I dunno what happens if the operator tries to be smart and puts a different ORDER BY on the SELECT!

Where are those 3 LEFT SEMI JOINs coming from?

With my new-found knoweldge! Do you want the NOLOCK hint? You'll get data that is in the "queue" but which is subsquently rolled back ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources