I have a SQL Server 2005 stored procedure that will receive a series
of valeus to be passed to an IN clause. Since IN clauses do not take
parameters, I need to use a UDF like fnc_Parse2TableString() to parse
the comma-delimited values into a table object as shown below:

DECLARE @DeptNames varchar(max)

SET @DeptNames = 'Accounting, Finance'

SELECT *
FROM Personnel
WHERE Department IN
	CASE
	  WHEN @DeptNames <> '' THEN (SELECT data FROM
dbo.fnc_Parse2TableString(@DeptNames, ','))
	END


What I'm trying to accomplish is to have @DeptNames as an optional
parameter. Since this SQL does not work as shown, what must I do in
order to accomplish this?

I really want to avoid dynamic SQL and since I may have many such
optional IN clause parameters, managing multiple versions of the same
SQL would become cumbersome.

Thanks

Carl

>> I have a SQL Server 2005 stored procedure that will receive a series of valeus to be passed to an IN clause. Since IN clauses do not take parameters, ..<<

Wrong, you just need to learn how to do it. It means thinking in sets
and not in procedural code

>> I need to use a UDF like fnc_Parse2TableString() to parse the comma-delimited values into a table object as shown below: <<

Yep, just like you were in a 1960's C programming class and having to
do low-level I/O. And you are using proprietary "features" that will
foil the optimizer, too.

SQL Server can handle up to 2K parameters, which is actually a short
list compared to DB2's 32K. Since they are parameters, the compiler
handles ALL the error checking (I'll bet your parser doesn't and I
will bet that you will not update your code to make your parser work
like the next release of T-SQL), can do optimizer tricks with them,
detect missing parameters, etc.

Here is a skeleton for 5 values. Yo use the table constructor to
create a derived table where you need it.

CREATE PROCEDURE Foobar
(@p1 INTEGER = NULL,
 @p2 INTEGER = NULL,
 @p3 INTEGER = NULL,
 @p4 INTEGER = NULL,
 @p5 INTEGER = NULL,
 etc.)

BEGIN
...
SELECT ..
  FROM ..
 WHERE x
        IN (SELECT parm
              FROM (VALUES (@p1), (@p2), (@p3), @p4), (@p5)) AS
X(parm)
             WHERE parm IS NOT NULL;
...
END;

