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.
| Author |
Topic |
|
herbey
Starting Member
4 Posts |
 Posted - 2009-12-17 : 16:43:51
|
| Hi,When I run a simple select statement(returns 450 rows and like 30 columns), the query completes in about 4 seconds. When I try to insert the results into a table, the query takes about 2 minutes.I tried creating the table first and the "Into" statement. I ran the profiler and the results are in the following table: with into statement without intocpu 97766 3235reads 2465 630writes 60 0duration 97545 3272clientprocessid 2148 2148spid 58 58starttime 21:36.4 27:59.9endtime 23:14.0 28:03.2It is interesting that after the first time the "into" runs taking about 2 minutes, the subsequetn executions of the same query (select * into myTable from anotherTable), take no time. It is like if SQL Server was caching the execution plan. I have no problem with this, but why would it take sooo long in the first execution. Needless to say, this simple query is timing out my web application.I tried with temp tables as well as normal tables. When trying with tempdb, I moved the tempdblog and tempdb to several locations to se if that made a difference.This has never happened, I recently migrated all my queries from sql server 2005 into 2008, I configured the server the same way.A final note, the table I'm querying is actually a view that queries other views, moderately complex but again, when I query it qithout inserting the results into a table, it executes almost imediately.And yes, I do need to have a temporary table because after it is generated I use it to do further joins with additional datasets. As a matter of fact, this same query used to execute immediately when I was using SQL Server 2005.Why would the insert be so slow?Thank you for your help |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-17 : 20:01:25
|
| That's how it works. SQL Server has to build an execution plan the first time it runs and subsequent ones can use that same plan in cache as long as the plan hasn't been aged out of cache. This is true of data too. In order to help with your insert and select, we'd need to see the code including the view definition as well as indexes on those tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
herbey
Starting Member
4 Posts |
Posted - 2009-12-18 : 09:40:41
|
| Ok, I'll look for that, just as additional info, these are some modifications I did:sp_configure 'show advanced options', 1RECONFIGURE--GOsp_configure 'awe enabled', 1RECONFIGURE--GOsp_configure 'min server memory', 1024RECONFIGURE--GOsp_configure 'max server memory', 3072RECONFIGURE--GO |
|
|
|
herbey
Starting Member
4 Posts |
Posted - 2009-12-18 : 09:41:27
|
| thee were done prior to having this problem, I wonder if setting the server memory is being problematic |
|
|
|
herbey
Starting Member
4 Posts |
Posted - 2009-12-18 : 13:37:07
|
| I think I found the problem Tara, as you pointed out, it might be the some of the views being queried. It turns out that some of the base tables have nulls in what it is supposed to be foreign key, which of course is not being enforced. When it comes to querying those tables, SQL Server behaves in an weird way. When you look at the execution plan, you see that the expected rows value is inconsistent and it takes longer, will get rid of those null values at once and update about my progress |
|
|
|
|
|
|
|
|
insert VERY slow