SQL batch query processing (SQL query input array)

问题:

I have SQL query like

SELECT *, dbo.func(@param1, a.point) as fValue 
FROM dbo.table AS a 
WHERE dbo.func(@param1, a.point) < @param2

When this query is executed only once, everything is fine, but when I have array of input @param1 values let's say, over 100 values, executing and fetching results for every value take s a lot of time.

Is it possible to pass array of @param1 into the query somehow, and receive dataset for all the input values, instead of executing it for each value?

function func() doing some math on 2 values. @param1 and a.point are type of double. and, yeah, a.point - is not an ID, and it is not a unique value.

I know, it should be really easy, but it looks like I'm missing something.

回答1:

What exactly does dbo.func() do? Is it possible that you could insert the 100 values into a table structure, and perform that operation on the set of 100 all at once, instead of 1x1 100 times?

As an example, let's say you have this function, which just turns a comma-separated list of float values into a single-column table:

CREATE FUNCTION dbo.ListFloats
(
    @List VARCHAR(MAX)
)
RETURNS TABLE
RETURN
(
    SELECT i = CONVERT(FLOAT, Item)
    FROM
    (
        SELECT Item = x.i.value('(./text())[1]', 'FLOAT')
        FROM
        (
            SELECT [XML] = CONVERT(XML, '<i>'
                + REPLACE(@List, ',', '</i><i>')
                + '</i>').query('.')
        ) AS a
        CROSS APPLY
        [XML].nodes('i') AS x(i)
    ) AS y
    WHERE Item IS NOT NULL
);
GO

Now you should be able to get your floats in a set by simply saying:

SELECT i FROM dbo.ListFloats('1.5, 3.0, 2.45, 1.9');

Taking that a step further, let's say dbo.func() takes these two inputs and says something like:

RETURN (SELECT (@param1 + @param2 / @param2));

Now, I know that you've always been told that modularization and encapsulation are good, but in the case of inline functions, I would suggest you avoid the function that gets this result (again you haven't explained what dbo.func() does, so I'm just guessing this will be easy) and do it inline. So instead of calling dbo.func() - twice for each row, no less - you can just say:

DECLARE 
    @Param1Array VARCHAR(MAX) = '1.5, 3.0, 2.45, 1.9',
    @Param2 FLOAT = 2.0;

WITH x AS 
(
    SELECT t.point, x.i, fValue = ((x.i + t.point)/t.point) 
    FROM dbo.[table] AS t
    CROSS JOIN dbo.ListFloats(@Param1Array) AS x
)
SELECT point, i, fValue FROM x
--WHERE fValue < @Param2
;

The keys are:

1. Avoiding processing each parameter individually.

2. Avoiding the individual calculations off in its own separate module.

3. Performing calculations as few times as possible.

If you can't change the structure this much, then at the very least, avoid calculating the function twice by writing instead:

;WITH x AS 
(
    SELECT *, dbo.func(@param1, a.point) as fValue 
    FROM dbo.table AS a 
)
SELECT * FROM x 
    WHERE fValue < @param2;

If you provide details about the data types, what dbo.func() does, etc., people will be able to provide more tangible advice.

回答2:

You still need to execute that function 100 times for each row, right? I don't see any shortcuts here.

If you wanted to get them all at once, you could do

SELECT dbo.func(@param1, a.point) as fValue1, 
       dbo.func(@param2, a.point) as fValue2 ...

or something like that, but looping through them just seems more efficient to me anyway.

I suppose you could use a cursor to retrieve each a.point value once, then act on it 100 times, but that's a lot of coding, and not necessarily a simpler solution.

回答3:

Do you have any indexes on this table? If you have an index on a.point, then you will never hit it using this code, ie will always table scan. This is to do with Search Arguments (you can google this). Example:

If you have table xTable with index on column xColumn, then this:

select colA, colB from xTable where xColumn/2 >= 5

will never use the index, but this probably will:

select colA, colB from xTable where xColumn >=10

So you might need something like this:

WHERE a.point < Otherfunc(@param1,  @param2 )