Prev: using schema in security mode
Next: Float Types Used as Primary Key on SQL 2005
From: --CELKO-- on 20 Jul 2010 16:28
>> 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.

>> 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 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?

Quest Software Wins Three Best of Tech·Ed 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
“Database Development” 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·Ed judging panel noted that, “Toad for SQL Server is
the ‘Swiss Army knife’ 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.”

http://investing.businessweek.com/research/stocks/news/article.asp?docKey=600-201006140923BIZWIRE_USPRX____BW6114-1&params=timestamp||06/14/2010%209:23%20AM%20ET||headline||Quest%20Software%20Wins%20Three%20Best%20of%20Tech%C2%B7Ed%202010%20Awards%20from%20Windows%20IT%20Pro%20and%20SQL%20Server%20Magazines||docSource||Business%20Wire||provider||ACQUIREMEDIA&ticker=VMW: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 understand 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 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.

From: Erland Sommarskog on 20 Jul 2010 17:45
--CELKO-- (jcelko212(a)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(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: Tony Rogerson on 21 Jul 2010 00:33


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:c970a1ea-0e5b-4b73-b01c-52c6b3c83ea1(a)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--




From: --CELKO-- on 21 Jul 2010 01:00
>> 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.


From: Tony Rogerson on 21 Jul 2010 02:03

> 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--



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