Re: insert..select
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/27/04
- Next message: Andrew J. Kelly: "Re: CMEMTHREAD"
- Previous message: clintonG: "Where's A Computer Scientist When You Need One?"
- In reply to: Keith Williams: "Re: insert..select"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 27 Jun 2004 20:41:15 +0200
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 the 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 that was obvious. My questions are > > 1) Why does the excution plan change at such a low threshold? The plan for 501 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 knew 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 bind variables in the actual SQL statement with hard coded parameters. SQLServer 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 DB2. > -- > Keith Williams > > > > "Tibor Karaszi" wrote: > > > 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 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 > > > > > > > > >
- Next message: Andrew J. Kelly: "Re: CMEMTHREAD"
- Previous message: clintonG: "Where's A Computer Scientist When You Need One?"
- In reply to: Keith Williams: "Re: insert..select"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
