sqlwith性能

⑴ sql server with as 能提高性能嗎

使用WITHAS提高性能簡化嵌套SQL


一．WITHAS的含義
WITHAS短語，也叫做子查詢部分（subqueryfactoring），可以讓你做很多事情，定義一個SQL片斷，該SQL片斷會
被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNIONALL的不同部分，作為提供數
據的部分。
特別對於UNIONALL比較有用。因為UNIONALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，
所以可以使用WITHAS短語，則只要執行一遍即可。如果WITHAS短語所定義的表名被調用兩次以上，則優化器會自動將
WITHAS短語所獲取的數據放入一個TEMP表裡，如果只是被調用一次，則不會。而提示materialize則是強制將WITHAS
短語里的數據放入一個全局臨時表裡。很多查詢通過這種方法都可以提高速度。
二．使用方法
先看下面一個嵌套的查詢語句：
select*fromperson.
(.CountryRegionwhereNamelike'C%')上面的查詢語句使用了一個子查詢。雖然這條SQL語句並不復雜，但如果嵌套的層次過多，會使SQL語句非常難以閱
讀和維護。因此，也可以使用表變數的方式來解決這個問題。
SQL語句如下：
declare@ttable(CountryRegionCodenvarchar(3))
insertinto@t(CountryRegionCode)(.CountryRegionwhereNamelike'C%')

select*fromperson.
in(select*from@t)

雖然上面的SQL語句要比第一種方式更復雜，但卻將子查詢放在了表變數@t中，這樣做將使SQL語句更容易維護，但又
會帶來另一個問題，就是性能的損失。由於表變數實際上使用了臨時表，從而增加了額外的I/O開銷，因此，表變數的方式
並不太適合數據量大且頻繁查詢的情況。為此，在SQLServer2005中提供了另外一種解決方案，這就是公用表表達式（CTE），使用CTE，可以增加SQL語句的可維護性，同時，CTE要比表變數的效率高得多。
下面是CTE的語法：
[WITH<common_table_expression>[,n]]
<common_table_expression>::=
expression_name[(column_name[,n])]
AS
(CTE_query_definition)

現在使用CTE來解決上面的問題，SQL語句如下：
with
cras
(
.CountryRegionwhereNamelike'C%'
)

select*fromperson.(select*fromcr)

其中cr是一個公用表表達式，該表達式在使用上與表變數類似，只是SQLServer2005在處理公用表表達式的方式上有
所不同。
在使用CTE時應注意如下幾點：
1.CTE後面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。如下面的SQL語句將無法正
常使用CTE：
with
cras
(
.CountryRegionwhereNamelike'C%'
)
select*fromperson.CountryRegion--應將這條SQL語句去掉
--使用CTE的SQL語句應緊跟在相關的CTE後面--
select*fromperson.(select*fromcr)


2.CTE後面也可以跟其他的CTE，但只能使用一個with，多個CTE中間用逗號（,）分隔，如下面的SQL語句所示：
with
cte1as
(
select*fromtable1wherenamelike'abc%'
),
cte2as
(
select*fromtable2whereid>20
),
cte3as
(
select*fromtable3whereprice<100
)
selecta.*fromcte1a,cte2b,cte3cwherea.id=b.idanda.id=c.id


3.如果CTE的表達式名稱與某個數據表或視圖重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE，當然，後面的SQL語句
使用的就是數據表或視圖了，如下面的SQL語句所示：
--table1是一個實際存在的表
with
table1as
(
select*frompersonswhereage<30
)
select*fromtable1--使用了名為table1的公共表表達式
select*fromtable1--使用了名為table1的數據表


4.CTE可以引用自身，也可以引用在同一WITH子句中預先定義的CTE。不允許前向引用。
5.不能在CTE_query_definition中使用以下子句：
（1）COMPUTE或COMPUTEBY
（2）ORDERBY（除非指定了TOP子句）
（3）INTO
（4）帶有查詢提示的OPTION子句
（5）FORXML
（6）FORBROWSE

6.如果將CTE用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾，如下面的SQL所示：
declare@snvarchar(3)
set@s='C%'
;--必須加分號
with
t_treeas
(
.CountryRegionwhereNamelike@s
)
select*fromperson.(select*fromt_tree)

⑵ sql server 中 如何有效的提高性能