You can generalize this, do calculations and add CASE expression to
the code. YOu can cast the first line to desired data types and force
the columns. After that use cut & paste.

 VALUES (CAST (sku_1 AS CHAR(9)), CAST (price_1 AS DECIMAL(8,2), CAST
(order_qty_1 AS INTEGER),
         (sku_2, price_2, order_qty_2),
          etc. ) AS X(..)

All declarative code, no need for procedural thinking at all.

You can simply use IF statement to execute two queries with different
WHERE predicate:

IF @DeptNames <> ''
   <query with IN>;
ELSE
  <query without IN>;

Also, see Erland's article on different technique for splitting
arrays:
http://www.sommarskog.se/arrays-in-sql-2005.html

--
Plamen Ratchev
http://www.SQLStudio.com

We used this suggested approach for a "Google Friend Connect" custom feature 
against a MySQL backend.

We needed to SELECT data for the current user, as well as the current user's 
Friends (up to 100).  MySQL doesn't support the row constructor, so we had to 
UNION ALL-up the input params in the derived table, but the SP call from the 
web app was a cinch and the SP was lightning fast.

"--CELKO--" wrote:

> >> I have a SQL Server 2005 stored procedure that will receive a series of valeus to be passed to an IN clause. Since IN clauses do not take parameters, ..<<
> 
> Wrong, you just need to learn how to do it. It means thinking in sets
> and not in procedural code
> 
> >> I need to use a UDF like fnc_Parse2TableString() to parse the comma-delimited values into a table object as shown below: <<
> 
> Yep, just like you were in a 1960's C programming class and having to
> do low-level I/O. And you are using proprietary "features" that will
> foil the optimizer, too.
> 
> SQL Server can handle up to 2K parameters, which is actually a short
> list compared to DB2's 32K. Since they are parameters, the compiler
> handles ALL the error checking (I'll bet your parser doesn't and I
> will bet that you will not update your code to make your parser work
> like the next release of T-SQL), can do optimizer tricks with them,
> detect missing parameters, etc.
> 
> Here is a skeleton for 5 values. Yo use the table constructor to
> create a derived table where you need it.
> 
> CREATE PROCEDURE Foobar
> (@p1 INTEGER = NULL,
>  @p2 INTEGER = NULL,
>  @p3 INTEGER = NULL,
>  @p4 INTEGER = NULL,
>  @p5 INTEGER = NULL,
>  etc.)
> 
> BEGIN
> ...
> SELECT ..
>   FROM ..
>  WHERE x
>         IN (SELECT parm
>               FROM (VALUES (@p1), (@p2), (@p3), @p4), (@p5)) AS
> X(parm)
>              WHERE parm IS NOT NULL;
> ...
> END;
> 
> You can generalize this, do calculations and add CASE expression to
> the code. YOu can cast the first line to desired data types and force
> the columns. After that use cut & paste.
> 
>  VALUES (CAST (sku_1 AS CHAR(9)), CAST (price_1 AS DECIMAL(8,2), CAST
> (order_qty_1 AS INTEGER),
>          (sku_2, price_2, order_qty_2),
>           etc. ) AS X(..)
> 
> All declarative code, no need for procedural thinking at all.
> .
> 

This is an old DB2 trick; I had not seen it done with MySQL. Since the
data is getting pushed on a parameter stack, it should fast in most
SQLs. SQL Server can take 2k parameters, but DB2 takes 32k and Oracle
takes 64K.

I have an article on it I have to send off this week; I will let you
know when it comes out.

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:93b0c487-b480-4f94-9a59-826e78137e29@q22g2000yqm.googlegroups.com...
> This is an old DB2 trick; I had not seen it done with MySQL. Since the
> data is getting pushed on a parameter stack, it should fast in most
> SQLs. SQL Server can take 2k parameters, but DB2 takes 32k and Oracle
> takes 64K.
>
> I have an article on it I have to send off this week; I will let you
> know when it comes out.

It used to surprise me why you push best practice from other products onto 
SQL Server when in fact in this case and other cases its the worst thing you 
can do.

Many SQL Server experts have had this discussion with you about having many 
hundreds of parameters on a stored procedure instead of using a table 
variable or single parameter Comma Separated Value string.

Erland has done the research and has the comparisons between the different 
methods.

The hundreds of parameters suffer from a number of things...

1.    It breaks 1NF because the parameters are repeating groups and the 
domain being modelled is the same. Correct, that is the case with CSV so you 
must use by definition a TABLE valued parameter in order to fullfil 
relational theory requirements.

2.    It's un-maintainable - the majority of developers working against SQL 
Server use Management Studio, Management Studio has no macro language, in 
order to handle the hundreds of parameters some automated code generator 
needs to be used in order to save from typo's and errors creeping in, that 
would require the developer to learn and buy yet another language.

3.    Have you ever tried to read and debug a stored procedure with a few 
hundred parameters - one such example is the sudoko proc which whilst doing 
what it says on the tin is un-maintainable.

4.    Domain protection is NOT done, for instance in the sudoko the data 
type does not prevent you from passing a number that is outside the range 
0 - 9, in order to do that you need either a load of IF statements or you 
insert all the values into a table which defeats why you used parameters in 
the first place and should have just used a table valued parameter.

I do look forward to seeing your article and will also critically point out 
its failings if you propose to use the many parameters method.

--ROGGIE--
 

--CELKO-- (jcelko212@earthlink.net) writes:
> This is an old DB2 trick; I had not seen it done with MySQL. Since the
> data is getting pushed on a parameter stack, it should fast in most
> SQLs. SQL Server can take 2k parameters, but DB2 takes 32k and Oracle
> takes 64K.
 
I don't know about the other products, but I when I tested various methods
for searching for a list of values, the idea of sending many parameters 
fared very poorly.

As long as I measured performance server-side only, the test procedure 
behaved very well, and even competed for the top. But when I measured the 
client-side time and thus added the call overhead, the picture was 
completely different. Passing 2000 parameters took 500 ms - then it doesn't 
matter if the procedure itself completes in 20 ms.


You can find the data at 
http://www.sommarskog.se/arrays-in-sql-perftest-2009.html

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>>  It breaks 1NF because the parameters are repeating groups and the domai=
n being modelled is the same. Correct, that is the case with CSV so you mus=
t use by definition a TABLE valued parameter in order to fulfill  relationa=
l theory requirements. <<

Parameter lists are not tables.  I happen to like tabel valued
parameters and think they have some advantage. I would prefer that
they be declared in the parameter list instead of outside of it, but
that is probably coming.

>>  It's UN-maintainable - the majority of developers working against SQL S=
erver use Management Studio, Management Studio has no macro language, in or=
der to handle the hundreds of parameters some automated code generator need=
s to be used in order to save from typo's and errors creeping in, that woul=
d require the developer to learn and buy yet another language. <<

Why not use the macro language in your C compiler? Or your word
processor? I need to move some of my old WORD stuff over to Open
Office soon, which was free.

I find a lot of people dislike Management Studio for that and other
reasons. Pretty printers, macros, source code controls and a lot of
other feaures are missing. They tend toward Toad and DB-Viz instead or
other tools. The argument that Management Studio makes code UN-
maintainable is not a very good one.  No law requires you to use it
exclusively.

>>  Have you ever tried to read and debug a stored procedure with a few hun=
dred parameters - one such example is the Sudoku procedure which whilst doi=
ng what it says on the tin is UN-maintainable. <<

Yes, and I have no problem; I use a regular expression to do any edits
to the set of parameters rather than one parameter at a time. The code
is so simple and obvious most of the time, you never really look at it
again. But if you do, it is to extent the pattern rather than change
processing -- this is just a parameter list.

>>   Domain protection is NOT done, for instance in the Sudoku the data typ=
e does not prevent you from passing a number that is outside the range 0 - =
9, in order to do that you need either a load of IF statements or you inser=
t all the values into a table which defeats why you used parameters in the =
first place and should have just used a table valued parameter. <<

Gee, I used the equivalent of this for my macro. Why use a procedural
IF when I have declarative CHECK()?

FOR %1 IN [0-9] DO
   FOR %2 IN [0-9] DO
       "cell_%1%2 SMALLINT NOT NULL CHECK (cell_%1%2 BETWEEN 0 AND
9)"

Then there were similar ones for the column, row and square checking
rules.

>> As long as I measured performance server-side only, the test procedure b=
ehaved very well, and even competed for the top. But when I measured the cl=
ient-side time and thus added the call overhead, the picture was completely=
 different. Passing 2000 parameters took 500 ms - then it doesn't matter if=
 the procedure itself completes in 20 ms. <<

 I saw that and I cannot figure out what Microsoft is doing in the
client. I expect the database to do very well, since we are using a
calling stack in most SQLs -- load right into main storage, etc.  But
I do not know what the client could be doing.  Let's ask Kalen ...

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:d6279d9a-7cfd-43d8-b3b3-8a69a7115db5@c10g2000yqi.googlegroups.com...
>>>  It breaks 1NF because the parameters are repeating groups and the 
>>> domain being modelled is the same. Correct, that is the case with CSV so 
>>> you must use by definition a TABLE valued parameter in order to fulfill 
>>> relational theory requirements. <<
>
> Parameter lists are not tables.  I happen to like tabel valued
> parameters and think they have some advantage. I would prefer that
> they be declared in the parameter list instead of outside of it, but
> that is probably coming.
>

I knew you'd gloss over you violating 1NF, you are breaking the set of 
values (from the app) up into parameters just to force it into the 
procedure - very poor.

>>>  It's UN-maintainable - the majority of developers working against SQL 
>>> Server use Management Studio, Management Studio has no macro language, 
>>> in order to handle the hundreds of parameters some automated code 
>>> generator needs to be used in order to save from typo's and errors 
>>> creeping in, that would require the developer to learn and buy yet 
>>> another language. <<
>
> Why not use the macro language in your C compiler? Or your word
> processor? I need to move some of my old WORD stuff over to Open
> Office soon, which was free.
>
> I find a lot of people dislike Management Studio for that and other
> reasons. Pretty printers, macros, source code controls and a lot of
> other feaures are missing. They tend toward Toad and DB-Viz instead or
> other tools. The argument that Management Studio makes code UN-
> maintainable is not a very good one.  No law requires you to use it
> exclusively.
>

In order to write SQL you now need a C compiler? Laughable.

More people like SSMS than dislike it - if there was such a movement 
disliking SSMS then there would be a significant user base of TOAD in the 
SQL Server space - there isn't.

>>>  Have you ever tried to read and debug a stored procedure with a few 
>>> hundred parameters - one such example is the Sudoku procedure which 
>>> whilst doing what it says on the tin is UN-maintainable. <<
>
> Yes, and I have no problem; I use a regular expression to do any edits
> to the set of parameters rather than one parameter at a time. The code
> is so simple and obvious most of the time, you never really look at it
> again. But if you do, it is to extent the pattern rather than change
> processing -- this is just a parameter list.
>

So, as well as having to learn a macro language the developer now needs to 
learn regular expressions.

To summarise, so far in order to do this development you would need a) a C 
Compiler, b) have familiarity with the particular macro language that C 
compiler uses - are macro languages standard? (no) and c) you need to 
understand the complexities and vagaries of regular expressions.

