Need one Single Optimized Query for this

Question

Below is my current SQL.

Insert into [dbo].[XYZ]
select col1,col2,
(select colValue from parsedData([ParameterizedValue]) where [Name] = 'Some Value') AS col3,
(select colValue from parsedData([ParameterizedValue]) where [Name] = 'Some Value') AS col4,
(select colValue from parsedData([ParameterizedValue]) where [Name] = 'Some Value') AS col5 from [dbo].[ABC]

This statement calls the parsedData function repeatedly. And table ABC has millions of records.

So is there any SQL for holding the result of parsedData so the parsedData is called once only, instead of multiple times.

Hi and welcome to SO. We can't possibly help you optimize this with nothing but a query. At a bare minimum we need to have the table definitions of all the tables involved including indexes. And we need to see that function. My guess is that function is the culprit here. — Sean Lange, Oct 02 '18 at 14:47
But if you always want the same value over and over why not use a cte or stick it into a variable? — Sean Lange, Oct 02 '18 at 14:48
Are the names the same between col3, col4, and col5 (Some Value)? Or does each column use a different name to retrieve a value? And are you actually passing the same field into the function for those columns? If not, please fix the posted code to be accurate of what you are actually doing. — UnhandledExcepSean, Oct 02 '18 at 14:52
Yes, definitively use a CTE (Common Table Expression) for those "parsed data", so they are computed a single time each, instead of once per row, as Sean Lange says. — The Impaler, Oct 02 '18 at 14:53
You need to post that function's definition if you are going to get meaningful help. — UnhandledExcepSean, Oct 02 '18 at 14:57
@UnhandledExcepSean that is a table valued function. But I suspect it is a multi-statement table valued function which is usually even worse for performance than scalar functions. It could probably be converted to an inline table valued function which will likely scream from a performance perspective. — Sean Lange, Oct 02 '18 at 14:59
@SeanLange Ah, you are right. I missed it was selecting from. — UnhandledExcepSean, Oct 02 '18 at 15:00

score 0 · Answer 1 · answered Oct 02 '18 at 14:57

0

You can do a CROSS APPLY (or OUTER APPLY).

A CROSS APPLY is an alternative for a correlated sub-query and can also be used to 'join on a function'. I do not have a nice example handy, but here are a few hopefully useful links:

answered Oct 02 '18 at 14:57

incomudro

548
4
12

Almost guaranteed to not be the most optimized. It might be better, but there is no way to know from the question currently what an optimized solution would be. – UnhandledExcepSean Oct 02 '18 at 14:58
I do think that having a look at 'parsedData' might be useful,.. to which to code is missing. – incomudro Oct 02 '18 at 15:02

score 0 · Answer 2 · answered Oct 02 '18 at 15:09

You can do:

Insert into [dbo].[XYZ]
    select abc.col1, abc.col2, p.col3, p.col4, p.col5
    from [dbo].ABC abc outer apply
         (select max(case when name = 'value1' then colvalue end) as col3,
                 max(case when name = 'value2' then colvalue end) as col4,
                 max(case when name = 'value3' then colvalue end) as col5             
          from parsedData(abc.ParameterizedValue) p
         ) p;

This should save you the multiple parsings of the same data.

Need one Single Optimized Query for this

2 Answers2