| Author |
Topic |
|
raghav99k
Starting Member
32 Posts |
 Posted - 2011-11-15 : 02:56:55
|
Hi All,In a query while inserting data into a table we are using cast and convert functions even though they are not actually required on 32 columns totally. When working on this it is taking around 16 hrs for about 8 Crore records.If i take these funtions wil it imporve the performance..!my dev team wil acept if we can shown diff of about 10 hrs..question is whether cast and convert wil effect r nt. Bcz of these only it is taking more time r nt..Can u pls provide ur valuable suggestins.Thnk. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-11-15 : 02:59:56
|
In general, if you have apply a function on a column in the JOIN or WHERE clause, SQL Server will not be able to utilize any index you have on that column, so the performance will be affected.If cast & convert is not required, why not just removed them ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
raghav99k
Starting Member
32 Posts |
Posted - 2011-11-15 : 03:17:34
|
| INSERT INTO xys (Pd, Cy , ID , CEte , Chnde , ChaKy , Cde...upto 35 colums)SELECT 'sdd', My , cast(d.CaD as varchar(12)) , convert(char(8),d.Ce,112) , cast(d.Chde as varchar(5)) , cast(d.CKe as int) , cast(d.Code as varchar(21))...using cast and convert upt 35 colums)FROM pers d WHERE NOT EXISTS (SELECT My FROM xys s WHERE s.MD = d.MD AND s.PD = d.PD AND s.LD = d.LD AND s.Ee = d.Ee ) AND My <> 0any suggestions pls..Thanks for ur reply.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-15 : 03:43:22
|
| if they were not really required, what was the purpose of doing all these casts? are the corresponding columns in target table of different type which is what cast is specifying?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raghav99k
Starting Member
32 Posts |
Posted - 2011-11-15 : 03:58:14
|
| nope, both the target and source datatypes are in syn(same type). for security they sepicified. if we remove these, can we improve..? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-15 : 04:05:32
|
| it should boost up performance to some extend especially if dataset involved is large------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raghav99k
Starting Member
32 Posts |
Posted - 2011-11-15 : 04:37:43
|
| dataset is toolarge.. we have about 8-10 crores of records...Thanks for ur suggestion, can we rewrite the query in place of not exists query. wil it help.! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-15 : 06:45:25
|
| not exists can be rewritten using left join but on most occasions i've seen not exists performing much better over left join so i dont think that will help------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raghav99k
Starting Member
32 Posts |
Posted - 2011-11-15 : 07:21:52
|
| yes visakh u r correct.. |
|
|
|
|
Cast Function.