>>>   Domain protection is NOT done, for instance in the Sudoku the data 
>>> type does not prevent you from passing a number that is outside the 
>>> range 0 - 9, in order to do that you need either a load of IF statements 
>>> or you insert all the values into a table which defeats why you used 
>>> parameters in the first place and should have just used a table valued 
>>> parameter. <<
>
> Gee, I used the equivalent of this for my macro. Why use a procedural
> IF when I have declarative CHECK()?
>
> FOR %1 IN [0-9] DO
>   FOR %2 IN [0-9] DO
>       "cell_%1%2 SMALLINT NOT NULL CHECK (cell_%1%2 BETWEEN 0 AND
> 9)"
>
> Then there were similar ones for the column, row and square checking
> rules.

Like I said - in order to get your domain checking you must insert the 
variables into a table, that means one row constructor per parameter - with 
hundreds of parameters that is a significant amount of code.

All this before you've even got to the real logic of the procedure, so 
approx 1,000 lines of code just to handle using parameters rather than a CSV 
or table valued parameter.

Like I said - totally unmaintainable and you'll not find a single Microsoft 
example in the product nor in books online of using your proposed method.

--ROGGIE--


 

>> I knew you'd gloss over you violating 1NF, you are breaking the set of v=
alues (from the app) up into parameters just to force it into the procedure=
 - very poor. <<

