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 |
|
mffm
Starting Member
2 Posts |
 Posted - 2008-03-02 : 13:25:29
|
Hi all i'm using sqlserver 2005 this statment take 1:30 min to execute****insert into temotable (select key from table1)if i used a select statment alone it takes 4 sec but with insert statment it take 1:30minby the way i put indexes on the table1plz how i can increase the speed of insert statment. thanks in advance |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-02 : 13:32:48
|
1st question..is, if the table1 column named KEY is in an index, why do you need to insert them into a temp table? I am assuming this is nothing like you actual query..Check the query execution plans for the Select only, and then the Insert Into statements and the results will be informative.what is the actual query, and what is the purpose of inserting the records into a temp table? that may be more of an appropriate angle to take.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
mffm
Starting Member
2 Posts |
Posted - 2008-03-02 : 13:48:14
|
| my real query is insert into temtableForStatics (PRIMEKEY) select PRIMEKEY from fmtable1 where primekey in( select primekey from fmtable2) and marks = 1the select statment is very fast but the insert is slow |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-02 : 14:37:56
|
[code]insert into temtableForStatics (PRIMEKEY) select PRIMEKEY from fmtable1 INNER JOIN fmtable2 on fmtable1.primekey = fmtable2.primekeyand marks = 1[/code]Inner join is faster than a where in anti-pattern.Does your temtableForStatics have any triggers or anything that might also be slowing down the insert?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-03 : 02:08:30
|
| There isn't any information about the regularity of the statement etc, and other application details. But some other stratgeies to look at are using TABLOCK and Simple Recovery ModelJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
|
|
|
increase the speed of insert statment