Performance tuning on String search using PATINDEX.
I have the following query to search the string using the PATINDEX.
Query:
SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............
FROM OPENQUERY([LinkDB],'SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............ FROM [LinkDB].dbo.[Employee]')
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1'
Data Details:
DATABASE : LinkDB
Columns : 60
Data : 10 Millions
INDEX : [Employee Number],[Employee First Name],[Employee Last Name]
Statistics:
Query : Above query
Rows : 90,505
Time taken : 00:02:45
sql-server tsql sql-server-2008-r2 edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
add a comment |
I have the following query to search the string using the PATINDEX.
Query:
SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............
FROM OPENQUERY([LinkDB],'SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............ FROM [LinkDB].dbo.[Employee]')
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1'
Data Details:
DATABASE : LinkDB
Columns : 60
Data : 10 Millions
INDEX : [Employee Number],[Employee First Name],[Employee Last Name]
Statistics:
Query : Above query
Rows : 90,505
Time taken : 00:02:45
sql-server tsql sql-server-2008-r2 edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
add a comment |
I have the following query to search the string using the PATINDEX.
Query:
SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............
FROM OPENQUERY([LinkDB],'SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............ FROM [LinkDB].dbo.[Employee]')
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1'
Data Details:
DATABASE : LinkDB
Columns : 60
Data : 10 Millions
INDEX : [Employee Number],[Employee First Name],[Employee Last Name]
Statistics:
Query : Above query
Rows : 90,505
Time taken : 00:02:45
sql-server tsql sql-server-2008-r2 edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
I have the following query to search the string using the PATINDEX.
Query:
SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............
FROM OPENQUERY([LinkDB],'SELECT [Employee First Name],[Employee Last Name],[Employee Middle Name],[Gender]............ FROM [LinkDB].dbo.[Employee]')
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1'
Data Details:
DATABASE : LinkDB
Columns : 60
Data : 10 Millions
INDEX : [Employee Number],[Employee First Name],[Employee Last Name]
Statistics:
Query : Above query
Rows : 90,505
Time taken : 00:02:45
sql-server tsql sql-server-2008-r2 sql-server tsql sql-server-2008-r2 edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
edited Nov 22 '18 at 7:13
gotqn
19.9k32113189
19.9k32113189
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
asked Nov 22 '18 at 5:39
MAKMAK
2,01653375
2,01653375
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can try with adding the WHERE clause to the query. Note, I have change the code a little bit to make it work with my environment:
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers]')
WHERE ISNULL(PATINDEX('%emil%',[FirstName]),'0') + ISNULL(PATINDEX('%emil%',[LastName]),'0') >= '1'
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers] WHERE ISNULL(PATINDEX(''%emil%'',[FirstName]),''0'') + ISNULL(PATINDEX(''%emil%'',[LastName]),''0'') >= ''1''')
You can see, in your case we have remote scan, then filter. In the second, the filter pass is missing:
Also, if you can add an indexes, you can add index on first and last name and try first selecting only the rows that need to be returned:
CREATE TABLE #EmployeesFiltered
(
[EmployeeID] INT
);
INSERT INTO #EmployeesFiltered ([EmployeeID])
SELECT [EmployeeID]
FROM [LinkDB].dbo.[Employee]
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1';
SELECT *
FROM [LinkDB].dbo.[Employee] A
INNER JOIN #EmployeesFiltered B
ON A.[EmployeeID] = B.[[EmployeeID];
Even if you are not allowed to rely the indexes ordering, you will work with smaller amount of data, when the indexes is read (you need only to read all users IDs, first and last names. Then having the IDs, just filter the clustered index.
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424502%2fperformance-tuning-on-string-search-using-patindex%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can try with adding the WHERE clause to the query. Note, I have change the code a little bit to make it work with my environment:
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers]')
WHERE ISNULL(PATINDEX('%emil%',[FirstName]),'0') + ISNULL(PATINDEX('%emil%',[LastName]),'0') >= '1'
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers] WHERE ISNULL(PATINDEX(''%emil%'',[FirstName]),''0'') + ISNULL(PATINDEX(''%emil%'',[LastName]),''0'') >= ''1''')
You can see, in your case we have remote scan, then filter. In the second, the filter pass is missing:
Also, if you can add an indexes, you can add index on first and last name and try first selecting only the rows that need to be returned:
CREATE TABLE #EmployeesFiltered
(
[EmployeeID] INT
);
INSERT INTO #EmployeesFiltered ([EmployeeID])
SELECT [EmployeeID]
FROM [LinkDB].dbo.[Employee]
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1';
SELECT *
FROM [LinkDB].dbo.[Employee] A
INNER JOIN #EmployeesFiltered B
ON A.[EmployeeID] = B.[[EmployeeID];
Even if you are not allowed to rely the indexes ordering, you will work with smaller amount of data, when the indexes is read (you need only to read all users IDs, first and last names. Then having the IDs, just filter the clustered index.
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
add a comment |
You can try with adding the WHERE clause to the query. Note, I have change the code a little bit to make it work with my environment:
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers]')
WHERE ISNULL(PATINDEX('%emil%',[FirstName]),'0') + ISNULL(PATINDEX('%emil%',[LastName]),'0') >= '1'
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers] WHERE ISNULL(PATINDEX(''%emil%'',[FirstName]),''0'') + ISNULL(PATINDEX(''%emil%'',[LastName]),''0'') >= ''1''')
You can see, in your case we have remote scan, then filter. In the second, the filter pass is missing:
Also, if you can add an indexes, you can add index on first and last name and try first selecting only the rows that need to be returned:
CREATE TABLE #EmployeesFiltered
(
[EmployeeID] INT
);
INSERT INTO #EmployeesFiltered ([EmployeeID])
SELECT [EmployeeID]
FROM [LinkDB].dbo.[Employee]
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1';
SELECT *
FROM [LinkDB].dbo.[Employee] A
INNER JOIN #EmployeesFiltered B
ON A.[EmployeeID] = B.[[EmployeeID];
Even if you are not allowed to rely the indexes ordering, you will work with smaller amount of data, when the indexes is read (you need only to read all users IDs, first and last names. Then having the IDs, just filter the clustered index.
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
add a comment |
You can try with adding the WHERE clause to the query. Note, I have change the code a little bit to make it work with my environment:
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers]')
WHERE ISNULL(PATINDEX('%emil%',[FirstName]),'0') + ISNULL(PATINDEX('%emil%',[LastName]),'0') >= '1'
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers] WHERE ISNULL(PATINDEX(''%emil%'',[FirstName]),''0'') + ISNULL(PATINDEX(''%emil%'',[LastName]),''0'') >= ''1''')
You can see, in your case we have remote scan, then filter. In the second, the filter pass is missing:
Also, if you can add an indexes, you can add index on first and last name and try first selecting only the rows that need to be returned:
CREATE TABLE #EmployeesFiltered
(
[EmployeeID] INT
);
INSERT INTO #EmployeesFiltered ([EmployeeID])
SELECT [EmployeeID]
FROM [LinkDB].dbo.[Employee]
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1';
SELECT *
FROM [LinkDB].dbo.[Employee] A
INNER JOIN #EmployeesFiltered B
ON A.[EmployeeID] = B.[[EmployeeID];
Even if you are not allowed to rely the indexes ordering, you will work with smaller amount of data, when the indexes is read (you need only to read all users IDs, first and last names. Then having the IDs, just filter the clustered index.
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
You can try with adding the WHERE clause to the query. Note, I have change the code a little bit to make it work with my environment:
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers]')
WHERE ISNULL(PATINDEX('%emil%',[FirstName]),'0') + ISNULL(PATINDEX('%emil%',[LastName]),'0') >= '1'
SELECT *
FROM OPENQUERY([RMVNSQL01INST1],'SELECT [login], [FirstName], [LastName] FROM [smModel_20180807_UpdateTests_CORE].dbo.[SecurityUsers] WHERE ISNULL(PATINDEX(''%emil%'',[FirstName]),''0'') + ISNULL(PATINDEX(''%emil%'',[LastName]),''0'') >= ''1''')
You can see, in your case we have remote scan, then filter. In the second, the filter pass is missing:
Also, if you can add an indexes, you can add index on first and last name and try first selecting only the rows that need to be returned:
CREATE TABLE #EmployeesFiltered
(
[EmployeeID] INT
);
INSERT INTO #EmployeesFiltered ([EmployeeID])
SELECT [EmployeeID]
FROM [LinkDB].dbo.[Employee]
WHERE ISNULL(PATINDEX('%Jack%',[Employee First Name]),'0') + ISNULL(PATINDEX('%Jack%',[Employee Last Name]),'0') >= '1';
SELECT *
FROM [LinkDB].dbo.[Employee] A
INNER JOIN #EmployeesFiltered B
ON A.[EmployeeID] = B.[[EmployeeID];
Even if you are not allowed to rely the indexes ordering, you will work with smaller amount of data, when the indexes is read (you need only to read all users IDs, first and last names. Then having the IDs, just filter the clustered index.
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
edited Nov 22 '18 at 6:34
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
answered Nov 22 '18 at 6:28
gotqngotqn
19.9k32113189
19.9k32113189
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424502%2fperformance-tuning-on-string-search-using-patindex%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
