Problem with "Big" insert Statement in SQL Ser2005

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with "Big" insert Statement in SQL Ser2005

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-23 : 11:23:49

Hi

i wrote a sp which fills a giant table with 80 columns
the data comes from more than 20 tables.i need this table to generates reports

example:

insert into tableA values (1,2,3,..........80) select (tableB.1*tableC.1,............)
from tableD .........(Joins on 20 tables)
Where (some condition)

This query works fine on sql server 2000 it takes around 4 mins....to fill the table
While i try to run the same sp on SQL server 2005..the sp never stops executing....
i have to manually break the operation.....what might be the problem...?
i think there is a problem with locks....when i go into the management -->activity monitor in sql server 2005 i see there are no locks before i run the sp but when i start the sp hundreds of locks are getting created..

plz help solve this problem ,i need to make the sp run on sql server 2005 and schedule a job making the sp run every 2 hours.....

if need any more info i will provide u..waiting for ur replys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 11:27:53

For a starter, use brackets leading and trailing bracket for column names starting with numeric value.
How are your indexes?

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-23 : 11:28:02

Try building up the data in temp tables doing less joins for each one. That will should you where the problem is and allow you to optimise moer easily.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 12:20:34

How did you migrate to SQL 2005?

Did you change the compatbility mode and rebuild all indexes etc? If not that's probably the only issue.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Migrating+to+SQL+2005+Hints+and+Tips

Kristen

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-23 : 13:41:27

quote:
Originally posted by Peso

For a starter, use brackets leading and trailing bracket for column names starting with numeric value.
How are your indexes?

E 12°55'05.25"
N 56°04'39.16"

i have given all the columns leading and trailing brackets...but no change
and coming to the indexes i have created 4 non clustered indexes on the table
i have generated the scripts in sql server 2000 for creating table with 80 columns and the sp to insert data into that table.I ran the same scripts in sql server 2005 to create the table and insert data into it.is it a problem do i need to change any thing in the scripts to make it compatible with 2005..?

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-23 : 13:44:09

quote:
Originally posted by Kristen

How did you migrate to SQL 2005?

Did you change the compatbility mode and rebuild all indexes etc? If not that's probably the only issue.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Migrating+to+SQL+2005+Hints+and+Tips

Kristen

i have both instances of sql server that is 2000 and 2005 on different machines..i created the table and sps in 2000 and generated scripts for them which i ran in 2005...is that a problem...?

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 13:47:21

"is that a problem...?"

Well, sounds like you will have created a database with latest Compatibility Mode (but worth checking I suppose), but does not sound like you have done any Index Rebuild or Update Statistics etc. since data import. If not that would most likely make a significant difference.

Have a read of the link.

(Some reason not to just restore a Backup of the SQL2000 database?)

Kristen

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-23 : 13:47:42

quote:
Originally posted by nr

Try building up the data in temp tables doing less joins for each one. That will should you where the problem is and allow you to optimise moer easily.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

i have not tried it this way,may be i need to try ur suggestion
but my question is it works fine in 2000
even in 2005 out of 10 times the sp executes 1 time and takes less time than in 2000...

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-23 : 14:04:21

quote:
Originally posted by Kristen

"is that a problem...?"

Well, sounds like you will have created a database with latest Compatibility Mode (but worth checking I suppose), but does not sound like you have done any Index Rebuild or Update Statistics etc. since data import. If not that would most likely make a significant difference.

Have a read of the link.

(Some reason not to just restore a Backup of the SQL2000 database?)

Kristen

sql server 2000 is on my local instance and sql server 2005 is the production environment,so every thing else works fine on 2005..(i guess Index Rebuild or Update Statistics etc are done on 2005 ,i personally am not a DBA,i am a .net programmer)
for that matter few other small sps' which i wrote in 2000 are working fine 2005 without creating any locks or hurdles

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 14:10:03

"i guess Index Rebuild or Update Statistics etc are done on 2005 "

Making that assumption it is VERY odd that things running find under SQL 2000 are running dog-slow under SQL 2005.

I've made the suggestion. You can follow it up your end if you like.

Kristen

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-23 : 14:21:39

quote:
Originally posted by Kristen

"i guess Index Rebuild or Update Statistics etc are done on 2005 "

Making that assumption it is VERY odd that things running find under SQL 2000 are running dog-slow under SQL 2005.

I've made the suggestion. You can follow it up your end if you like.

Kristen

ya i will sure follow it up and will let u know whether index rebuilding and update statistics are done...

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-24 : 02:58:48

quote:
Originally posted by Kristen

"i guess Index Rebuild or Update Statistics etc are done on 2005 "

Making that assumption it is VERY odd that things running find under SQL 2000 are running dog-slow under SQL 2005.

I've made the suggestion. You can follow it up your end if you like.

Kristen

i have rebuild all the indexes , updated statistics and changed comparability mode to sql server 2005.....the problem still exists.......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 03:01:41

What if you do the other suggestion made to you?
Backup the database and restore it on the SQL Server 2005 instance?
And then run the query?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-24 : 04:32:16

"i have rebuild all the indexes , updated statistics and changed comparability mode to sql server 2005"

Hopefully you changed to SQL 2005 Compatibility (90) BEFORE doing the index rebuild / statistics updates

Kristen

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-10-24 : 09:30:23

quote:
Originally posted by Kristen

"i have rebuild all the indexes , updated statistics and changed comparability mode to sql server 2005"

Hopefully you changed to SQL 2005 Compatibility (90) BEFORE doing the index rebuild / statistics updates

Kristen

ya i did exactly the same thing first changed the mode to sql 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources