Wednesday, August 5, 2015

Providing a parameterized list to an ad-hoc SQL query

MS SQL Server, like all SQL RDBMSes, is optimized to deal with sets of data and performs poorly when feed input one value at a time. Often you are in a situation where you need to provide a parameterized query with a list of values as input. Perversely, no matter what language you're using, most APIs only let you parameterize on atomic values. You're left with a choice between using dynamic SQL, which is a security risk, or fetching records from the database one at a time, which has a massive performance penalty.

Stored procedures allow table parameters, but you have to define the schema of the table in a separate database object, and only a few connectivity APIs support calling such stored procedures, anyway. Another method is writing a user defined function to "split" on commas, which will either have poor performance if you write it in T-SQL, or you can go the CLR route and deal with all of its associated issues.

One alternative I've found is providing the criteria in an XML document. SQL Server has very good XML support that is often overlooked, but is useful in many situations like this.

DECLARE @xml xml = N'
<root>
    <row field1="abcd" field2="261" />
    <row field1="dfke" field2="378" />
    <row field1="cirp" field2="381" />
    <row field1="vkdp" field2="381" />
    <row field1="boer" field2="381" />
    <row field1="npmr" field2="2638" />
    <row field1="qpci" field2="2638" />
    <row field1="biep" field2="2638" />
</root>
';

SELECT q.field1, q.field2, table1.field3
FROM
    (SELECT DISTINCT
        n.value('@field1', 'varchar(12)') AS field1,
        n.value('@field2', 'int') AS field2
    FROM @xml.nodes('//row') x(n)) q
LEFT JOIN table1
ON q.field1 = table1.field1 AND
    q.field2 = table1.field2;

Since the XML document is provided to the query in a string, it's parameterizable. It's completely flexible in the kind of data that you can provide; the schema is defined and validated when you shred the XML using T-SQL's XML methods.

XML is often associated with poor performance, but these concerns are overblown unless you're dealing with documents that are tens of thousands of lines or longer, not when you're sending a few hundred lines of input into a parameterized query. But if you are providing a lot of data, you might want to shred the XML into a temporary table so that you can use indexes and a primary key.

No comments:

Post a Comment