SQL ServerË÷Òý¹ÜÀíÖ®Áù´óÌúÂÉ
ͨ¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä£»¿ÉÒÔʹ±íºÍ±íÖ®¼äµÄÁ¬½ÓËٶȼӿ졣µ«ÊÇ£¬²»ÊÇÔÚÈκÎʱºòʹÓÃË÷Òý¶¼Äܹ»´ïµ½ÕâÖÖЧ¹û¡£ÈôÔÚ²»Ç¡µ±µÄ³¡ºÏÏ£¬Ê¹ÓÃË÷Òý·´¶ø»áÊÂÓëԸΥ¡£ Ë÷ÒýÊÇÒÔ±íÁÐΪ»ù´¡µÄÊý¾Ý¿â¶ÔÏó¡£Ë÷ÒýÖб£´æ×űíÖÐÅÅÐòµÄË÷ÒýÁÐ
¡¡¡¡
ͨ¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä£»¿ÉÒÔʹ±íºÍ±íÖ®¼äµÄÁ¬½ÓËٶȼӿ졣µ«ÊÇ£¬²»ÊÇÔÚÈκÎʱºòʹÓÃË÷Òý¶¼Äܹ»´ïµ½ÕâÖÖЧ¹û¡£ÈôÔÚ²»Ç¡µ±µÄ³¡ºÏÏ£¬Ê¹ÓÃË÷Òý·´¶ø»áÊÂÓëԸΥ¡£
Ë÷ÒýÊÇÒÔ±íÁÐΪ»ù´¡µÄÊý¾Ý¿â¶ÔÏó¡£Ë÷ÒýÖб£´æ×űíÖÐÅÅÐòµÄË÷ÒýÁУ¬²¢ÇҼͼÁËË÷ÒýÁÐÔÚÊý¾Ý¿â±íÖеÄÎïÀí´æ´¢Î»Öã¬ÊµÏÖÁ˱íÖÐÊý¾ÝµÄÂß¼ÅÅÐò¡£Í¨¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä;¿ÉÒÔʹ±íºÍ±íÖ®¼äµÄÁ¬½ÓËٶȼӿ졣
µ«ÊÇ£¬²»ÊÇÔÚÈκÎʱºòʹÓÃË÷Òý¶¼Äܹ»´ïµ½ÕâÖÖЧ¹û¡£ÈôÔÚ²»Ç¡µ±µÄ³¡ºÏÏ£¬Ê¹ÓÃË÷Òý·´¶ø»áÊÂÓëԸΥ¡£ËùÒÔ£¬ÔÚSQL ServerÊý¾Ý¿âÖÐʹÓÃË÷ÒýµÄ»°£¬»¹ÊÇÐèÒª×ñÊØÒ»¶¨µÄ¹æÔò¡£
ÌúÂÉÒ»£ºÌìÏÂûÓÐÃâ·ÑµÄÎç²Í£¬Ê¹ÓÃË÷ÒýÊÇÐèÒª¸¶³ö´ú¼ÛµÄ
Ë÷ÒýµÄÓŵãÓÐÄ¿¹²¶Ã£¬µ«ÊÇ£¬È´ºÜÉÙÓÐÈ˹ØÐĹý²ÉÓÃË÷ÒýËùÐèÒª¸¶³öµÄ³É±¾¡£ÈôÊý¾Ý¿â¹ÜÀíÔ±Äܹ»¶ÔË÷ÒýËùÐèÒª¸¶³öµÄ´ú¼ÛÓÐÒ»¸ö³ä·ÖµÄÈÏʶ£¬Ò²¾Í²»»áÄÇôËæÒâµ½´¦½¨Á¢Ë÷ÒýÁË¡£
×ÐϸÊýÊý£¬Æäʵ½¨Á¢Ë÷ÒýµÄ´ú¼Û»¹ÊÇÂù´óµÄ¡£Èç´´½¨Ë÷ÒýºÍά»¤Ë÷Òý¶¼ÐèÒª»¨·Ñʱ¼äÓ뾫Á¦¡£ÌرðÊÇÔÚÊý¾Ý¿âÉè¼ÆµÄʱºò£¬Êý¾Ý¿â¹ÜÀíԱΪ±íÖеÄÄÄЩ×Ö¶ÎÐèÒª½¨Á¢Ë÷Òý£¬Òªµ÷ÑС¢ÒªÐµ÷¡£Èçµ±½¨ÓÐË÷ÒýµÄ±íÖеļͼÓÖÔö¼Ó¡¢É¾³ý¡¢Ð޸IJÙ×÷ʱ£¬Êý¾Ý¿âÒª¶ÔË÷Òý½øÐÐÖØе÷Õû¡£ËäÈ»Õâ¸ö¹¤×÷Êý¾Ý¿â×Ô¶¯»áÍê³É£¬µ«ÊÇ£¬ÐèÒªÏûºÄ·þÎñÆ÷µÄ×ÊÔ´¡£µ±±íÖеÄÊý¾ÝÔ½¶à£¬Õâ¸öÏûºÄµÄ×ÊÔ´Ò²¾ÍÔ½¶à¡£ÈçË÷ÒýÊÇÊý¾Ý¿âÖÐʵ¼Ê´æÔڵĶÔÏó£¬ËùÒÔ£¬Ã¿¸öË÷Òý¶¼»áÕ¼ÓÃÒ»¶¨µÄÎïÀí¿Õ¼ä¡£ÈôË÷Òý¶àÁË£¬²»µ«»áÕ¼ÓôóÁ¿µÄÎïÀí¿Õ¼ä£¬¶øÇÒ£¬Ò²»áÓ°Ïìµ½Õû¸öÊý¾Ý¿âµÄÔËÐÐÐÔÄÜ¡£
¿É¼û£¬Êý¾Ý¿â¹ÜÀíÔ±ÈôÒª²ÉÓÃË÷ÒýÀ´Ìá¸ßϵͳµÄÐÔÄÜ£¬×ÔÉíÈÔÈ»ÐèÒª¸¶³ö²»ÉٵĴú¼Û¡£Êý¾Ý¿â¹ÜÀíÔ±ÏÖÔÚÒª¿¼ÂǵľÍÊÇÈçºÎÔÚÕâÁ½¸öÖ®¼äÈ¡µÃÒ»¸ö¾ùºâ¡£»òÕß˵£¬ÕÒµ½Ò»¸ö»Ø±¨ÓëͶÈëµÄÁÙ½çµã¡£
ÌúÂɶþ£º¶ÔÓÚ²éѯÖкÜÉÙÉæ¼°µÄÁлòÕßÖظ´Öµ±È½Ï¶àµÄÁУ¬²»Òª½¨Á¢Ë÷Òý
ÔÚ²éѯµÄʱºò£¬Èç¹ûÎÒÃDz»°´Ä³¸ö×Ö¶ÎÈ¥²éѯ£¬ÔòÔÚÕâ¸ö×Ö¶ÎÉϽ¨Á¢Ë÷ÒýÒ²ÊÇÀË·Ñ¡£ÈçÏÖÔÚÓÐÒ»ÕÅÔ±¹¤ÐÅÏ¢±í£¬ÎÒÃÇ¿ÉÄÜ°´Ô±¹¤±àºÅ¡¢Ô±¹¤ÐÕÃû¡¢»òÕß³öÉíµØÈ¥²éѯԱ¹¤ÐÅÏ¢¡£µ«ÊÇ£¬ÎÒÃÇÍùÍù²»»á°´ÕÕÉí·ÝÖ¤ºÅÂëÈ¥²éѯ¡£ËäÈ»Õâ¸öÉí·ÝÖ¤ºÅÂëÊÇΨһµÄ¡£´Ëʱ£¬¼´Ê¹ÔÚÕâ¸ö×Ö¶ÎÉϽ¨Á¢Ë÷Òý
Ïà¹ØÎĵµ£º
sp_databases --Áгö·þÎñÆ÷ÉϵÄËùÓÐÊý¾Ý¿â
sp_server_info --Áгö·þÎñÆ÷ÐÅÏ¢£¬Èç×Ö·û¼¯£¬°æ±¾ºÍÅÅÁÐ˳Ðò
sp_stored_procedures--Áгöµ±Ç°»·¾³ÖеÄËùÓд洢¹ý³Ì
sp_tables --Áгöµ±Ç°»·¾³ÖÐËùÓпÉÒÔ²éѯµÄ¶ÔÏó
sp_start_job --Á¢¼´Æô¶¯×Ô¶¯»¯ÈÎÎñ
sp_stop_job --Í£Ö¹ÕýÔÚÖ´ÐеÄ×Ô¶¯»¯ÈÎÎñ
sp_password --Ì ......
¡ô1¡¢Ê¹ÓÃË÷ÒýÀ´¸ü¿ìµØ±éÀú±í
ȱʡÇé¿öϽ¨Á¢µÄË÷ÒýÊÇ·ÇȺ¼¯Ë÷Òý£¬µ«ÓÐʱËü²¢²»ÊÇ×î¼ÑµÄ¡£ÔÚ·ÇȺ¼¯Ë÷ÒýÏ£¬Êý¾ÝÔÚÎïÀíÉÏËæ»ú´æ·ÅÔÚÊý¾ÝÒ³ÉÏ¡£ºÏÀíµÄË÷ÒýÉè¼ÆÒª½¨Á¢ÔÚ¶Ô¸÷ÖÖ²éѯµÄ·ÖÎöºÍÔ¤²âÉÏ¡£Ò»°ãÀ´Ëµ£º¢Ù.ÓдóÁ¿Öظ´Öµ¡¢ÇÒ¾³£Óз¶Î§²éѯ£¨between, > ,< £¬> =,< =£©ºÍorder by¡¢group by·¢ÉúµÄÁÐ ......
Maximizing SQL*Loader Performance
SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads. These include:
¡ñ Use Direct Path Loads - The conventional path loader essentially loads the data by usin ......
Ó¦ÓÃʾÀý£º
-- ´´½¨2¸ö²âÊÔ±í
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)
go
-- Ìî³äÊý¾Ý
declare @i int
set @i = 0
while(@i < 20)
begin
insert Table_2019(Data) values('')
  ......
Óû§×Ô¶¨Ò庯Êý£¨User Defined Functions£©ÊÇSQL Server µÄÊý¾Ý¿â¶ÔÏó£¬Ëü²»ÄÜÓÃÓÚÖ´ÐÐһϵÁиıäÊý¾Ý¿â״̬µÄ²Ù×÷£¬µ«Ëü¿ÉÒÔÏñϵͳº¯ÊýÒ»ÑùÔÚ²éѯ»ò´æ´¢¹ý³ÌµÈµÄ³ÌÐò¶ÎÖÐʹÓã¬Ò²¿ÉÒÔÏñ´æ´¢¹ý³ÌÒ»Ñùͨ¹ý EXECUTE ÃüÁîÀ´Ö´ÐС£Óû§×Ô¶¨Ò庯ÊýÖд洢ÁËÒ»¸öTransact-SQL Àý³Ì£¬¿ÉÒÔ·µ»ØÒ»¶¨µÄÖµ¡£
¡¡¡¡ÔÚSQL Server Öиù¾Ýº ......