insert..select

I have an insert into select from statement that sometimes performs HORRIBLY
on SQL Server. I did some tests. This is what I found.
INSERT INTO target
SELECT top 500 *
FROM source
executes in less than 1 second.
INSERT INTO target
SELECT top 501 *
FROM source
executes in 140 seconds!
What is up? How can I get respectable performance from this database.
I am COMPLETELY at a loss why the number 500 is magic.
--
Keith WilliamsHi,
This will not happen. I feel that first one is read from memory. Try
executing the below:-
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 500 *
FROM source
-- Now
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 501 *
FROM source
Thanks
Hari
MCDBA
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs
HORRIBLY on SQL Server. I did some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>|||Perhaps this is the line where the optimizer decides to not use an index vs.
using the index. Check
the execution plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs HORRIBLY on SQ
L Server. I did
some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>|||The execution plan is vastly different. I had looked at it already and assum
ed that was obvious. My questions are
1) Why does the excution plan change at such a low threshold? The plan for 5
01 rows to insert would be fantastic if I was inserting at least 5000 rows,
maybe more like 50,000 rows.
2) How can I influence the plan? I have tried all the hints, even those I kn
ew wouldn't work. I am dissapointed that the FIRSTROWS hint doesn't have an
affect.
Since I posted this I have gotten much better performance by replacing the b
ind variables in the actual SQL statement with hard coded parameters. SQLSer
ver now makes a better estimate of the number of rows being inserted.
The actual SQL is much more complicated than the trivial example because it
is joining several tables, the target is clustered in a non-optimal fashion
for the insert, and there is an after trigger on the target table.
Real world, real issues. This same query runs in sub-second on Oracle and DB
2.
--
Keith Williams
"Tibor Karaszi" wrote:

> Perhaps this is the line where the optimizer decides to not use an index v
s. using the index. Check
> the execution plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in messag
e
> news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> some tests. This is what I found.
>
>|||I can think of two things off hand:
Statistics. Did you try to update for involved tables? With fullscan?
Parameter sniffing. Doesn't sound like it, but if you aren't familiar with t
he concept, search the
archives and you should find good explanations on the topic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:6B6AF096-A16A-4BC7-9E78-A993F8E59BCB@.microsoft.com...
> The execution plan is vastly different. I had looked at it already and assumed tha
t was obvious.
My questions are
> 1) Why does the excution plan change at such a low threshold? The plan for 501 row
s to insert
would be fantastic if I was inserting at least 5000 rows, maybe more like 50,000 rows.[vbcol
=seagreen]
> 2) How can I influence the plan? I have tried all the hints, even those I knew wou
ldn't work. I am[/vbcol]
dissapointed that the FIRSTROWS hint doesn't have an affect.
> Since I posted this I have gotten much better performance by replacing the bind va
riables in the
actual SQL statement with hard coded parameters. SQLServer now makes a bette
r estimate of the number
of rows being inserted.
> The actual SQL is much more complicated than the trivial example because it is joi
ning several
tables, the target is clustered in a non-optimal fashion for the insert, and
there is an after
trigger on the target table.[vbcol=seagreen]
> Real world, real issues. This same query runs in sub-second on Oracle and
DB2.
> --
> Keith Williams
>
> "Tibor Karaszi" wrote:
>
Check[vbcol=seagreen]
did[vbcol=seagreen]