The application is not required to be normalized, nor are reports.

>> In order to write SQL you now need a C compiler? Laughable.<<

You will really laugh over the use of word processors for
document,then :)

>> More people like SSMS than dislike it - if there was such a movement dis=
liking SSMS then there would be a significant user base of TOAD in the SQL =
Server space - there isn't. <<

May I quote from BUSINESS WEEK?

Quest Software Wins Three Best of Tech=B7Ed 2010 Awards from Windows IT
Pro and SQL Server Magazines

Quest Toad for SQL Server

Toad for SQL Server is a back-to-back winner for 2009 and 2010 in the
=93Database Development=94 category. An all-in-one SQL Server development
and administration toolset, Toad for SQL Server bridges the
functionality gaps in both Microsoft's SQL Server Management Studio
and Visual Studio.

The Best of Tech=B7Ed judging panel noted that, =93Toad for SQL Server is
the =91Swiss Army knife=92 of development tools. This product won because
it offers an incredibly wide range of functionality, including
IntelliSense; Group Server query execute for running queries on
multiple servers; an advanced SQL Optimizer to analyze alternative SQL
statements; as well as server, database, and data-comparison tools.=94

http://investing.businessweek.com/research/stocks/news/article.asp?docKey=
=3D600-201006140923BIZWIRE_USPRX____BW6114-1&params=3Dtimestamp||06/14/2010=
%209:23%20AM%20ET||headline||Quest%20Software%20Wins%20Three%20Best%20of%20=
Tech%C2%B7Ed%202010%20Awards%20from%20Windows%20IT%20Pro%20and%20SQL%20Serv=
er%20Magazines||docSource||Business%20Wire||provider||ACQUIREMEDIA&ticker=
=3DVMW:US

