英文:
How to optimize my query or code for my search page using C#asp.net
问题
我正在尝试优化我的查询以加快加载速度，因为一旦数据达到5000条，记录就会在2分钟后到达。
我正在使用两个存储过程来进行搜索。第一个存储过程用于根据搜索参数获取候选人详情，第二个存储过程用于按候选人ID计数，因此将候选人ID从第一个存储过程的结果发送到第二个存储过程。稍后在循环结果中匹配候选人ID。
以下是我的第一个存储过程查询：
declare @FirstName varchar(500)='',
@LastName varchar(500)='',
@Phone varchar(500)='',
@Email varchar(500)='',
@Specialty varchar(500)='',
@AdditionalLicensedStates varchar(500)='AL',
@DesiredLocation varchar(50)='',
@CompactState varchar(50)=''
SET NOCOUNT ON;
declare @query nvarchar(max)=' '
set @query=@query+
'SELECT
T.CandidateId,''''Ranking,T.[Status], t.FirstName,t.LastName, t.Phone, t.[Email],t.ID AS TroveId
,t.Desired,t.Licensed,t.Specialty,t.CallBack,t.LastContact
,t.LastContactNote,0 MatchingJobs ,t.AvailableDate ,T.DepartmentId
,0 DocumentId,''''Division,''''EMRSystem,''''Application,T.Compactstate
FROM (
select T.CandidateId,T.Status, C.FirstName,C.LastName, C.Phone, C.Email,T.ID,T.DepartmentId,j.DesiredStates as Desired,
j.Licensed,j.Specialty,T.Compactstate,j.AvailableDate,cb.CallbackDate CallBack,convert(date,n.DateCreated) LastContact,
n.Notes LastContactNote from T1 T(NOLOCK)
INNER JOIN Candidate C(NOLOCK) on T.CandidateId= C.Id and T.DepartmentId=1 AND T.Profile=''0''
INNER JOIN T2 P(NOLOCK) on T.CandidateId=P.TroveCandidateId
LEFT join T3(Nolock) j on j.CandidateId=T.CandidateId
Inner join T4 js on js.jackieId=j.Id
LEFT JOIN dbo.T5(Nolock) N ON N.ObjectID = T.CandidateId and n.IsLatest=1 and n.Category=''3'' and n.NoteType=''NormalNote''
LEFT JOIN T6(Nolock) cb on cb.CandidateId=t.CandidateId and cb.IsLatest=1 and cb.CallBackType=''Trove''
where
('''+@FirstName+''' ='''' or C.FirstName like ''%'+@FirstName+'%'') AND
('''+@LastName+''' ='''' or C.LastName like ''%'+@LastName+'%'') AND
('''+@Phone+''' ='''' or C.Phone ='''+@Phone+''') AND
('''+@Email+''' ='''' or C.Email like ''%'+@Email+'%'') AND
('''+@Specialty+''' ='''' or cast(Js.specialtyId as varchar(10)) = '''+@Specialty+''') AND
('''+@AdditionalLicensedStates+''' ='''' or J.Licensed like ''%'+@AdditionalLicensedStates+'%'') AND
('''+@DesiredLocation+''' ='''' or J.DesiredStates like ''%'+@DesiredLocation+'%'') AND
('''+@CompactState+''' ='''' or t.CompactState like ''%'+@CompactState+'%'')
) as t '
-- print @query
exec (@query)
优化查询或代码。
英文:
I am trying to optimize my query for loading should be fast because once the data is 5000 thousand then
records is coming after 2 minute of time.
I am using two sps for my search for first SP by search Parameters getting candidate details and In second SP using for counts by candidate Id so sending candidate ids to second sp from first sp result. Later from Foreach result match candidate id
Below is my First SP query
declare @FirstName varchar(500)='',
@LastName varchar(500)='',
@Phone varchar(500)='',
@Email varchar(500)='',
@Specialty varchar(500)='',
@AdditionalLicensedStates varchar(500)='AL',
@DesiredLocation varchar(50)='',
@CompactState varchar(50)=''
SET NOCOUNT ON;
declare @query nvarchar(max)=' '
set @query=@query+
'SELECT
T.CandidateId,''''Ranking,T.[Status], t.FirstName,t.LastName, t.Phone, t.[Email],t.ID AS TroveId
,t.Desired,t.Licensed,t.Specialty,t.CallBack,t.LastContact
,t.LastContactNote,0 MatchingJobs ,t.AvailableDate ,T.DepartmentId
,0 DocumentId,''''Division,''''EMRSystem,''''Application,T.Compactstate
FROM (
select T.CandidateId,T.Status, C.FirstName,C.LastName, C.Phone, C.Email,T.ID,T.DepartmentId,j.DesiredStates as Desired,
j.Licensed,j.Specialty,T.Compactstate,j.AvailableDate,cb.CallbackDate CallBack,convert(date,n.DateCreated) LastContact,
n.Notes LastContactNote from T1 T(NOLOCK)
INNER JOIN Candidate C(NOLOCK) on T.CandidateId= C.Id and T.DepartmentId=1 AND T.Profile=''0''
INNER JOIN T2 P(NOLOCK) on T.CandidateId=P.TroveCandidateId
LEFT join T3(Nolock) j on j.CandidateId=T.CandidateId
Inner join T4 js on js.jackieId=j.Id
LEFT JOIN dbo.T5(Nolock) N ON N.ObjectID = T.CandidateId and n.IsLatest=1 and n.Category=''3'' and n.NoteType=''NormalNote''
LEFT JOIN T6(Nolock) cb on cb.CandidateId=t.CandidateId and cb.IsLatest=1 and cb.CallBackType=''Trove''
where
('''+@FirstName+''' ='''' or C.FirstName like ''%'+@FirstName+'%'') AND
('''+@LastName+''' ='''' or C.LastName like ''%'+@LastName+'%'') AND
('''+@Phone+''' ='''' or C.Phone ='''+@Phone+''') AND
('''+@Email+''' ='''' or C.Email like ''%'+@Email+'%'') AND
('''+@Specialty+''' ='''' or cast(Js.specialtyId as varchar(10)) = '''+@Specialty+''') AND
('''+@AdditionalLicensedStates+''' ='''' or J.Licensed like ''%'+@AdditionalLicensedStates+'%'') AND
('''+@DesiredLocation+''' ='''' or J.DesiredStates like ''%'+@DesiredLocation+'%'') AND
('''+@CompactState+''' ='''' or t.CompactState like ''%'+@CompactState+'%'')
) as t '
-- print @query
exec (@query)
Optimize quyer or code
答案1
得分: 4
以下是翻译好的部分：
危险
您的代码容易受到注入攻击的威胁。这非常危险。您将参数注入到语句中，使其不安全。您使用的 EXEC(@SQL) 语法也不帮助此问题，因为它使得参数化语句变得不可能。请使用 sys.sp_executesql 来执行您的语句并进行参数化。
弃用
您正在使用已弃用的 FROM <TableName> (<Hint>) 语法，应改为 FROM <TableName> WITH (<Hint>)，不过，谈到您的表提示...
(滥用) NOLOCK
您在这里滥用了 NOLOCK 提示。Aaron Bertrand 在 Bad habits : Putting NOLOCK everywhere 中深入讨论了这个主题，然而，您很可能不需要任何这些提示。如果您“必须”（我强烈怀疑），请更改隔离级别，而不要对每个表都使用这个提示。
非 SARGable 子句
几乎所有您的子句都不是 SARGable。带有通配符前缀的查询无法使用索引，而几乎所有您的子句都是这样的。唯一不是的之一是对 specialtyId 的，它将该列转换为 varchar，也使其不是 SARGable。如果您没有注入您的值，也许您可以使用有效的数据类型对其进行参数化。
包罗万象
您这里使用的是所谓的“Catch-all”或“厨房水槽”查询，不过您没有很好地处理这个问题。您仍然检查了每个变量的值，但如果您使用动态 SQL，那么应该只传递具有非NULL值的变量（您正在使用 NULL 而不是 '' 表示“没有值”，对吗？）。因为您使用了动态语句，我会继续这条路线。
隐式 INNER JOIN
您对 Jackie 的 LEFT JOIN 实际上是隐式的 INNER JOIN，因为它的列 id 必须具有非NULL值以便在 jackieSpecialty 的 INNER JOIN 中使用。不要对必须从中返回值的表使用 LEFT JOIN。
格式化
格式需要进行很多工作。诸如 ''''Application 这样的语法非常令人困惑（记住这是在动态语句中）。使用 AS 或 = 语法，如 '''' AS Application 或 Application = ''''。更不用说您的许多语句都在一行上，很难阅读。
错误的别名
您的派生表被别名为 tl，但您的外部 SELECT 引用的是 t。
解决方案
这只是用来检查是否创建了有效语句的未经测试的解决方案，但对于某些参数来说，可能会更快（@Specialty 和 @Phone）。对于其他参数来说，也许会更快，因为我使查询仅传递了具有非NULL值的变量的子句（请注意，是NULL，不是 ''）。正如我提到的，带有通配符前缀的查询无法使用索引，因此如果需要更好的性能，您应该重新考虑是否应该在这里使用它们。
这不能针对您的表进行测试，变量的数据类型是猜测的。如果遇到错误，请使用您的好朋友来帮助调试（在我看来，我在这里做了太多的工作）。
-- 以下所有数据类型都是猜测的
DECLARE @FirstName nvarchar(50),
@LastName nvarchar(50),
@Phone varchar(15),
@Email varchar(100),
@Specialty int = 5,
@AdditionalLicensedStates varchar(10),
@DesiredLocation varchar(50),
@CompactState varchar(5);
DECLARE @query nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @query= N'SELECT t.CandidateId,' + @CRLF +
N' '''' AS Ranking,' + @CRLF +
N' t.[Status],' + @CRLF +
N' t.FirstName,' + @CRLF +
N' t.LastName,' + @CRLF +
N' t.Phone,' + @CRLF +
N' t.[Email],' + @CRLF +
N' t.ID AS TroveId,' + @CRLF +
N' t.Desired,' + @CRLF +
N' t.Licensed,' + @CRLF +
N' t.Specialty,' + @CRLF +
N' t.CallBack,' + @CRLF +
N' t.LastContact,' + @CRLF +
N' t.LastContactNote,' + @CRLF +
N' 0 AS MatchingJobs,' + @CRLF +
N' t.AvailableDate,' + @CRLF +
N' t.DepartmentId,' + @CRLF +
N' 0 AS DocumentId,' + @CRLF +
N' '''' AS Division,' + @CRLF +
N' '''' AS EMRSystem,' + @CRLF +
N' '''' AS Application,' + @CRLF
<details>
<summary>英文:</summary>
There is *a lot* to unpack here. Firstly, let's start with the dangerous:
## Dangerous
Your code is *wide open* to injection attacks. This is incredibly dangerous. You are injecting your parameters into your statement, making it insecure. Your use of `EXEC(@SQL)` syntax doesn't help the matter as it makes it **impossible** to parametrise your statement. Use `sys.sp_executesql` to execute your statement and **parametrise** it.
## Deprecations
You are using `FROM <TableName> (<Hint>)` syntax, which is deprecated. It should be `FROM <TableName> WITH (<Hint>)`, however, speaking of your table hints...
## (Ab)use of `NOLOCK`
You're (ab)using the `NOLOCK` hint here. Aaron Bertrand covers this subject in depth in [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere), however, you likely don't need *any* of those hints. If you "must" (which I strongly doubt) use it change the isolation level, don't spam the hint against every table.
## Non-SARGable clauses
Almost *all* your clauses aren't SARGable. Queries with a leading wildcard can't use indexes and almost *all* of your clauses do this. One of the only that doesn't, against `specialtyId` `CAST`s that column to a `varchar` making it also non-SARGable. If you didn't inject your value, perhaps you could parametrise it with a valid data type.
## Everything but the kitchen-sink
What you have here is known as a "[Catch-all](https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/)" or "[Kitchen Sink](https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example)" query, however, you don't handle the problem well. You still check the value of *every* variable but if you're using dynamic SQL then you should only pass the variables that have a non-`NULL` value (you are using `NULL` rather than `''` for "no value, right?). As you're using a dynamic statement, I continue down that route.
## Implicit INNER JOIN
Your `LEFT JOIN` to `Jackie` was an implicit `INNER JOIN` as its column `id` *had* to have a non-`NULL` value in the `INNER JOIN` to `jackieSpecialty`. Don't use `LEFT JOIN`s for tables that must have a value returned from them.
## Formatting
The formatting needed *a lot* of work too. Syntax like `''''Application` is very confusing (remember that's in a dynamic statement). Use `AS` or `=` syntax such as `'''' AS Application` or `Application = ''''`. Not to mention many of your statments were on a single line and are hard to read.
#### Incorrect Alias
Your derived table was alaised as `tl` but your outer `SELECT` references `t`.
# Solution
This is untested apart from to check that it creates a valid statement, but for some of your parameters should be faster (`@Specialty` and `@Phone`). For others, it might be, as I make the query only pass the clauses for the variables that have a non-`NULL` value (**note** `NULL`, not `''`). As mentioned, leading wildcards aren't SARGable, so if you need better performance, you should reconsider if you should really be using them here.
This can't be tested against your table, and the data types for variables are guessed. Use your best friend to help debug this if you encounter errors (I've done too much here in my opinion):
```sql
--All following datatypes are GUESSED
DECLARE @FirstName nvarchar(50),
@LastName nvarchar(50),
@Phone varchar(15),
@Email varchar(100),
@Specialty int = 5,
@AdditionalLicensedStates varchar(10),
@DesiredLocation varchar(50),
@CompactState varchar(5);
DECLARE @query nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @query= N'SELECT t.CandidateId,' + @CRLF +
N' '''' AS Ranking,' + @CRLF +
N' t.[Status],' + @CRLF +
N' t.FirstName,' + @CRLF +
N' t.LastName,' + @CRLF +
N' t.Phone,' + @CRLF +
N' t.[Email],' + @CRLF +
N' t.ID AS TroveId,' + @CRLF +
N' t.Desired,' + @CRLF +
N' t.Licensed,' + @CRLF +
N' t.Specialty,' + @CRLF +
N' t.CallBack,' + @CRLF +
N' t.LastContact,' + @CRLF +
N' t.LastContactNote,' + @CRLF +
N' 0 AS MatchingJobs,' + @CRLF +
N' t.AvailableDate,' + @CRLF +
N' t.DepartmentId,' + @CRLF +
N' 0 AS DocumentId,' + @CRLF +
N' '''' AS Division,' + @CRLF +
N' '''' AS EMRSystem,' + @CRLF +
N' '''' AS Application,' + @CRLF +
N' t.Compactstate ' + @CRLF +
N'FROM (SELECT T.CandidateId,' + @CRLF +
N' T.Status,' + @CRLF +
N' C.FirstName,' + @CRLF +
N' C.LastName,' + @CRLF +
N' C.Phone,' + @CRLF +
N' C.Email,' + @CRLF +
N' T.ID,' + @CRLF +
N' T.DepartmentId,' + @CRLF +
N' j.DesiredStates AS Desired,' + @CRLF +
N' j.Licensed,' + @CRLF +
N' j.Specialty,' + @CRLF +
N' T.Compactstate,' + @CRLF +
N' j.AvailableDate,' + @CRLF +
N' cb.CallbackDate AS CallBack,' + @CRLF +
N' CONVERT(date,n.DateCreated) AS LastContact,' + @CRLF +
N' n.Notes AS LastContactNote' + @CRLF +
N' FROM dbo.TreasureTrove T' + @CRLF +
N' INNER JOIN dbo.Candidate C ON T.CandidateId = C.Id' + @CRLF +
N' AND T.DepartmentId = 1' + @CRLF +
N' AND T.Profile = ''0''' + @CRLF + --If this is 0, why not compare to a literal int?
N' INNER JOIN dbo.ProfileLinked P ON T.CandidateId = P.TroveCandidateId' + @CRLF +
N' INNER JOIN dbo.Jackie j ON j.CandidateId = T.CandidateId' + @CRLF + -- As j.Id must have a non-NULL value, changed to an INNER JOIN
N' INNER JOIN dbo.jackieSpecialty js ON js.jackieId = j.Id' + @CRLF +
N' LEFT JOIN dbo.Notes N ON N.ObjectID = T.CandidateId' + @CRLF +
N' AND n.IsLatest = 1 ' + @CRLF +
N' AND n.Category = ''3''' + @CRLF + --If this is a 3, why not compare to a literal int?
N' AND n.NoteType = ''NormalNote''' + @CRLF +
N' LEFT JOIN dbo.CallBack cb ON cb.CandidateId = t.CandidateId' + @CRLF +
N' AND cb.IsLatest = 1' + @CRLF +
N' AND cb.CallBackType = ''Trove''' + @CRLF +
--I'm suggest you pass NULL for your parameters, not '' when you don't need the, I assume they are NULL below
NULLIF(N'WHERE ' + CONCAT_WS(@CRLF + N' AND ',CASE WHEN @FirstName IS NOT NULL THEN N'C.FirstName LIKE ''%''+@FirstName+''%''' END,
CASE WHEN @LastName IS NOT NULL THEN N'C.LastName LIKE ''%''+@LastName+''%''' END,
CASE WHEN @Phone IS NOT NULL THEN N'C.Phone = @Phone' END,
CASE WHEN @Email IS NOT NULL THEN N'C.Email like ''%''+@Email+''%''' END,
CASE WHEN @Specialty IS NOT NULL THEN N'Js.specialtyId = @Specialty' END, --Don't CAST/CONVERT your columns. I guess the data type here later.
CASE WHEN @AdditionalLicensedStates IS NOT NULL THEN N'J.Licensed LIKE ''%''+@AdditionalLicensedStates+''%''' END,
CASE WHEN @DesiredLocation IS NOT NULL THEN N'J.DesiredStates LIKE ''%''+@DesiredLocation+''%''' END,
CASE WHEN @CompactState IS NOT NULL THEN N't.CompactState LIKE ''%''+@CompactState+''%''' END),N'WHERE ') + N') as t';
PRINT @query;-- YOur best friend
EXEC sys.sp_executesql @query,
N'@FirstName nvarchar(50),@LastName nvarchar(50),@Phone varchar(15),@Email varchar(100),@Specialty int = 5,@AdditionalLicensedStates varchar(10),@DesiredLocation varchar(50),@CompactState varchar(5)', --Remember, these are all guessed
@FirstName,
@LastName,
@Phone,
@Email,
@Specialty,
@AdditionalLicensedStates,
@DesiredLocation,
@CompactState;
答案2
得分: 0
Thom A提出的所有观点都是有效而重要的。我要补充的是，在字符串文字中使用SQL是一种难以维护的畸变，无论您是在C#中使用还是在SQL中使用。
您这样做是为了根据实际提供的参数在运行时构建WHERE子句。这没有必要。只需使用...
@FirstName为null或C.FirstName LIKE @Firstname + '%'
如果未提供参数，所有行都将匹配，将返回所有内容。通常情况下，您会希望使用NOCACHE，因为每种参数的排列都需要不同的查询计划。
绝对避免使用字符串文字的最佳方法是QueryFirst。您的SQL存储在应用程序中的.sql文件中，在键入时进行语法验证。所有ADO工作都为您生成，包括真实参数和创建SQL注入漏洞几乎不可能（也没有意义，因为正确的方法更容易）。
接下来，我已经盯着这个问题看了一段时间，但我无论如何都看不出为什么需要内部查询。WHERE子句似乎在内部查询上，谢天谢地，但我看不出为什么需要两个级别。SQL在您可以在“一击”中指定您想要的内容，然后让优化器进行工作时效果最佳。
如果查询更容易编写，您可以编写更多、更小、更专注的查询。这个查询并不太可怕，但它开始变得复杂：7个表，8个参数，20多个列。如果您关注索引，5000行应该在不到10秒的时间内完成，根据您的硬件情况可能有所不同。但是谁有时间查看5000条记录呢？是否应该将其拆分为更少的小查询？
英文:
All the points made by Thom A are valid and important. I'm going to add that SQL in string literals is an unmaintainable aberration, whether you do it in C#, or in SQL as here.
You're doing it so you can construct a where clause at runtime based on the parameters actually supplied. There is no need for this. Just use...
@FirstName is null or C.FirstName LIKE @Firstname + '%'
If the parameter is not supplied, all rows will match, everything will be returned. You will usually want to use this with NOCACHE, because each permutation of parameters requires a different query plan.
Absolutely the best way of avoiding string literals is QueryFirst. Your sql lives in .sql files in your app, syntax validated as you type. All the ADO stuff is generated for you, including real parameters, and creating SQL injection vulnerabilities becomes virtually impossible (and pointless, because the right way is easier).
Then, I've been staring at this for some time, and I can't see for the life of me why you need an inner query. The WHERE clause appears to be on the inner query, thank goodness, but there's absolutely no need I can see to have the two levels. SQL works best when you can specify what you want in "one hit", then leave the optimiser do its work.
If queries are easier to write, you can write more, smaller, more focussed queries. This query is not too scary but its starting to get up there: 7 tables, 8 parameters, 20 odd columns. If you pay attention to indexes, 5000 rows should take less than 10 seconds, guessing wildly, depends on your hardware. But who has the time to read through 5000 records? Should this be split into a small number of smaller queries?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论