Prev: using schema in security mode
Next: Float Types Used as Primary Key on SQL 2005
From: Erland Sommarskog on 24 Jul 2010 07:09
m (m(a)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(a)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

From: Sylvain Lafontaine on 24 Jul 2010 21:56
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(a)verizon.net> wrote in message
news:110c1f18-9822-45ea-874a-0d68b561037d(a)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


First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: using schema in security mode
Next: Float Types Used as Primary Key on SQL 2005