>> So, as well as having to learn a macro language the developer now needs =
to learn regular expressions. <<

Don't you already know regular expressions?  They are part of Standard
SQL.

>> To summarise, so far in order to do this development you would need a) a=
 C Compiler, b) have familiarity with the particular macro language that C =
compiler uses - are macro languages standard? (no) and c) you need to under=
stand the complexities and vagaries of regular expressions. <<

I think of some level of these things as basic modern programming
skills.

>> Like I said - in order to get your domain checking you must insert the v=
ariables into a table, that means one row constructor per parameter - with =
hundreds of parameters that is a significant amount of code. <<

Did you ever see a serious COBOL program? :) This stuff is nothing.

--CELKO-- (jcelko212@earthlink.net) writes:
>>> As long as I measured performance server-side only, the test procedure
>>> behaved very well, and even competed for the top. But when I measured
>>> the client-side time and thus added the call overhead, the picture was
>>> completely different. Passing 2000 parameters took 500 ms - then it
>>> doesn't matter if the procedure itself completes in 20 ms. << 
> 
>  I saw that and I cannot figure out what Microsoft is doing in the
> client. I expect the database to do very well, since we are using a
> calling stack in most SQLs -- load right into main storage, etc.  But
> I do not know what the client could be doing.  Let's ask Kalen ...
 
It is necessarily not the client. It may be SQL Server that is slow 
in retrieving the parameter values.

Just face it: SQL Server is not designed for this type of solutions.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:c970a1ea-0e5b-4b73-b01c-52c6b3c83ea1@j8g2000yqd.googlegroups.com...
>>> I knew you'd gloss over you violating 1NF, you are breaking the set of 
>>> values (from the app) up into parameters just to force it into the 
>>> procedure - very poor. <<
>
> The application is not required to be normalized, nor are reports.
>

So you'll now have no problems and stop berating people that use pivoting 
techniques in their queries so long as there is an ORDER BY on the query - 
ok? Of course by definition once an ORDER BY is on the query then it's a 
cursor.

But, as ever you are talking rubbish; the application is passing a set of 
data into the database, it seems odd that a relational database MUST insist 
that the set is broken up into a repeating group of parameters.

You've got this entirely wrong - everybody else aside from you knows it; 
like I say - give me a url to somewhere in the product that uses multiple 
parameters - somewhere in books online or one of the product stored 
procedures itself.

>>> In order to write SQL you now need a C compiler? Laughable.<<
>
> You will really laugh over the use of word processors for
> document,then :)
>

Right tool - right job; word processors are for writing documents and not 
code - duh.

>>> More people like SSMS than dislike it - if there was such a movement 
>>> disliking SSMS then there would be a significant user base of TOAD in 
>>> the SQL Server space - there isn't. <<
>
> May I quote from BUSINESS WEEK?
>