特別說明 在微軟的SQL Server系統中通過有效的使用索引可以提高資料庫的查詢性能，但是性能的提高取決於資料庫的實現。在本文中將會告訴你如何實現索引並有效的提高資料庫的性能。 在關系型資料庫中使用索引能夠提高資料庫性能，這一點是非常明顯的。用的索引越多，從資料庫系統中得到數據的速度就越快。然而，需要注意的是，用的索引越多，向資料庫系統中插入新數據所花費的時間就越多。在本文中，你將了解到微軟的SQL Server資料庫所支持的各種不同類型的索引，在這里你將了解到如何使用不同的方法來實現索引，通過這些不同的實現方法，你在資料庫的讀性能方面得到的遠比在資料庫的整體性能方面的損失要多得多。 索引的定義 索引是資料庫的工具，通過使用索引，在資料庫中獲取數據的時候，就可以不用掃描資料庫中的所有數據記錄，這樣能夠提高系統獲取數據的性能。使用索引可以改變數據的組織方式，使得所有的數據都是按照相似的結構來組織的，這樣就可以很容易地實現數據的檢索訪問。索引是按照列來創建的，這樣就可以根據索引列中的值來幫助資料庫找到相應的數據。 索引的類型 微軟的SQL Server 支持兩種類型的索引：clustered 索引和nonclustered索引。Clustered 索引在數據表中按照物理順序存儲數據。因為在表中只有一個物理順序，所以在每個表中只能有一個clustered索引。在查找某個范圍內的數據時，Clustered索引是一種非常有效的索引，因為這些數據在存儲的時候已經按照物理順序排好序了。 Nonclustered索引不會影響到下面的物理存儲，但是它是由數據行指針構成的。如果已經存在一個clustered索引，在nonclustered中的索引指針將包含clustered索引的位置參考。這些索引比數據更緊促，而且對這些索引的掃描速度比對實際的數據表掃描要快得多。 如何實現索引 資料庫可以自動創建某些索引。例如，微軟的SQL Server系統通過自動創建唯一索引來強制實現UNIQUE約束，這樣可以確保在資料庫中不會插入重復數據。也可以使用CREATE INDEX語句或者通過SQL Server Enterprise Manager來創建其他索引，SQL Server Enterprise Manager還有一個索引創建模板來指導你如何創建索引。 得到更好的性能 雖然索引可以帶來性能上的優勢，但是同時也將帶來一定的代價。雖然SQL Server系統允許你在每個數據表中創建多達256個nonclustered索引，但是建議不要使用這么多的索引。因為索引需要在內存和物理磁碟驅動器上使用更多的存儲空間。在執行插入聲明的過程中可能會在一定程度上導致系統性能的下降，因為在插入數據的時候是需要根據索引的順序插入，而不是在第一個可用的位置直接插入數據，這樣一來，存在的索引越多將導致插入或者更新聲明所需要的時間就越多。 在使用SQL Server系統創建索引的時候，建議參照下面的創建准則來實現： 正確的選擇數據類型 在索引中使用某些數據類型可以提高資料庫系統的效率，例如，Int，bigint， smallint，和tinyint等這些數據類型都非常適合於用在索引中，因為他們都佔用相同大小的空間並且可以很容易地實現比較操作。其他的數據類型如char和varchar的效率都非常低，因為這些數據類型都不適合於執行數學操作，並且執行比較操作的時間都比上面提到數據類型要長。 確保在使用的過程中正確的利用索引值 在執行查詢操作時，可能所使用的列只是clustered的一部分，這時尤其要注意的是如何使用這些數據。當用這些數據列作為參數調用函數時，這些函數可能會使現有的排序優勢失效。例如，使用日期值作為索引，而為了實現比較操作，可能需要將這個日期值轉換為字元串，這樣將導致在查詢過程中無法用到這個日期索引值。 在創建多列索引時，需要注意列的順序 資料庫將根據第一列索引的值來排列記錄，然後進一步根據第二列的值來排序，依次排序直到最後一個索引排序完畢。哪一列唯一數據值較少，哪一列就應該為第一個索引，這樣可以確保數據可以通過索引進一步交叉排序。 在clustered索引中限制列的數量 在clustered索引中用到的列越多，在nonclustered索引中包含的clustered索引參考位置就越多，需要存儲的數據也就越多。這樣將增加包含索引的數據表的大小，並且將增加基於索引的搜索時間。 避免頻繁更新clustered索引數據列 由於nonclustered 索引依賴於clustered 索引，所以如果構成clustered 索引的數據列頻繁更新，將導致在nonclustered中存儲的行定位器也將隨之頻繁更新。對於所有與這些列相關的查詢來說，如果發生記錄被鎖定的情況時，這將可能導致性能成本的增加。 分開操作（如果可能的話） 對於一個表來說，如果需要進行頻繁的執行插入、更新操作，同時還有大量讀操作的話，在可能的情況下嘗試將這個表分開操作。所有的插入和更新操作可以在一個沒有索引的表中操作，然後將其復制到另外一個表中，在這個表裡有大量的索引可以優化讀數據的能力。 適當的重建索引 Nonclustered索引包含clustered索引的指針，這樣一來Nonclustered索引將從屬於clustered 索引。當重建clustered索引時，首先是丟棄原來的索引，然後再使用CREATE INDEX 來創建索引，或者在使用CREATE INDEX 聲明的同時將DROP_EXISTING 子句作為重建索引的一部分。將丟棄和創建分為幾步將會導致多次重建nonclustered 索引，而不象使用DROP_EXISTING 子句那樣，只重建一次nonclustered 索引。 明智的使用填充因子 數據存儲在那些具有固定大小的連續內存頁面內。隨著新的記錄行的加入，數據內存頁將逐漸被填滿，系統就必須執行數據頁的拆分工作，通過這個拆分工作將部分數據轉移到下一個新的頁面當中。這樣的拆分之後，將加重系統的負擔，並且會導致存儲的數據支離破碎。填充因子可以維護數據之間的缺口，一般在創建索引的時候，該索引的填充因子就已經被設置好了。這樣一來，可以減少插入數據所引起的頁面分裂的次數。因為只是在創建索引的時候才維護空間的大小，在增加數據或者更新數據時不會去維護空間的大小。因此，要想能夠充分的利用填充因子，就必須周期性的重建索引。由填充因子所造成的缺口將導致讀性能的下降，因為隨著資料庫的擴張，越來越多的磁碟存取工作需要讀取數據。所以，在讀的次數超過寫的次數的時候，很重要的一點是考慮使用填充因子還是使用預設方式合適。 管理層的決策 通過有效的使用索引，可以在微軟的SQL Server系統中實現很好的查詢功能，但是使用索引的效率取決於幾種不同的實現決策。在索引的性能平衡方面，要做出正確的資料庫管理決策意味著需要在良好的性能和困境中抉擇。在特定的情況下，本文給出的一些建議將有助於你做出正確的決策。

