C?i thi?n hi?u su?t c?a cc vn b?n ?y ? truy v?n trong SQL Server

D?ch tiu ? D?ch tiu ?
ID c?a bi: 2549443 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

Tm t?t

Bi vi?t ny m t? m?t phng php ? c?i thi?n hi?u su?t truy v?n Microsoft SQL Server s? d?ng tra c?u vn b?n ?y ? predicates (ch?ng h?n nh ch?a v CONTAINSTABLE) v c?ng l?c d? li?u. V d?, phng php ny c?i thi?n hi?u su?t c?a truy v?n sau y:
select * from dbo.ftTest where CONTAINS(TextData, '"keyword"') and CDate > @date
Phng php ny cho php b?n thi?t k? cc truy v?n, l?c ? b?ng, v vn b?n ?y ? ch? trong m?t cch m cc m t tim kim vn b?n ?y ? b? l?c ra k?t qu? tr?c khi chng ?c g?i ?n c quan h?. V? v?y, cc ?ng c quan h? khng ph?i l?c m?t t?p d? li?u l?n.

Thng tin thm

Khi b?n t?o m?t truy vn tim kim vn b?n ?y ?, cc y?u t? nguyn t?c ?nh h?ng ?n hi?u su?t c?a cc truy v?n l s? l?ng d? li?u cng c? vn b?n ?y ? tra c?u ph?i x? l? tr?c khi ph?n c?n l?i d? li?u ?c g?i ?n c quan h?. Trong SQL Server, b?n c th? c?i thi?n hi?u su?t c?a cc truy v?n b?ng cch l?c ra hng ?u ? gi?m s? l?ng hng m ph?i ?c x? l? sau ny.

Trong phin b?n c?a SQL Server ? ?c pht hnh tr?c khi SQL Server 2008, cng c? vn b?n ?y ? tra c?u tr? v? t?t c? cc hng ph h?p v?i c?m t? tra c?u, v sau ?ng c quan h? p d?ng b?t k? b? l?c. C?i ti?n cho hnh vi ny ?c th?c hi?n trong SQL Server 2008, SQL Server 2008 R2 v SQL Server 2012. Tuy nhin, n l kh khn ? s? d?ng cc c?i ti?n ny b?i v? ton b? n?i dung tra c?u ch? s? ?c t? ch?c r?t khc nhau t? b? my c s? d? li?u ch? s?. Ngoi ra, cc ?ng c tra c?u vn b?n ?y ? v ?ng c quan h? lm vi?c r?t khc nhau. V? v?y, phng php m bi vi?t ny m t? s? d?ng Table-Valued ch?c nng (TVF) ? l?c ra hng ?u v ? gi?m b?t s? l?ng hng m ph?i ?c x? l? sau ny.

V d?, k? ho?ch truy v?n sau y tr? v? 131051 hng ph h?p v?i m?t chu?i tra c?u ch?a. Ngoi ra, m?t nh i?u hnh tham gia trong k? ho?ch th?c hi?n b? l?c b? sung b?ng cch s? d?ng m?t m?c ch? d?n tra c?u.
Rows StmtText 
-------------------- -----------------------------------------------------------------------------------------------------------
1167 select CDate, ID from dbo.fttest where contains (c2, '"create"') and CDate> '08/05/2019' 

1167 |--Merge Join(Left Semi Join, MERGE:([FTSdb].[dbo].[fttest].[ID])=(FulltextMatch.[docid]), RESIDUA
5858 |--Sort(ORDER BY:([FTSdb].[dbo].[fttest].[ID] ASC)) 
5858 | |--Clustered Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[clidx1]), SEEK:([FTSdb].[
131051 |--Table-valued function 
Tuy nhin, n?u cc truy v?n bao g?m cc vn b?n ?y ? ch? s? duy nh?t quan tr?ng c?t l m?t predicate, cc m t tim kim vn b?n ?y ? c th? dng predicate ? l?c cc k?t qu? ? vn b?n ?y ?. Trong t?nh hu?ng ny, TVF tr? l?i m?t s? l?ng nh? hn nhi?u d? li?u tr?c khi b? l?c b? sung ph?i ?c p d?ng. V d?, truy v?n sau y xc ?nh gi tr? nm ph?i ph h?p v?i i?u ki?n c2, v TVF tr? l?i ch? nh?ng k?t qu? ph h?p v?i cc gi tr? nm:
Rows StmtText 

-------- ---------------------------------------------------------------------------------------------------------------------------------
5 select CDate, ID from dbo.fttest where contains (c2, '"create"') and CDate > '08/05/2019' and ID in ( 654051, 644051, 649106, 465, 105)

5 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([FTSdb].[dbo].[fttest].[ID])) 
5 |--Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[idx1]), SEEK:([FTSdb].[dbo].[fttest].[ID]=(105) OR ...
5 |--Table-valued function
?ng c tra c?u vn b?n ?y ? kh? nng ? ?y xu?ng cc gi tr? ?c s? d?ng b?i duy nh?t ch? s? chnh l n?n t?ng c?a phng php sau.

N?u m?t predicate c ch?a m?t c?t ki?u DateTime d? li?u, b?n c th? bao g?m thng tin ngy trong c?t quan tr?ng duy nh?t m?c ch? d?n ? ch? cc hng ph h?p v?i ny predicate ?c pht ra t?. ? lm i?u ny, b?n ph?i h?p l? k?t h?p thng tin ngy trong c?t quan tr?ng. Tuy nhin, b?n c?ng c th? c ? thay ?i ki?u d? li?u c?t quan tr?ng v cc ?ng d?ng s? d?ng cc truy v?n.

? th?c hi?n cc phng php, thay ?i ki?u d? li?u ?y ?-o?n ?c o key ID ? BIGINT. 4 Byte ?u tin ch?p ID chnh nm, thng v ngy gi tr? t? c?t ngy, v 4 byte cu?i v?n nh c?. V d?, cc byte ?u tin c?a key ID c th? ? c?p ?n nm, cc byte ti?p theo c th? ? c?p ?n thng, v hai byte cu?i c th? ? c?p ?n ngy. Cc ?ng d?ng ph?i ph h?p v?i s? thay ?i ki?u d? li?u ny.

Sau , D?ch predicate ph?m vi ? m?t predicate trn phm c?a b?n. V d?: "x<>< y"="" range="" predicate="" can="" be="" translated="" to="" the="" "(x*2^32="">< id="">< y*2^32)"="" predicate.="" because="" the="" translated="" predicate="" is="" a="" predicate="" on="" the="" full-text="" key,="" the="" predicate="" will="" be="" pushed="" down="" into="" the="" full-text="" streaming="" table-valued="" functions="" (stvf).="" this="" behavior="" effectively="" performs="" searches="" within="" the="" date="">

Thu?c tnh

ID c?a bi: 2549443 - L?n xem xt sau cng: 22 Thang Ba 2013 - Xem xt l?i: 2.0
p d?ng
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2012 Enterprise
T? kha:
kbmt KB2549443 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny: 2549443

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com