Query tuning and Insertion Error

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

General SQL Server Forums

New to SQL Server Programming

Query tuning and Insertion Error

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-05 : 07:24:29

Hi All,

I have got two issues.

1. i am now making an insertion into a table from another database.
i made suitable converstions to make data types compatible. but still the query is not working.
the column size in the remote db is more than that of local db.
now can i use SUBSTRING to extract only a part of data?

2. What are the basic aspects we need to look into while tuning a sql query ?
the query has three insert statements which insert huge amount of data into the table based on few conditions. its running fast on my machine but thru appln it is slower. what can i do now?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-05 : 07:42:34

quote:
Originally posted by mrm23

Hi All,

I have got two issues.

1. i am now making an insertion into a table from another database.
i made suitable converstions to make data types compatible. but still the query is not working.
the column size in the remote db is more than that of local db.
now can i use SUBSTRING to extract only a part of data?

Yes you can use the substring function.

quote:

2. What are the basic aspects we need to look into while tuning a sql query ?
the query has three insert statements which insert huge amount of data into the table based on few conditions. its running fast on my machine but thru appln it is slower. what can i do now?

Refer the below article for tips on query tuning.

http://www.sql-server-performance.com/articles/per/query_tuning_p1.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

byrdzeye
Starting Member

14 Posts

Posted - 2010-04-05 : 09:13:51

You can use substring but if you specify length in the conversion then you won't need to.
Instead of:

INSERT INTO tbl2 (Col1, Col2)
SELECT convert(varchar,Col1) ,convert(char,Col2)
FROM tbl1

Use:

INSERT INTO tbl2 (Col1, Col2)
SELECT convert(varchar(5),Col1) ,convert(char(5),Col2)
FROM tbl1

Besides query tuning, inserting huge amounts of data may require other stratagies. The first I would look at is breaking the huge single insert into smaller 'chunks' thereby reducing the transaction size which can be a major bottleneck in a huge transaction.
There are other stratagies including managing constraints, indexes and staging data. Depends...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 09:53:53

Also see the problems in not specifying length when do conversions below

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources