January 18, 2013 at 8:33 am
Hello,
I try to optimize time processing while running an SP that goes through all rows in a table (150,000) (via a cursor) to send each two columns as parameters of a Table-Valued-Function.
This function does the matching between these two parameters received and a column of a table.
CREATE FUNCTION [dbo].[findNewPerson]
(
@nom AS varchar(4000),
@prenom AS varchar(4000)
)
returns TABLE
AS
RETURN
SELECT WCS_NomComplet,
WCS_StringAliases,
WCS_Uid,
WCS_ActualDateMaj
FROM db_ref..LAB_WCS_WorldCheckStrings
WHERE LEN(@nom) > 0
AND ( WCS_StringAliases LIKE '% ' + @nom + ' %' + @prenom
+ '[^a-zA-Z0-9]%'
OR WCS_StringAliases LIKE @nom + ' %' + @prenom
+ '[^a-zA-Z0-9]%'
)
Do you have any ideas to optimize the execution time of the SP especially since I have to run every day.
Actually to compare 7000 lines it tooks 14 minutes . I have to compare 150.000 lines
Thank you for your help !
January 18, 2013 at 10:24 am
Use cross apply instead of cursor. The cursor approach will take forever, the cross apply approach will be much faster.
http://www.sqlservercentral.com/articles/APPLY/69953/%5B/url%5D
If you need specific coding help you will need to provide enough details for somebody to be able to help. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply