Tuning Query In Stored Procedure

Question

Post reply

Tuning Query In Stored Procedure

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Grant Fritchey - Thursday, February 7, 2019 4:19 AM

Jeff Moden - Wednesday, February 6, 2019 11:06 PM

One of the things that I absolutely don't understand is why they're doing replaces on both the product name column and the variable for such things like the TM symbol. They really need to cleanup and homogenize their data.

I'm sure this won't shock you, but 100% agreement. Rather than killing yourself trying to desperately find a way to tune a query that just will not tune, fix the code & structures that are the root of the problem. It's like fixing a leaky roof by fiddling with the windows or shoring up a shaky foundation by painting the walls. Focus on the root of the problem.

Heh... certainly no shock there, Grant. I just had to say it out loud. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jkramprakash SSCommitted Points: 1870 More actions · Answer 2

The Product_Name column in the table contains lots of special characters like '®' ,'©',so i have to replace with TM,TS and CP characters.So now i am going to try with Computed columns concept for Product_Name and Custom Fields and i will use these computed columns in my procedure. is this method improve performance?Computed columns and Calculated columns are same?

Jonathan Szeto SSC Enthusiast Points: 120 More actions · Answer 3

jkramprakash - Tuesday, February 12, 2019 9:31 AM

The Product_Name column in the table contains lots of special characters like 'Â®' ,'Â©',so i have to replace with TM,TS and CP characters.So now i am going to try with Computed columns concept for Product_Name and Custom Fields and i will use these computed columns in my procedure. is this method improve performance?Computed columns and Calculated columns are same?

Are you highly confident that the actual character lengths in F_CUSTOM1, F_CUSTOM2, and F_CUSTOM3 are much less than 4000 characters?

If you are, I would also suggest using a LEFT or SUBSTRING function in your computed column definitions to limit the columns to the appropriate lengths. (I do notice that you have defined @PRODUCTNAME as NVARCHAR(200). So why not have the computed column definition as LEFT(REPLACE(F_CUSTOM1,'^',''), 200)?

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

jkramprakash - Tuesday, February 12, 2019 9:31 AM

The Product_Name column in the table contains lots of special characters like 'Â®' ,'Â©',so i have to replace with TM,TS and CP characters.So now i am going to try with Computed columns concept for Product_Name and Custom Fields and i will use these computed columns in my procedure. is this method improve performance?Computed columns and Calculated columns are same?

Why do you "have" to replace special chars in the column in the table? That will cause huge performance overhead. You'd be much better off changing the chars in the search column value. Even, worst case, if you have to do two searches for the value.

<key_columns> = 'SomeNameÂ®' OR <key_column> = 'Some Name|TM'

In fact, given those types of issues here, I'd put the actual names in a separate table. That would translate the product name to a corresponding int (or bigint if necessary) value, then use the int/bigint for the lookup to the main table.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ThatDataGuy SSC Rookie Points: 33 More actions · Answer 5

F_SUBFORMAT is an index on its own. Why not make the last condition "AND EXISTS (SELECT 1 FROM AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)" into an INNER JOIN in the main FROM table source? Depends on how many records that do not have a 'valid' F_SUBFORMAT in the AllSubformats table (or view), that might help filter out a whole bunch of records in the result set right off the bat.
FROM T_PDF TP INNER JOIN AllSubformats ASF ON ASF.Val = TP.F_SUBFORMAT LEFT JOIN V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT

Also, the code is doing the same REPLACE on both sides of the comparison between F_PRODUCT_NAME and @PRODUCTNAME; but it is not doing any REPLACE on F_CUSTOM1, 2, or 3. So why can't the REPLACE be removed and compare F_PRODUCT_NAME with @PRODUCTNAME as-is? UNLESS, F_PRODUCT_NAME contains both formats of the TM symbol and "|TM", TS symbol and "|TS", etc.