Quote all you want, so what you are saying is that the millions of users 
product base of SQL Server, the majority use TOAD.

That's just laughable.

Yes, its a popular tool, yes its won awards, doesn't mean folk use it, in 
fact its my experience as a SQL Server consultant and the guy who runs the 
UK SQL Server User Group who comes into contact with hundreds of SQL Server 
professionals every month that the main tool of choice for SQL Server 
professionals is SSMS.

>
>>> So, as well as having to learn a macro language the developer now needs 
>>> to learn regular expressions. <<
>
> Don't you already know regular expressions?  They are part of Standard
> SQL.
>

And it differs from what developers use.

>>> To summarise, so far in order to do this development you would need a) a 
>>> C Compiler, b) have familiarity with the particular macro language that 
>>> C compiler uses - are macro languages standard? (no) and c) you need to 
>>> understand the complexities and vagaries of regular expressions. <<
>
> I think of some level of these things as basic modern programming
> skills.
>

Laughable, so you are now saying in order to write SQL you must know C - 
unreal.

>>> Like I said - in order to get your domain checking you must insert the 
>>> variables into a table, that means one row constructor per parameter - 
>>> with hundreds of parameters that is a significant amount of code. <<
>
> Did you ever see a serious COBOL program? :) This stuff is nothing.
>

My main language for the first FIVE years of my career was developing and 
maintaining programs written in PL/1, CICS and DB2.

Most new code is written using .NET or JAVA.

But - do, bring the article on; I'll be eagerly looking for it on 
simple-talk.

--ROGGIE--


 

>> It is necessarily not the client. It may be SQL Server that is slow in retrieving the parameter values. <<

I was at a local User Groups meeting tonight and Wes Brown was the
speaker doing a piece on SQL Server 2008 R2. He has been SQL Server
since 6.0, worked by companies that make tools, been a DBA some of the
heavy users of SQL Server in the US, etc. -- he has credentials.

I gave him your rough numbers from your blog and he did not feel they
were right. His quick guess was that the front end was converting each
parameter, then passing it along the DB for a redundant conversion in
some loop. This is based on a wild guess on his way out of the
building and a few decades of prior tuning experience with my
narrative. He has passed a lot more data to SQL Server procedures with
MUCH better numbers.

I am going to send him links to your stuff this week and bribe him
into doing some testing.

DB2 and Oracle have no problems with INSANELY long parameter lists.
They live in a 64-bit world. Same networks, ODBC, etc.

Hell, I even got a positive feedback from a MySQL guy! Their product
sits on top of DOZENS of file engines and is used for most LAMP apps.
There cannot be any kind of physical performance optimizations.

I had one email that reported a single "InputOrderForm" statement that
copied the paper order for the few products they sell instead of a
procedure.

> I gave him your rough numbers from your blog and he did not feel they

So, when are we going to see YOUR numbers from YOUR testing?

Surely if you've researched this properly like Erland has then you must have 
the benchmarks to prove what you are advocating.

So - post them or shut up and stop advocating something based on what you 
"feel" may be correct!

--ROGGIE--

 

>> So, when are we going to see YOUR numbers from YOUR testing? <<

I never test my own stuff; this is what Wes has done for years as part
of his jobs with various third party tools companies. My home machine
should have been replaced three years ago and I have no set up for
testing. Most of my clients are bigger than anything I woudl ever keep
at home

>> So - post them or shut up and stop advocating something based on what you "feel" may be correct! <<

I know that this is a wild idea for a cowboy coder like yourself, but
some of us design test scripts. I know that taking the extra time is
not as much fun as your "git'er done!" approach to programming, but we
just feel better about it.

A more professional approach would have been to propose things that
ought to be in the test suite, but that is just not your style ..

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:a7762bc0-d7d3-4270-8ed7-a697df5ac610@i28g2000yqa.googlegroups.com...
>>> So, when are we going to see YOUR numbers from YOUR testing? <<
>
> I never test my own stuff; this is what Wes has done for years as part
> of his jobs with various third party tools companies. My home machine
> should have been replaced three years ago and I have no set up for
> testing. Most of my clients are bigger than anything I woudl ever keep
> at home
>

