我必須對表執行全域搜索意味著如果用戶輸入任何關鍵字或多個關鍵字并單擊搜索按鈕，那么根據輸入的關鍵字它應該帶來所有組合記錄。
我們必須在表的每一列中搜索這 2 個關鍵字（SQL 中的 Like 子句，使用 OR 運算子獲取多個關鍵字）并且查詢應該獲取資料。
我在資料庫中有大約 20 萬條記錄。
首先呼叫函式來加載資料
if ((Role)user.Role == Role.InternalAdministrator || (Role)user.Role ==
Role.InternalStaff)
{
listJobs = (
from d in db.Jobs
where d.TimeCreated.Value.Year >= 2020
select new JobModel()
{
AlternatePickupDelivery = d.AlternatePickupDelivery,
Branch = (
from b in db.Branches
where b.BranchId == d.ProcessingCity
select b.Branch1
).FirstOrDefault(),
ClientName = d.ClientName,
ClientId = d.ClientId,
ContactName = d.ContactName,
MatterReference = d.MatterReference,
JMSNumber = d.JmsNumber,
JobDescription = d.JobDescription,
JobId = d.JobId,
JobShortDescription = d.JobShortDescription,
OrderType = d.OrderType,
OrderTypeDisplay = (
from jt in db.JobTypes
where jt.Id == d.OrderType
select jt.JobTypeName
).FirstOrDefault(),
ProcessingCity = d.ProcessingCity ?? 0,
DisplayProcessingCity = (
from jt in db.ProcessingCities
where jt.ProcessingCityId == d.ProcessingCity
select jt.ProcessingCity1
).FirstOrDefault(),
Status = d.Status,
DisplayStatus = (
from jt in db.JobStatuses
where jt.Id == d.Status
select jt.JobStatusName
).FirstOrDefault(),
StatusDisplayOrder = (from js in db.JobStatuses
where js.Id == d.Status
select js.DisplayOrder).FirstOrDefault(),//d.JobStatus.DisplayOrder,
StatusLastModifiedBy = (
from u in db.Users
where (u.UserId == d.StatusLastModifiedById)
select u.FirstName " " u.LastName
).FirstOrDefault(),
StatusLastModifiedById = d.StatusLastModifiedById,
StatusLastModified = d.StatusLastModified ?? DateTime.UtcNow,
CreatedByDisplay = (
from u in db.Users
where (u.UserId == d.CreatedById)
select u.FirstName " " u.LastName
).FirstOrDefault(),
CreatedById = d.CreatedById,
ModifiedByDisplay = (
from u in db.Users
where (u.UserId == d.LastModifiedById)
select u.FirstName " " u.LastName
).FirstOrDefault(),
LastModifiedById = d.LastModifiedById,
TimeCreated = d.TimeCreated ?? DateTime.UtcNow,
TimeDelivered = (d.Status == (int)JMS4.Utilities.JobStatus.Delivered) ? d.StatusLastModified :
null,
TimeDue = d.TimeDue ?? DateTime.UtcNow,
TimeReady = d.TimeReady ?? DateTime.UtcNow,
TimeZoneId = timeZoneId.ToString(),
ExtClientId = d.ExtClientId,
Address = d.Address,
ReceivedBy = d.ReceivedBy,
ContactPhone = d.ContactPhone,
AfterHourContactNumber = d.AfterHoursContactNumber,
Email = d.Email,
CostEstimateNumber = d.CostEstimateNumber,
LastModifiedBy = d.LastModifiedBy,
MatterType = d.MatterType,
QaData = d.QaData,
InternalInstructions = d.InternalInstructions,
GlobalSearch = d.GlobalSearch
}
);
如果搜索文本框有任何要搜索的關鍵字/關鍵字，則呼叫第二個函式
jobs = jobs.Where(x => x.JMSNumber.ToLower().Contains(keyword.ToLower())
|| (x.ClientName != null && x.ClientName.ToLower().Contains(keyword.ToLower()))
|| (x.MatterReference != null && x.MatterReference.ToLower().Contains(keyword.ToLower()))
|| (x.ContactName != null && x.ContactName.ToLower().Contains(keyword.ToLower()))
|| (x.JobShortDescription != null &&
x.JobShortDescription.ToLower().Contains(keyword.ToLower()))
|| (x.StatusLastModifiedBy != null &&
x.StatusLastModifiedBy.ToLower().Contains(keyword.ToLower()))
|| (x.Address != null && x.Address.ToLower().Contains(keyword.ToLower()))
|| (x.Email != null && x.Email.ToLower().Contains(keyword.ToLower()))
|| (x.LastModifiedBy != null &&
x.LastModifiedBy.ToString().ToLower().Contains(keyword.ToLower()))
|| (x.CostEstimateNumber != null &&
x.CostEstimateNumber.ToLower().Contains(keyword.ToLower()))
|| (x.ClientId != null && x.ClientId.ToString().ToLower().Contains(keyword.ToLower()))
|| (x.JobDescription != null && !String.IsNullOrEmpty(x.JobDescription.ToString()) &&
x.JobDescription.ToString().ToLower().Contains(keyword.ToLower()))
|| (x.CreatedByDisplay != null && !String.IsNullOrEmpty(x.CreatedByDisplay.ToString()) &&
x.CreatedByDisplay.ToString().ToLower().Contains(keyword.ToLower()))
|| (x.ModifiedByDisplay != null && !String.IsNullOrEmpty(x.ModifiedByDisplay.ToString()) &&
x.ModifiedByDisplay.ToString().ToLower().Contains(keyword.ToLower()))
|| (x.InternalInstructions != null &&
x.InternalInstructions.ToString().ToLower().Contains(keyword.ToLower()))
);
使用這些查詢后，獲取記錄需要超過 3 分鐘。
請建議我如何提高搜索性能并優化查詢。
uj5u.com熱心網友回復：
要針對資料庫優化這樣的查詢，有一些規則可以嘗試并遵循
- 確保查詢傳遞到資料庫，不要在記憶體中操作
- 洗掉或減少函式的使用
- 不要費心比較空值
- 將查詢拆分為多個并行查詢
- 改進結構以優化查詢
一般的想法是，您希望直接在索引條目中進行比較，對資料庫中的記錄進行函式或轉換不會使用索引。資料庫專門針對索引進行了優化，因此在搜索列上創建必要的索引也很重要。
您已將此標記為linq 到 sql所以我們假設您的查詢正在傳遞到資料庫，確保它確實是很重要的。以下代碼和建議僅在 LINQ 運算式是真實
IQueryable<T>的且將被決議為 SQL 時才有效。
如果您的資料庫使用CASE INSENSITIVE排序規則，那么您可以洗掉所有
.ToLower()函式呼叫，您希望避免函式呼叫，以便可以直接訪問任何索引。- 盡管 C#默認情況下對大小寫敏感，但如果將 LINQ 查詢轉換為 SQL，那么它將遵循排序規則設定以進行標準
LIKE '%' @param '%'比較。
- 盡管 C#默認情況下對大小寫敏感，但如果將 LINQ 查詢轉換為 SQL，那么它將遵循排序規則設定以進行標準
跳過空比較，就像
.ToLower()在 SQL 中沒有必要在對該欄位執行比較之前先檢查欄位的可空性一樣。
這已經是一個更好的過濾器：
jobs = jobs.Where(x => x.JMSNumber.Contains(keyword)
|| x.ClientName.Contains(keyword)
|| x.MatterReference.Contains(keyword)
|| x.ContactName.Contains(keyword)
|| x.JobShortDescription.Contains(keyword)
|| x.StatusLastModifiedBy.Contains(keyword)
|| x.Address.Contains(keyword)
|| x.Email.Contains(keyword))
|| x.LastModifiedBy.Contains(keyword))
|| x.CostEstimateNumber.Contains(keyword))
|| x.ClientId.ToString().Contains(keyword))
|| x.JobDescription.Contains(keyword))
|| x.CreatedByDisplay.Contains(keyword))
|| x.ModifiedByDisplay.Contains(keyword))
|| x.InternalInstructions.Contains(keyword))
);
- 以可搜索的格式存盤值。
如果您確實需要在數字欄位上進行搜索，我們可以比上面做得更好，例如在這種情況下ClientId，將數值存盤在基于字串的列中會有所幫助，因為我們的搜索引數是一個字串。在資料庫中實作欄位變體的最簡單方法是使用計算列，但是它需要是寫入計算列或持久列才能實作搜索索引的好處。使計算列脫離運算式：
CAST(ClientId as char(10))
相同的規則適用于可能需要對其應用函式的任何其他列，如果將函式評估移動到記錄為INSERT或的時間，您將看到更高的性能UPDATE，這發生在通過讀取的頻率要低得多SELECT。
- Normalize the structure, most of your comparisons are on a user - displayname if the query joins on to a user table, then you only have one seek for any user that matches instead of 1 separate seek for each user
There is a clear related table here for the User who is applying the data modifications. This can add a great deal of redundant information in the search query. Ideally we do not search across the user fields, as any match there would bring up all records that are associated with them, it is not usually a good search candidate, unless users do not edit many records. So if you can, exclude them from the general search, and allow the user to pick from a list of users to scope the results, or to search from the users in parallel with the main search
Now the search is much quicker: (this assumes a new column called ClientIdString)
jobs = jobs.Where(x => x.JMSNumber.Contains(keyword)
|| x.ClientName.Contains(keyword)
|| x.MatterReference.Contains(keyword)
|| x.ContactName.Contains(keyword)
|| x.JobShortDescription.Contains(keyword)
|| x.Address.Contains(keyword)
|| x.Email.Contains(keyword))
|| x.CostEstimateNumber.Contains(keyword))
|| x.ClientIdString.Contains(keyword))
|| x.JobDescription.Contains(keyword))
|| x.InternalInstructions.Contains(keyword))
);
Hypothetical User Search for searching and then filtering by the users:
var userIds = db.Users.Where(u => u.UserName.Contains(keyword))
.Select(u => u.Id)
.ToList();
//Filter to only rows that match the user lookup
if (jobs.Any())
{
jobs = jobs.Where(x => userIds.Contains(x.StatusLastModifiedByUserId)
|| userIds.Contains(x.LastModifiedByUserId)
|| userIds.Contains(x.CreatedByUserId)
|| userIds.Contains(x.ModifiedByUserId)
);
}
If the schema is NOT already normalised, then I strongly suggest you do at least normalise out the users into their own table, indexing the possible users is much more efficient than searching across 200K records.
It is also possible that we can write the query directly in SQL. Sometimes we can write much more efficient SQL by hand than we might be able to achieve through LINQ don't feel bad about that, just recognise that it is one of many tools at your disposal.
- Most Linq providers will give you an explicit mechanism for executing raw SQL that will return into a linq expression. The detail for this is out of scope, but searching is a specific scenario where this is accepted.
There are other external options too like SQL Server Full Text Search or MySQL FULLTEXT Indexes or even Microsoft Azure Search or Elastic Search. These external mechanisms can be used to return the search content directly or they might return references that you can use to access the records in your local DB.
- Many NoSQL providers can be used to construct an efficient search index, the products I listed above as simply designed for searching and are likely to implement a lot of industry
Indexes
All of the above assumes that you have implemented adequate indexes on the underlying data store. Searching can have such a large impact on the user experience, it is worth putting in the effort to get it right.
- Assessing and Implementing Indexes is out of scope for this question
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/431553.html