SQL2000 Fast Query Slow Insert
-
I am officially confused.
I have a somewhat involved query which despite numerous inner/outer joins and subqueries returns around 2500+ results in under 1 second. This works fine.
When I attempt to INSERT the results of this query into a table the process takes around 2.5 minutes to complete.
I have tried every configuration I could think of with regards to the INSERT destination (Table|Temp_Table|Table_Variable) and played with inclusion and removal of Indexes (Clustered|NonClustered), Primary Keys, Identity fields, etc..
Any suggestions would be greatly appreciated and would probably save what's left of my sanity.
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001) -
Is there a trigger on the table you are inserting into? Are there referential integrity constraints?
And then again, I might be wrong ...
David Webb -
None at all. I stripped it bare trying to eliminate all possible suspects.
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001) -
Is this on a server class machine? Where is your T-Log physically located? How big is your T-log? TempDB? Make sure there is not any virus scanning software running that may be scanning your files.
-
To add to the good advice already posted ...
>> I stripped it bare trying to eliminate all possible suspects.
Does "bare" include dropping all indexes ?
Are there any other concurrent processes using the table being inserted to, i.e. are you potentially blocking, waiting on another process to complete ?
-
Machine: Windows Server 2003 SP1 Intel Xeon CPU 3.06GHz, 3GB of RAM
T-Log: on mapped Drive Array currently 530MB in size (tempdb is 500kb in size).
V-Scan: Norton is on machine but only scans at 3am. Checked logs to confirm. [Nice idea I didn't even consider that]
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001) -
This is a new table created with no indexes/pks/constraints/etc. Originally was a temp table in a stored procedure. Created an actual table for testing in QA. There are no other processes accessing or blocking this.
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001) -
Are all inserts slow? What about updates? Is your t-log the only thing using the mapped array? Is it on a SAN?
-
The T-log and data file are both on the array and other inserts and updates are working normally. But now I'm really confused because suddenly the insert is working fine and nothing has changed. Unless the drive array was being hammered from another server [File or Exchange]; I never checked that.
I guess for now thank you everybody for your help and I'll go over and pester my network admin again.
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001) -
Did you figure out the problem here?
I had a strange query a couple of weeks back on SQL2005 - the query ran fine by itself, but when I added an insert statement before the select, the execution plan cunningly changed to make use of parallelism (introducing some table scans and ridiculous joins
) - and took forever as a result (well, long enough anyway). Using a cursor sorted that out for me (strange idea, but it worked).Jon
-
Continuing with Journeyman's post, try forcing the optimizer to use only non-parallel query plan on the INSERT by using the optimizer hint MAXDOP (I think it is called - check BOL).
You can also try the same query directly in query optimizer as opposed to inside the sproc. Perhaps a recompile is causing some delay?
Another question is once you have the data inserted into a table variable/temp table, does a subsequent INSERT into the real table take another 2-3 mins?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
The insert still seems to be working normally thank you.
With regards to Jon's post I avoid cursors like the plague (whether by misinformation or peer paranoia is open to debate another day; that always seems to spark a bit of discussion..) However it is interesting that it fixed it; I will keep that in mind if this pops up again.
In reference to Kevin's post I have never used optimizer hints so I will certainly look into them to see how they could help here and elsewhere. Also results were the same from SP and QA, and the temp table was the end destination the results are formatted and further filtered (couldn't do it in one query) then returned to application search forms and web pages.
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001) -
'Unless the drive array was being hammered from another server [File or Exchange]; I never checked that.'
Is this on a SAN? If so, you want to make sure that your DB server has its own physical disks/spindles. You do not want to split a physical disk up and give part of it to your DB server and part of it to your file or exchange server. DB servers need their own disks for best performance.
-
Thanks John. I talked to my network guys and confirmed that the database server has it's own disks on the SAN. They also (in a rather untimely manner as I've been asking them since Friday) mentioned that the AX150's primary controller was having problems on Friday with Exchange as well. So this basically was a hardware issue.
In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply