Performance issue - insert / delete

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

SQL Server Administration (2005)

Performance issue - insert / delete

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-12-15 : 01:00:55

DELETE FROM TAPP_PROPERTY WHERE APP_NM ='DDS' AND PROPERTY_GROUP_NM ='property' AND PROPERTY_CATEGORY_NM ='config' AND PROPERTY_SUB_CATEGORY_NM= '16_JST_99999999_JSTNAV16'
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','16_JST_99999999_JSTNAV16','templateid','JST_16','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','16_JST_99999999_JSTNAV16','appname','jetstarjapan','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','16_JST_99999999_JSTNAV16','username','user01','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','16_JST_99999999_JSTNAV16','sender','aiuota@aig.co.jp','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','16_JST_99999999_JSTNAV16','subject','?From AIU????????????????','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','16_JST_99999999_JSTNAV16','location','16JetstarJapan_email.html','','DD','samkumar',GETDATE())
GO
DELETE FROM TAPP_PROPERTY WHERE APP_NM ='DDS' AND PROPERTY_GROUP_NM ='property' AND PROPERTY_CATEGORY_NM ='config' AND PROPERTY_SUB_CATEGORY_NM= '14_BMI'
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','templateid','BMI_14','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','appname','bmi','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','username','user01','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','sender','bmitravelinsurance@chartisinsurance.com','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','subject','Your bmi Travel Insurance Documents','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','location','14BMIemail.html','','DD','samkumar',GETDATE())
GO
INSERT INTO TAPP_PROPERTY (APP_NM,PROPERTY_GROUP_NM,PROPERTY_CATEGORY_NM,PROPERTY_SUB_CATEGORY_NM,PROPERTY_NM,PROPERTY_VALUE_TXT,PROPERTY_DESC,SOURCE_SYSTEM_ID,USERID_CD,TIMESTAMP)VALUES ('DDS','property','config','14_BMI','policywordingslink','bmi/14BMIpolicywording.pdf','','DD','samkumar',GETDATE())
GO
Like this i have almost 10 k line script.
It will take approx 1.15 hr to execute.

How i can increase the performance.
if i used tran / comiit block ---- it will help full .pls suggest.

Regards,
avijit

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-15 : 12:36:26

You can increase the performance by not doing it via INSERT. You really need to be doing this via an import program such as bcp from a csv file.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources