ÏÈÕ¾ÔÚÓ¦ÓóÌÐòµÄ½Ç¶È˵˵ËüÃǵIJ»Í¬¡£
1¡¢ Ö±½ÓÆ´ SQL
¾ÍÏñ´ó¼ÒÁ˽âµÄÄÇÑù£¬Ö±½ÓÆ´ SQL ´øÀ´ÁË SQL ×¢Èë¹¥»÷£¬´øÀ´ÁËÆ´Ê±Ð©ÐíµÄÐÔÄÜËðʧ£¬µ«ÊÇÆ´²»ÓÃÌí¼Ó SqlParameter £¬»áÉÙдºÜ¶à´úÂë——ºÜ¶àÈËϲ»¶Ö±½ÓÆ´£¬Ò²Ðí¾ÍÒòΪÕâµã¡£ÕâÖÖ×ö·¨»á°ÑÄãÆ´ºÃµÄ SQL ÔÑùÖ±½Ó·¢Ë͵½ DB ·þÎñÆ÷È¥Ö´ÐС££¨×¢ÒâÀàËÆ ”exec yourproc ‘param1’, 12” µÄÓï¾ä²»ÔÚ´Ë·¶³ë£¬ÕâÊǵ÷Óô洢¹ý³ÌµÄÒ»ÖÖ·½Ê½£©
2¡¢ ²ÎÊý»¯ SQL
ËùνµÄ“²ÎÊý»¯ SQL ”¾ÍÊÇÔÚÓ¦ÓóÌÐò²àÉèÖà SqlCommand.CommandText µÄʱºòʹÓòÎÊý£¨È磺 @param1 £©£¬È»ºóͨ¹ý SqlCommand.Parameters.Add À´ÉèÖÃÕâЩ²ÎÊýµÄÖµ¡£ÕâÖÖ×ö·¨»á°ÑÄã×¼±¸ºÃµÄÃüÁîͨ¹ý sp_executesql ϵͳ´æ´¢¹ý³ÌÀ´Ö´ÐС£Í¨¹ý²ÎÊý»¯ SQL £¬ºÍÖ±½ÓÆ´ SQL Ïà±È£¬×îÖ±½ÓµÄºÃ´¦¾ÍÊÇûÓÐ SQL ×¢Èë¹¥»÷ÁË¡£
3¡¢ µ÷Óô洢¹ý³Ì
Ö±½Óµ÷Óô洢¹ý³ÌÆäʵºÍ²ÎÊý»¯ SQL ·Ç³£ÏàËÆ¡£Î¨Ò»µÄ±¾Öʲ»Í¬ÔÚÓÚÄã·¢Ë͵½ DB ·þÎñÆ÷µÄÖ¸Áî²»ÔÙÊÇ sp_executesql £¬¶øÊÇÖ±½ÓµÄ´æ´¢¹ý³Ìµ÷ÓöøÒÑ¡£
ºÜ¶àÈ˷dz£·Ç³£Ñá¶ñÔÚÓ¦ÓóÌÐòÖÐʹÓô洢¹ý³Ì£¬¶øÄþԸʹÓÃÆ´ SQL »òÕß²ÎÊý»¯ SQL £¬ÀíÓÉÊÇËüÃÇÌṩÁ˸üºÃµÄÁé»îÐÔ——Õâ¸öÀ ......
ÎÒÃÇÏÈ¿´ NestedLoop ºÍ MergeJoin µÄËã·¨£¨ÒÔÏÂΪÒýÓ㬼û RicCC µÄ¡¶ ͨÍùÐÔÄÜÓÅ»¯µÄÌìÌà - µØÓü JOIN ·½·¨ËµÃ÷ ¡· ):
==================================
NestedLoop:
foreach rowA in tableA where tableA.col2=?
{
search rowsB from tableB where tableB.col1=rowA.col1 and tableB.col2=? ;
if(rowsB.Count<=0)
discard rowA ;
else
output rowA and rowsB ;
}
MergeJoin:
Á½¸ö±í¶¼°´ÕÕ¹ØÁª×Ö¶ÎÅÅÐòºÃÖ®ºó£¬ merge join ²Ù×÷´Óÿ¸ö±íȡһÌõ¼Ç¼¿ªÊ¼Æ¥Å䣬Èç¹û·ûºÏ¹ØÁªÌõ¼þ£¬Ôò·ÅÈë½á¹û¼¯ÖУ»·ñÔò£¬½«¹ØÁª×Ö¶ÎÖµ½ÏСµÄ¼Ç¼Åׯú£¬´ÓÕâÌõ¼Ç¼¶ÔÓ¦µÄ±íÖÐÈ¡ÏÂÒ»Ìõ¼Ç¼¼ÌÐø½øÐÐÆ¥Å䣬ֱµ½Õû¸öÑ»·½áÊø¡£
==================================
ÎÒÃÇͨ¹ý×î¼òµ¥µÄÇé¿öÀ´¼ÆËã NestedLoop ºÍ MergeJoin µÄÏûºÄ£º
Á½Õűí A ¡¢ B £¬·Ö±ðÓÐ m ¡¢ n ÐÐÊý¾Ý£¨ m < n £©£¬Õ¼Óûù´¡±íÎïÀí´æ´¢¿Õ¼ä·Ö±ðΪ a ¡¢ b Ò³£¬¾Û¼¯Ë÷ÒýÊ÷·ÇÒ¶½Úµã¶¼ÊÇÁ½²ã£¨Ò»²ã¸ù½ ......
ÉÏ»ØÎÒÃÇ˵µ½ÆÀ¹ÀÒ»ÌõÓï¾äÖ´ÐÐЧÂÊÖ÷Òª¿´Âß¼ IO £¨É¶ÊÇÂß¼ IO £¬É¶ÊÇÎïÀí IO ¼ûÁª»úÎĵµ£©£¬Õâ´ÎÎÒÃǼÌÐø¡£
ÎÒÃÇÏÈ˵˵£¬·µ»Ø¶àÐнá¹ûʱ£¬ÎªÊ²Ã´ SQLServer ÓÐʱ»áÑ¡Ôñ index seek £¬ÓÐʱ»áÑ¡Ôñ index scan ¡£
ÒÔ nonclustered index ΪÀý˵Ã÷¡£
ÏñËùÓеÄË÷Òý B Ê÷Ò»Ñù£¬·Ç¾Û¼¯Ë÷ÒýÊ÷Ò²°üÀ¨ÍêÈ«ÓÉË÷ÒýÊý¾Ý×é³ÉµÄ¸ù½ÚµãºÍÖм伶½Úµã£»µ«ÊǺ;ۼ¯Ë÷ÒýÊ÷²»Í¬µÄÊÇ£¬¾Û¼¯Ë÷ÒýÊ÷Ò¶½Úµã°üº¬µÄÊÇ»ù´¡±íµÄÊý¾ÝÒ³£¨ÎÒÃdz£Ëµ£¬±íµÄÎïÀí´æ´¢Ë³ÐòºÍ¾Û¼¯Ë÷ÒýÏàͬ£¬¾ÍÊÇÕâ¸öÔÒò£©,·Ç¾Û¼¯Ë÷ÒýÊ÷Ò¶½ÚµãÊÇË÷ÒýÒ³¡£ SQLServer ͨ¹ý·Ç¾Û¼¯Ë÷Òý²éÕÒÊý¾Ýʱ£¬»áͨ¹ýÕâ¸ö·Ç¾Û¼¯Ë÷Òý¼üֵȥËÑË÷¾Û¼¯Ë÷Òý£¬½ø¶ø¼ìË÷»ù´¡±íÊý¾ÝÐС£
¼ÙÉèÓÐÕâÑùÒ»ÕÅ±í£¬·Ç¾Û¼¯Ë÷ÒýÊ÷Éî¶ÈΪ 2 £¬Ò»²ã¸ù½Úµã£¨ 1 ¸öË÷ÒýÒ³£©£¬Ò»²ãÒ¶½Úµã£¨ 4 ¸öË÷ÒýÒ³£©¡£¾Û¼¯Ë÷ÒýÊ÷Éî¶ÈΪ 3 £¬Ò»²ã¸ù½Úµã£¨ 1 ¸öË÷ÒýÒ³£©£¬Ò»²ãÖм伶½Úµã£¨ 2 ¸öË÷ÒýÒ³£©£¬Ò»²ãÒ¶½Úµã£¨ 250 Ò³£¬Ò²¾ÍÊÇ»ù´¡±íÎïÀí´æ´¢Ò³£©±íµÄÊý¾Ý¼ÙÉè 1w ÐС£×¢£ºËùÓÐÊý¾Ý¾ùΪ¼ÙÉ裬ֻΪ˵Ã÷ÔÀí¡£
ÎÒÃÇÊ×ÏÈ£¬ÔÙÇ¿µ÷Ò»±é£¬ SQLServer »ñÈ¡Êý¾Ý£¬×ÜÊÇÒÔҳΪµ¥Î»£¬¾ÍËãÊÇÖ»¶ÁȡһÐÐÒ²»á»ñÈ¡ÕûÕÅÒ³ £¨¼û¡¶Ð´ÓÐЧÂ浀 SQL ²éѯ£¨ I £©¡·£©
ÏÖÔÚÓÐÒ»Ìõ¼òµ¥²éѯ ( Èç ......
´óÐÍϵͳµÄÉú²ú»·¾³£¬Ò»°ãÇé¿öÏ£¬ÎÒÃÇÆÀ¼ÛÒ»Ìõ²éѯÊÇ·ñÓÐЧÂÊ£¬¸ü¶àµÄÊǹØ×¢Âß¼ IO( ÖÁÓÚΪʲô£¬»ØÍ·²¹Ò»Æª ) ¡£ÎÒÃdz£Ëµ£¬“Òª½¨±ëº·µÄË÷Òý”¡¢“Ҫд¸ßЧµÄ SQL ”£¬Æäʵ×îÖÕÄ¿µÄ¾ÍÊÇÔÚÏàͬ½á¹û¼¯Çé¿öÏ£¬¾¡¿ÉÄܼõÉÙÂß¼ IO ¡£
1.1 where Ìõ¼þµÄÁÐÉ϶¼µÃÓÐͳ¼ÆÐÅÏ¢¡£
ûͳ¼ÆÐÅÏ¢ SQLServer ¾ÍÎÞ·¨¹ÀË㲻ͬ²éѯ¼Æ»®¿ªÏúÓÅÁÓ£¬¶øÖ»ÄܲÉÓÃ×îÎÈÍ×µÄ Scan £¨²»¹ÜÊÇ table scan »¹ÊÇ clustered index scan £©¡£Ò»°ãÇé¿öÏÂÎÒÃDz»»á·¸ÕâÖÖ´íÎó—— where Ìõ¼þÀﲻʹÓ÷ÇË÷ÒýÁÐÊǸö³£Ê¶¡£Ë÷ÒýÉϵÄͳ¼ÆÐÅÏ¢ÊÇÎÞ·¨É¾³ýµÄ¡£
1.2 ¾¡Á¿²»Ê¹Óò»µÈÓÚ£¨ != £©»òÕß NOT Âß¼ÔËËã·û¡£
ÕâÌõ¹æÔò±»¹ãΪ´«ËÌ£¬ÔÒò¾ÝÁª»úÎĵµºÍ°Ù¾´Í¬Ñ§µÄÊé½²£¬Ò²ÊÇ SQLServer ÎÞ·¨ÆÀ¹À²»Í¬²éѯ¼Æ»®¿ªÏúµÄÓÅÁÓ¡£µ«ÊÇ SqlServer2k5 ´ÏÃ÷Á˺ܶ࣬ÊÔÑé·¢ÏÖ¾¡¹ÜÓÃÁË != »òÕß not £¬²éѯ»¹ÊǻᱻÓÅ»¯¡£ÈçÏ£º
create table tb1
(
col1 int identity ( 1, 1) primary key ,
col2 int not null,
col3 varchar ( 64) not null
)
create i ......
ÏÈ˵˵ÕâЩÎóÇø¡£Ëùν“ÎóÇø”£¬ÓÐһЩÊÇÐÂÊÖºÜÈÝÒ×·¸µÄ´íÎó»òÕߺÜÈÝÒ׺öÂÔµÄÎÊÌ⣬ÁíÍâһЩ£¬ÔòÊÇÏñ“ºÄ×Ó³ÔÁËÑλá±ä³Éòùòð”Ò»Ñù£¬ÈÃÎÒÃÇ´ÓС¾ÍÈÏΪÊÇÕýÈ·µÄÊÂÇé¡£ÈçÏ£º
1¡¢ ±íÉϲ»¹ÜÓõÃ×ÅÓò»×Å£¬¶¼¼Ó¸ö¾Û¼¯Ë÷Òý¡£
ÎÒÃÇÖªµÀ£¬±íÒÔÁ½ÖÖ·½Ê½×éÖ¯ÎïÀí´æ´¢£ºÓоۼ¯Ë÷ÒýµÄ“¾Û¼¯±í”£»Ã»Óоۼ¯Ë÷ÒýµÄ“¶Ñ”¡£ÔÚ¾Û¼¯±íÖУ¬Êý¾ÝÐа´ÕÕ¾Û¼¯Ë÷ÒýµÄ˳Ðò´æ´¢£¨ÕâÒ²ÊÇΪɶһÕűí×î¶àÖ»ÄÜÓÐÒ»¸ö¾Û¼¯Ë÷ÒýµÄÔÒò£©£»¶ÑÖУ¬Êý¾ÝÐеĴ洢¿ÉÒÔÈÏΪÊDz»È·¶¨µÄ¡£
ÔÚż¡¶Ð´ÓÐЧÂ浀 SQL ²éѯ£¨ II £©¡·ÖÐÔø¾½éÉܹý DB ÒýÇæÈçºÎÔÚ¾Û¼¯±íÖÐͨ¹ý·Ç¾Û¼¯Ë÷Òý²éÕÒÄ¿±êÊý¾Ý£º´Ó·Ç¾Û¼¯Ë÷ÒýÊ÷¸ù¿ªÊ¼ seek £¬²éÕÒµ½Ä¿±êË÷ÒýÐУ¬È»ºóͨ¹ýË÷ÒýÐÐÉÏ´æ´¢µÄ¾Û¼¯Ë÷Òý¼üÖµ£¬ÅÀ¾Û¼¯Ë÷ÒýÊ÷£¬²¢×îÖÕͨ¹ý¾Û¼¯Ë÷ÒýÐÐÉϵÄÖ¸ÕëÄõ½Ä¿±êÊý¾Ý¡£
µ«ÊǶÑÉϵķǾۼ¯Ë÷Òý´æ´¢µÄ²»ÊǾۼ¯Ë÷Òý¼üÖµ£¬Ëü´æ´¢µÄÊÇÖ¸ÏòÄ¿±êÐеÄÖ¸Õë¡£Ò²¾ÍÊÇ˵£¬Èç¹ûÔÚͬÑùµÄ±íÊǶѣ¬Í¨¹ý·Ç¾Û¼¯Ë÷Òý seek Êý¾Ý½«Ê¡µôÅÀ¾Û¼¯Ë÷ÒýÊ÷µÄËðºÄ£¬¶ø¿ÉÒÔÖ±½Óͨ¹ý·Ç¾Û¼¯Ë÷ÒýÐÐÉϵÄÐÐÖ¸ÕëÖ±½ÓÄõ½Ä¿±êÊý¾Ý¡£Ò²¾ÍÊÇ˵£¬ÔÚijЩÇé¿öÏ£¬Ê¹ÓöѿÉÒÔÌá¸ßϵͳЧÂÊ¡£
Õâ¸ö“ijЩÇé¿ö”£¬¾ÍÊÇ ......
SQL ²é¿´ËùÓбíÃû£º
select name from sysobjects where type='U'
²éѯ±íµÄËùÓÐ×Ö¶ÎÃû£º
Select name from syscolumns Where ID=OBJECT_ID('±íÃû')
select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
ACCESS
²é¿´ËùÓбíÃû£º
select name from MSysObjects where type=1 and flags=0
MSysObjectsÊÇϵͳ¶ÔÏó£¬Ä¬ÈÏÇé¿öÊÇÒþ²ØµÄ¡£Í¨¹ý¹¤¾ß¡¢Ñ¡Ïî¡¢ÊÓͼ¡¢ÏÔʾ¡¢ÏµÍ³¶ÔÏó¿ÉÒÔʹ֮ÏÔʾ³öÀ´¡£
SQL²éѯ±íµÄ±¸×¢ËµÃ÷
SELECT ±íÃû = case when a.colorder = 1 then d.name
else '' end,
±í˵Ã÷ = case when a.colorder = 1 then isnull(f.value, '')
else '' end
from syscolumns a
inner join sysobjects d
&nbs ......