Can i optimize this process?

Question

0.00/5 (No votes)

See more:

Hello All,

I want to optimize the process of SQL sub string replacement in big email HTML. The process is used to build customer email html building, which is taking 2 days for 100 thousands records. There is basic email HTML with various placeholders and variables like 3 images, 4 different types of texts and links. These replacement done at SQL side in stored procedures. Could you please suggest anything to optimize this process time.
Thank you in advance for your help.

Regards,
-Amey

UPDATE: from comments
Hey Andy Thanks for replying.. here is, below, one example SQL which I am using for my HTML replacing process.

SQL

UPDATE EL
SET LayoutHTMLActual=
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EL.LayoutHTMLActual,'{TextID1}',ISNULL(EL.BodyText,'')),'{TextID2}',ISNULL(EL.BodyText2,'')),'{TextID3}',ISNULL(EL.BodyText3,'')),'{TextID4}',ISNULL(EL.BodyText4,'')),'{TextID5}',ISNULL(EL.BodyText5,'')),'{TextID6}',ISNULL(EL.BodyText6,'')),'{TextID7}',ISNULL(EL.BodyText7,'')),'{TextID8}',ISNULL(EL.BodyText8,'')),'{TextID9}',ISNULL(EL.BodyText9,'')),'{TextID10}',ISNULL(EL.BodyText10,'')),'{TextID11}',ISNULL(EL.BodyText11,'')),'{TextID12}',ISNULL(EL.BodyText12,'')),'{TextID13}',ISNULL(EL.BodyText13,'')),'{TextID14}',ISNULL(EL.BodyText14,'')) FROM LayoutPreview EL WITH(NOLOCK)
WHERE FileID=@FileID and (LayoutName like '%STD%' OR EL.LayoutName LIKE '%LAP%')
AND IsValidated = 0

There are no of such queries have been used and these are for 100 thousands of records. i.e If the flag for the record "IsValidated=0" then such records would be considered.

Thanks in advance for your help.

What I have tried:

I have tried just the replace function from SQL to replace these place holders and variables. I updates the main HTML column for every place holder and variable.

Posted 14-Feb-16 20:43pm

Amey Shahane

Updated 14-Feb-16 22:55pm

Andy Lanng

v2

Add a Solution

Comments

Andy Lanng 15-Feb-16 4:35am

Post the sql so we can point out specific improvements

Amey Shahane 15-Feb-16 4:52am

Hey Andy Thanks for replying.. here is, below, one example SQL which I am using for my HTML replacing process.

UPDATE EL
SET LayoutHTMLActual=
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EL.LayoutHTMLActual,'{TextID1}',ISNULL(EL.BodyText,'')),'{TextID2}',ISNULL(EL.BodyText2,'')),'{TextID3}',ISNULL(EL.BodyText3,'')),'{TextID4}',ISNULL(EL.BodyText4,'')),'{TextID5}',ISNULL(EL.BodyText5,'')),'{TextID6}',ISNULL(EL.BodyText6,'')),'{TextID7}',ISNULL(EL.BodyText7,'')),'{TextID8}',ISNULL(EL.BodyText8,'')),'{TextID9}',ISNULL(EL.BodyText9,'')),'{TextID10}',ISNULL(EL.BodyText10,'')),'{TextID11}',ISNULL(EL.BodyText11,'')),'{TextID12}',ISNULL(EL.BodyText12,'')),'{TextID13}',ISNULL(EL.BodyText13,'')),'{TextID14}',ISNULL(EL.BodyText14,'')) FROM LayoutPreview EL WITH(NOLOCK)
WHERE FileID=@FileID and (LayoutName like '%STD%' OR EL.LayoutName LIKE '%LAP%')
AND IsValidated = 0

There are no of such queries have been used and these are for 100 thousands of records. i.e If the flag for the record "IsValidated=0" then such records would be considered.

Thanks in advance for your help.