⑶ 這個sql如何優化性能,內附詳細sql

crm_contract_details 的 contractid 有索引嗎？

把各個連接條件涉及到的列，都檢查一下有沒有索引吧。

看樣子應該是沒有什麼太好的辦法。

你的 contract 表的 數據量似乎很大，是什麼級別的？ 在查詢中也沒有對 contract 表進行篩選。

所以這個表的 全表掃描 似乎是不可避免的。

⑷ 請幫忙分析這個sql語句的性能，現在查詢很慢，哪裡需要進行優化 表中數據是百萬級的，索引情況未知

1、area_code in (...) 可以改成 join或 exist試試
2、t02的from中的 order by his.GATHER_DATE, his.USER_NAME可以去了，無用的排序
3、t02的開窗函數是否可以改成 GATHER_DATE=(select max(GATHER_DATE) from t1 where t1.username=t2.username)形式，不過這都是需要看執行計劃
4、 order by t01.PUSH_DATE,
t01.TAG_USERNAME
)
WHERE rn > 0 5 6
AND rn <= 20000 7 這個order by不應該放在子查詢里，排序的記錄多呀，可以放到最外邊來

⑸ 關於sql性能問題

WITH AS短語，也叫做子查詢部分（subquery factoring），可以讓你做很多事情，定義一個SQL片斷，該SQL片斷會
被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供數
據的部分。
特別對於UNION ALL比較有用。因為UNION ALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，
所以可以使用WITH AS短語，則只要執行一遍即可。如果WITH AS短語所定義的表名被調用兩次以上，則優化器會自動將
WITH AS短語所獲取的數據放入一個TEMP表裡，如果只是被調用一次，則不會。而提示materialize則是強制將WITH AS
短語里的數據放入一個全局臨時表裡。很多查詢通過這種方法都可以提高速度。

後者性能其實差不多，但是with as 好維護，可讀性也好很多

⑹ SQL資料庫性能和資料庫調優

連接數量有三種方法查看 1.通過系統的逗性能地來查看： 開始->管理工具->性能（或者是運行裡面輸入 mmc）然後通過 添加計數器添加 SQL 的常用統計 然後在下面列出的項目裡面選擇用戶連接就可以時時查詢到sql server資料庫連接數了。 不過此方法的話需要有訪問那台計算機的許可權，就是要通過windows賬戶登陸進去才可以添加此計數器。 2.通過系統表來查詢： SELECT * FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='databaseName' ) databaseName 是需要查看的資料庫，然後查詢出來的行數，就是當前的sql server資料庫連接數。不過裡面還有一些別的狀態可以做參考用。 3.通過系統過程來查詢： SP_WHO 'loginName' loginName 是當然登陸Sql的用戶名，一般程序裡面都會使用一個username來登陸SQL這樣通過這個用戶名就能查看到此用戶名登陸之後佔用的連接了。 如果不寫loginName，那麼返回的就是所有的sql server資料庫連接。 至於如何改善資料庫性能，就是屬於資料庫調優方面的工作了，通常有以下幾種調優方法： 1 查看資料庫中造成資料庫訪問變慢的語句，通常是執行數量較多，執行速度慢的語句，對這些語句進行執行計劃分析，並重寫語句來優化，最常見的就是not in語句使用外連接語句代替； 2 根據語句中查詢訪問條件中的謂詞，創建對應的索引，以提高查詢的執行效率； 3 在數據存儲上優化，將數據文件根據某個頻繁訪問屬性的屬性值進行水平分片，提高對應表的訪問效率（oracle支持，sql server2000沒有此功能） 4 重新設計業務邏輯結構，避免執行代價高的查詢語句 5 伺服器和資料庫軟體的能力終究還是有限的，無論如何優化當達到一定的訪問數量是還是會超出負載，此時就需要考慮可擴展規模的分布式並行數據存儲架構了。

