SQL ²éѯÓÅ»¯ £¨Ò»£©
ʹÓþۼ¯Ë÷ÒýÓÅ»¯SQL²éѯ
Ê×ÏÈÈÃÎÒÃÇ×öÒ»¸ö²âÊÔ£¬ÏÖ´´½¨Ò»¸ö±í Ïò±íÖвåÈë²»µÈÊý¾Ý
--DROP TABLE T_UserInfo--------------------------------------
CREATE
TABLE
T_UserInfo
(
Userid
varchar(20),
UserName varchar(20)
)
--
DECLARE
@I INT
DECLARE
@ENDID INT
SELECT
@I =
1
SELECT
@ENDID = 100
-- ÔÚ´Ë´¦¸ü¸ÄÒª²åÈëµÄÊý¾Ý£¬ÖØвåÈë֮ǰҪɾµôËùÓÐÊý¾Ý
WHILE @I <=@ENDID
BEGIN
INSERT INTO T_UserInfo
SELECT 'ABCDE'+CAST(@I AS VARCHAR(20))+'EF','Fan'+CAST(@I AS VARCHAR(20))
SELECT @I = @I+1
END
--Ïà¹ØSQLÓï¾ä½âÊÍ
-------------------------------------------------------------½¨¾Û¼¯Ë÷Òý
CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
--½¨·Ç¾Û¼¯Ë÷Òý
CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
--ɾ³ýË÷Òý
DROP INDEX T_UserInfo.INDEX_Userid
ÎÒÃÇ´´½¨ ±íT_UserInfo £¬ÏòT_UserInfo Ìí¼Ó100ÌõÊý¾Ý¡£Õâʱ²»´´½¨Ë÷Òý¡£
È»ºóдÁËÒ»Ìõ²éѯÓï¾ä£º
SELECT * from T_UserInfo WHERE USERID='ABCDE6EF' Ñ¡ÖÐÒÔÉÏÓï¾ä£¬
°´Ctrl+L
±íɨÃ裺ɨÃè±íÖеÄÐÐ
Ö´ÐÐ:SET STATISTICS IO ON
´ËʱÔÙÖ´ÐиÃSQL:SELECT * from T_UserInfo WHERE USERID='ABCDE6EF'
Çл»µ½ÏûÏ¢À¸ÏÔʾÈçÏ£º
±í'T_UserInfo'¡£É¨Ãè¼ÆÊý1£¬Âß¼¶Á1 ´Î£¬ÎïÀí¶Á0 ´Î£¬Ô¤¶Á0 ´Î¡£
½âÊÍÏÂÆäÒâ˼£º
ËĸöÖµ·Ö±ðΪ£º
Ö´ÐеÄɨÃè´ÎÊý;
´ÓÊý¾Ý»º´æ¶ÁÈ¡µÄÒ³Êý;
´Ó´ÅÅ̶ÁÈ¡µÄÒ³Êý;
Ϊ½øÐвéѯ¶ø·ÅÈ뻺´æµÄÒ³Êý
ÎÒÃÇÔÚ´´½¨Ò»¸öË÷Òý
Ö´ÐÐCREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
È»ºóÔÙÖ´ÐÐSELECT * from T_UserInfo WHERE USERID='ABCDE6EF'
Çл»µ½ÏûÏ¢À¸ÈçÏÂÏÔʾ£º
CTRL+ L
±í'T_UserInfo'¡£É¨Ãè¼ÆÊý1£¬Âß¼¶Á2 ´Î£¬ÎïÀí¶Á0 ´Î£¬Ô¤¶Á0 ´Î¡£
´ËʱÂß¼¶ÁÓÉÔÀ´µÄ1±ä³É2£¬ ˵Ã÷ÎÒÃÇÓÖ¼ÓÁËÒ»¸öË÷ÒýÒ³£¬ÏÖÔÚÎÒÃDzéѯʱ£¬Âß¼¶Á¾ÍÊÇÒª¶ÁÁ½Ò³(1Ë÷ÒýÒ³+1Êý¾ÝÒ³)£¬´ËʱµÄЧÂÊ»¹²»Èç²»½¨Ë÷Òý¡£
ÄÇôokay, ÈÃÎÒÃÇ°ÑÊý¾ÝÌî
Ïà¹ØÎĵµ£º
д³ÌÐòµÄÈË£¬ÍùÍùÐèÒª·ÖÎöËùдµÄSQLÓï¾äÊÇ·ñÒѾÓÅ»¯¹ýÁË£¬·þÎñÆ÷µÄÏìӦʱ¼äÓжà¿ì£¬Õâ¸öʱºò¾ÍÐèÒªÓõ½SQLµÄSTATISTICS״ֵ̬À´²é¿´ÁË¡£
ͨ¹ýÉèÖÃSTATISTICSÎÒÃÇ¿ÉÒԲ鿴ִÐÐSQLʱµÄϵͳÇé¿ö¡£Ñ¡ÏîÓÐPROFILE£¬IO £¬TIME¡£½éÉÜÈçÏ£º
SET STATISTICS PROFILE ON£ºÏÔʾ·ÖÎö ......
ÔÚExcelÖУ¬ÎÒÃÇʱ³£»áÅöµ½ÕâÑùµÄ×Ö¶Î(×î³£¼ûµÄ¾ÍÊǵ绰ºÅÂë)£¬¼´Óд¿Êý×ÖµÄ(ÈçûÓдøÇøºÅµÄµç»°ºÅÂë)£¬ÓÖÓÐÊý×ÖºÍÆäËü×Ö·û»ìºÏ (Èç“ÇøºÅ-µç
»°ºÅÂë”)µÄÊý¾Ý£¬ÔÚµ¼ÈëSQLServer¹ý³ÌÖУ¬»á·¢ÏÖҪô´¿Êý×ÖµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³ÉÁËNULL£¬ÒªÃ´¾ÍÊÇÊý×ÖºÍÆäËü×Ö·û»ìºÏµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³É
ÁËNULL¡£
&n ......
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËƺõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......
ÔÚʹÓÃODP.NET½øÐÐOracle±à³Ìʱ£¬ÓÐʱºòSQLÓï¾ä·Ç³£¸´ÔÓ£¬ÐèÒª²ÉÓö¯Ì¬¹¹Ôì²éѯÓï¾äµÄÇé¿ö£¬ÓÐÁ½ÖÖ·½·¨¿ÉÒÔ¹¹Ô춯̬µÄSQLÓï¾ä£¬²¢Ö´Ðзµ»Ø½á¹û¼¯¡£
1¡¢ÔÚÊý¾Ý·ÃÎʲ㹹ÔìSQLÓï¾ä
ÀýÈçÏÂÃæµÄÓï¾ä£¬½«¹¹ÔìÍêÕûµÄSQLÓï¾ä¸³Öµ¸øCommandText£¬ÔÙ´«µÝµ½Êý¾Ý¿â½øÐÐÖ´ÐУ¬·µ»Ø½á¹û¼¯¡£
loadCommand.CommandType = Com ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ÓÐЩʱºòÎÒÃÇÏ£ÍûµÃµ½Ö¸¶¨Êý¾ÝÖеÄÇ°nÁУ¬Ê¾ÀýÈçÏ£º
µÃµ½Ã¿¸ö²¿ÃÅнˮ×î¸ßµÄ ......