Andy Lanng 15-Feb-16 5:02am

Hmm - interesting. Is the LayoutHTMLActual the same for all rows before the replace?

Amey Shahane 15-Feb-16 5:03am

Yes, it is commonly updated for all the records. Then for every record we replace the variable e.g. "{Text}" as per the record.

Andy Lanng 15-Feb-16 5:06am

Ah ha - there's your quick fix:
The way you are doing this is that the query has to check each row's text for the placeholders. It does this 100k times, which is VERY costly.

If you had a single string which was your LayoutHTMLActual template then it would only need to find the placeholders once. It would still need to perform the replace function for each row but you'd be cutting down the workload by over 90%!

I'll add a quick solution for you to try

Amey Shahane 15-Feb-16 5:33am

Thanks Andy I have got your solution.
But, as I have mentioned in reply to Tomas, there are distinct and various conditions on which replace the variables. Currently we are optimizing with your given solution only. By doing "With CTE" of distinct conditions and by taking top 1 LayoutHTMLActual for all such records and replacement of variables over it. It will save some time.

Thanks

Andy Lanng 15-Feb-16 5:51am

Yup - that'll work :)

Tomas Takac 15-Feb-16 4:47am

If you are updating the table after each replacement then don't do this. This way you do rows x replacements updates. Do the replacements in a variable and only then update so you only have one update per one row.

Amey Shahane 15-Feb-16 5:02am

You are correct Tomas, but there are specific criteria/conditions to update the HTML placeholder or variable. Suppose condition to show one of three images dynamically, then 1st will be replaced with correct HTML image tag and other two should show blank in that place of placeholder.

We can implement the logic, if there are 100 thousands of records then we would get 5000 distinct records, so we can build these 5000 records one time then these records would be distributed to 100 thousands of records. This would save 5 to 7 hours from 36 hours, but still the resulted process will be 30 hours and it is too much time consuming.

Thanks for your great suggestion, that is what we are currently doing. But still we will need to optimize.

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)

Andy Lanng · Accepted Answer · 2016-02-14T23:16:00

If you have need to customize a template per record, then you would have to use the approach you use (there are other ways to optimize, but it would just be a work-around for the 1 template solution).

If you use the same template then you should only have to search for the placeholder text one in one string, rather than doing it for every row.

Here I have worked around your data structure. What you should do it create a templates table that you would join, or select from.

As I mentioned above, if some of the templates needed to be customized per (record?) then you could create an template override table. That would slow the whole thing down but if you only have 1% custom then it would slow things down by 0.9% ^_^

SQL

DECLARE @htmlTemplate NVARCHAR(MAX)

set @htmlTemplate = (select top 1 LayoutHTMLActual from EL)

UPDATE EL
SET LayoutHTMLActual=
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@htmlTemplate,'{TextID1}',ISNULL(EL.BodyText,'')),'{TextID2}',ISNULL(EL.BodyText2,'')),'{TextID3}',ISNULL(EL.BodyText3,'')),'{TextID4}',ISNULL(EL.BodyText4,'')),'{TextID5}',ISNULL(EL.BodyText5,'')),'{TextID6}',ISNULL(EL.BodyText6,'')),'{TextID7}',ISNULL(EL.BodyText7,'')),'{TextID8}',ISNULL(EL.BodyText8,'')),'{TextID9}',ISNULL(EL.BodyText9,'')),'{TextID10}',ISNULL(EL.BodyText10,'')),'{TextID11}',ISNULL(EL.BodyText11,'')),'{TextID12}',ISNULL(EL.BodyText12,'')),'{TextID13}',ISNULL(EL.BodyText13,'')),'{TextID14}',ISNULL(EL.BodyText14,'')) FROM LayoutPreview EL WITH(NOLOCK)
WHERE FileID=@FileID and (LayoutName like '%STD%' OR EL.LayoutName LIKE '%LAP%')
AND IsValidated = 0