⑺ 如何進行SQL性能優化

這里分享下mysql優化的幾種方法。

1、首先在打開的軟體中，需要分別為每一個表創建 InnoDB FILE的文件。

⑻ SQL 語句 優化 性能提升

經常見到有新手寫出這樣的語句。本質上是沒有想清楚要查詢什麼數據。
其實，只要轉換一下思路，用連接來完成就行了。不要用這么多in和not in。
目的就是直接查你想要的東西，而不要把不想要的東西都查出來，再用in和not in過濾掉。
這無異於把整個圖書館的書都搬回家，然後留下一本之後其他的又都送回去一樣。累死資料庫不償命啊。

⑼ 如何測試sql語句性能，提高執行效率，sql2008

一段SQL代碼寫好以後，可以通過查看SQL的執行計劃，初步預測該SQL在運行時的性能好壞，尤其是在發現某個SQL語句的效率較差時，我們可以通過查看執行計劃，分析出該SQL代碼的問題所在。 1、 打開熟悉的查看工具：PL/SQL Developer。 在PL/SQL Developer中寫好一段SQL代碼後，按F5，PL/SQL Developer會自動打開執行計劃窗口，顯示該SQL的執行計劃。 2、 查看總COST，獲得資源耗費的總體印象 一般而言，執行計劃第一行所對應的COST(即成本耗費)值，反應了運行這段SQL的總體估計成本，單看這個總成本沒有實際意義，但可以拿它與相同邏輯不同執行計劃的SQL的總體COST進行比較，通常COST低的執行計劃要好一些。 3、 按照從左至右，從上至下的方法，了解執行計劃的執行步驟 執行計劃按照層次逐步縮進，從左至右看，縮進最多的那一步，最先執行，如果縮進量相同，則按照從上而下的方法判斷執行順序，可粗略認為上面的步驟優先執行。每一個執行步驟都有對應的COST,可從單步COST的高低，以及單步的估計結果集（對應ROWS/基數），來分析表的訪問方式，連接順序以及連接方式是否合理。 4、 分析表的訪問方式 表的訪問方式主要是兩種：全表掃描（TABLE ACCESS FULL）和索引掃描(INDEX SCAN)，如果表上存在選擇性很好的索引，卻走了全表掃描，而且是大表的全表掃描，就說明表的訪問方式可能存在問題；若大表上沒有合適的索引而走了全表掃描，就需要分析能否建立索引，或者是否能選擇更合適的表連接方式和連接順序以提高效率。 5、 分析表的連接方式和連接順序 表的連接順序：就是以哪張表作為驅動表來連接其他表的先後訪問順序。 表的連接方式：簡單來講，就是兩個表獲得滿足條件的數據時的連接過程。主要有三種表連接方式，嵌套循環（NESTED LOOPS）、哈希連接（HASH JOIN）和排序-合並連接（SORT MERGE JOIN）。我們常見得是嵌套循環和哈希連接。 嵌套循環：最適用也是最簡單的連接方式。類似於用兩層循環處理兩個游標，外層游標稱作驅動表，Oracle檢索驅動表的數據，一條一條的代入內層游標，查找滿足WHERE條件的所有數據，因此內層游標表中可用索引的選擇性越好，嵌套循環連接的性能就越高。 哈希連接：先將驅動表的數據按照條件欄位以散列的方式放入內存，然後在內存中匹配滿足條件的行。哈希連接需要有合適的內存，而且必須在CBO優化模式下，連接兩表的WHERE條件有等號的情況下才可以使用。哈希連接在表的數據量較大，表中沒有合適的索引可用時比嵌套循環的效率要高。

⑽ 如何進行SQL性能優化

這里分享下mysql優化的幾種方法。

1、首先在打開的軟體中，需要分別為每一個表創建 InnoDB FILE的文件。