How to optimize replace and find function?

Question

5.00/5 (1 vote)

See more:

I am trying to create function which can replace certain words with hyperlink in sql. When I call the function as query in sql, its takes a really long time to execute the query, more than 2-3min. I assumed this is because, the tag_libary table has around 600,000 records and iterating through large number, would consume a lot of processing time.

SQL

CREATE FUNCTION dbo.ReplaceTags(@body VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN

SELECT @body = REPLACE(@body,name,'<a href="/KB/answers/pagename.aspx"?tag='+name+'">'+name+'</a>')
FROM Tag_Library

RETURN @body
END

Hence, I am writing to seek some advice, if there is a way to make this sql function optimal or would it be better to change this function, into a insert trigger?

Please advice, if possible.

Posted 5-Nov-14 23:41pm

miss786

Add a Solution

Comments

Tomas Takac 6-Nov-14 6:03am

I don't see how the insert trigger would help here. Unless you are ok with the fact your inserts will take 2-3min to complete.

miss786 6-Nov-14 6:12am

Thank you for your reply. I am slightly struggling to figure out a way, if there is way to execute the query under 1 min time, if possible. Please advice further.

Tomas Takac 6-Nov-14 8:12am

600k replacements is a lot, that's the problem here I guess. You could catgorize the tags and inputs somehow so you don't have to do all the replacements each time, just a subset based on the category.

Tomas Takac 6-Nov-14 9:50am

Do you need to do it on database level? Maybe application layer would be more suitable. BTW how do the tags look like? Can you post a sample of @body and Tag_Library?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

SITU.PATI · Answer 1 · 2014-11-06T03:04:00

Solution 1

Since you have a lot of records so it may take time. Why dont you use a trigger instead of a function. It may help you

Posted 6-Nov-14 3:04am

SITU.PATI

Comments

Tomas Takac 6-Nov-14 9:49am

What about the insert then? That will then take 2-3mins. You are not solving the problem, just moving it around.

miss786 6-Nov-14 10:39am

Thank you you all for your suggestion. Will the following procedure work, if I partition the tag_library data into smaller subset called "small_libraryTag". Then create a update trigger, to update the article field body with hyperlinks (i.e. call the above function). Is this new procedure plausible, to execute quickly.

Many thanks for your help and advice/

SITU.PATI 11-Nov-14 10:59am

Do you have only 600k data to change or each time you give insert or update the data you have to change?
If you sub part it, then also it take the same time. Since there are a lot of record so it may take some time

miss786 12-Nov-14 7:23am

Hi, thank you for reply. would it be possible to update the function, so it only looks for tags name after date September 2014?
Thank you for your advice.

SITU.PATI 19-Nov-14 10:35am

Sure, Just put a select query while updating. It will do the thing