Thought as much.

>>> So - post them or shut up and stop advocating something based on what 
>>> you "feel" may be correct! <<
>
> I know that this is a wild idea for a cowboy coder like yourself, but
> some of us design test scripts. I know that taking the extra time is
> not as much fun as your "git'er done!" approach to programming, but we
> just feel better about it.
>
> A more professional approach would have been to propose things that
> ought to be in the test suite, but that is just not your style ..

Lol, in my 24 years as an IT professional with a solid foundation in terms 
of training I am fully aware of the life cycle and believe me I'd never take 
lessons from you on that one!

Frankly all this work has already been done in depth by Erland, his work is 
recognized throughout the community and his articles are the definitive 
point of reference on this matter.

I'd suspect you've not even read his work because you are too dam arrogant 
to accept when you are wrong.

And no - what works on DB2 or MySQL (btw a product people are leaving in 
droves in favor of one of the NoSQL ones because it won't scale) does not 
mean you can shoe horn it onto this product.

Like I've said - do post your article and I'll make sure it gets ripped to 
shreds.

--ROGGIE--

 

I thought Wes Brown plays for Manchester United? I didn't know he
moonlights as a geek!!

--CELKO-- (jcelko212@earthlink.net) writes:
> I gave him your rough numbers from your blog and he did not feel they
> were right. His quick guess was that the front end was converting each
> parameter, then passing it along the DB for a redundant conversion in
> some loop. This is based on a wild guess on his way out of the
> building and a few decades of prior tuning experience with my
> narrative. He has passed a lot more data to SQL Server procedures with
> MUCH better numbers.
 
The main bulk of the test suite runs with Win32::SqlServer a Perl API 
of my own making. I found that table-valued parameters had lousy 
performance, so I wrote simplifed version of the test for SqlClient and
ADO .Net. Which performed a lot better.

Since it could be a similar issue with many parameters, I wrote an 
ADO .Net client for this test as well. This too outperformed my Perl
module, but not as radically as with TVPs. And performance was still
far from acceptable.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Although I have not reviewed your findings I am stunned that you wasted your 
time performance testing with Perl?  I suppose that this might be okay for 
_comparative_ testing as long as DB operations were long enough, but if you 
want to test performance, then try the SQLNCLI C API.  Don't get me wrong, 
..NET is great and I use it every day - just not for things that are supposed 
to be fast - and Perl will perform badly under the best circumstances.  But 
perhaps I am out of the ordinary because I work with applications that 
perform millions of updates in a day to tables with billions of rows and 
therefore my standards are skewed.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DBCF3DBBAD40Yazorman@127.0.0.1...
> --CELKO-- (jcelko212@earthlink.net) writes:
>> I gave him your rough numbers from your blog and he did not feel they
>> were right. His quick guess was that the front end was converting each
>> parameter, then passing it along the DB for a redundant conversion in
>> some loop. This is based on a wild guess on his way out of the
>> building and a few decades of prior tuning experience with my
>> narrative. He has passed a lot more data to SQL Server procedures with
>> MUCH better numbers.
>
> The main bulk of the test suite runs with Win32::SqlServer a Perl API
> of my own making. I found that table-valued parameters had lousy
> performance, so I wrote simplifed version of the test for SqlClient and
> ADO .Net. Which performed a lot better.
>
> Since it could be a similar issue with many parameters, I wrote an
> ADO .Net client for this test as well. This too outperformed my Perl
> module, but not as radically as with TVPs. And performance was still
> far from acceptable.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 

m (m@b.c) writes:
> Although I have not reviewed your findings I am stunned that you wasted
> your time performance testing with Perl?  I suppose that this might be
> okay for _comparative_ testing as long as DB operations were long
> enough, but if you want to test performance, then try the SQLNCLI C API.
>  Don't get me wrong, .NET is great and I use it every day - just not for
> things that are supposed to be fast - and Perl will perform badly under
> the best circumstances.  But perhaps I am out of the ordinary because I
> work with applications that perform millions of updates in a day to
> tables with billions of rows and therefore my standards are skewed. 
 
I used Perl, because it is the client-side langauge I master the best.

And originally when I devised this test suite for SQL 2000, there was 
little reason to assume that the client-side API would matter, since then
I only measured time within the stored procedures. Since all methods were
passed strings, there was little reason to measure client-side overhead.
(The SQL 2000 tests did not include the method "many parameters".)

This changed with SQL 2005 with the introduction of the xml data type,
but the SQL 2005 I only tried infer the overhead by using variations of
the XML procedures.

For the SQL 2008 tests, the situations became untenable with the advent
of TVP, and it was also now I added "many parameters". It was absolutely
necessary to measure call overhead, since parameters were now passed in 
several different ways.

And indeed, using Perl for that task is not a good idea. Not because Perl
is slower or faster than anything else, but because it is not a very
common way to access SQL Server. If I had the time, I should rewrite the
entire test suite in C#. Yes, C# and not native C++, because there are
far more people out there writing data access in C# than in C++. 

And mind you, what my Perl module uses is an API that is built on top
of SQLNCLI in C++.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

A Case expression can only return a single value; hence your problem with 
mixing the IN statement with the Case because the subquery can return more 
than a single value.  You don't say if you want to return all the values or 
none of them when @DeptName is empty.  If you want to retrieve all the 
values when the string is empty, the following code should be OK:

SELECT *
FROM Personnel
WHERE @DeptNames = '' OR Department IN (SELECT data FROM
dbo.fnc_Parse2TableString(@DeptNames, ','))


If @DeptNames can also be Null, then use:

SELECT *
FROM Personnel
WHERE @DeptNames is Null OR @DeptNames = '' OR Department IN (SELECT data 
FROM
dbo.fnc_Parse2TableString(@DeptNames, ','))


Also, in your examples, you have put a space in the sample data:

SET @DeptNames = 'Accounting, Finance'

You should check the code of your fnc_Parse2TableString() function to see 
how it deals with the space character.


Another possibility would be to use the LIKE statement if you get rid of the 
blank spaces and add some extra commas at the beginning and the end of 
@DeptNames:

SET @DeptNames = ',Accounting,Finance,'


SELECT *
FROM Personnel
WHERE @DeptNames LIKE ('%,' + Department + ',%')


The INSTR function could also be used for this.


Finally, using the names (Account, Finance, ...) of the department directly 
in the table Personnel is calling for trouble.  These names should be stored 
in their own table and you should use their IDs (the primary key of this 
second table) as a foreign key in your table Personnel.

-- 
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"SetonSoftware" <seton.software@verizon.net> wrote in message 
news:110c1f18-9822-45ea-874a-0d68b561037d@x21g2000yqa.googlegroups.com...
>I have a SQL Server 2005 stored procedure that will receive a series
> of valeus to be passed to an IN clause. Since IN clauses do not take
> parameters, I need to use a UDF like fnc_Parse2TableString() to parse
> the comma-delimited values into a table object as shown below:
>
> DECLARE @DeptNames varchar(max)
>
> SET @DeptNames = 'Accounting, Finance'
>
> SELECT *
> FROM Personnel
> WHERE Department IN
> CASE
>   WHEN @DeptNames <> '' THEN (SELECT data FROM
> dbo.fnc_Parse2TableString(@DeptNames, ','))
> END
>
>
> What I'm trying to accomplish is to have @DeptNames as an optional
> parameter. Since this SQL does not work as shown, what must I do in
> order to accomplish this?
>
> I really want to avoid dynamic SQL and since I may have many such
> optional IN clause parameters, managing multiple versions of the same
> SQL would become cumbersome.
>
> Thanks
>
> Carl