11/30/2004

MSMQ 3.0 and Multicast

Amazing what you can learn when you RTFM, ya know? From the Message Queueing in Windowx XP: New Features:


MSMQ 3.0 in Windows XP extends the regular MSMQ programming model from one-to-one to one-to-many. The traditional MSMQ model allows a single message to be sent to a single queue. However, the one-to-many messaging model enables clients to send the same message to multiple recipient queues. MSMQ 3.0 offers multiple ways to implement this model:
" Real-time messaging multicast
" Distribution lists and multiple-element format names

In MSMQ 3.0, one-to-many mechanisms provide the application developer with the fundamental tools required to build sophisticated publish and subscribe systems. These kinds of systems are useful for addressing reliable information dissemination scenarios.
Real-Time Messaging Multicast
MSMQ 3.0 provides a mechanism to send messages on top of IP multicast.
Specifically, MSMQ 3.0 uses the Pragmatic General Multicast (PGM) protocol (see http://www.ietf.org/internet-drafts/draft-speakman-pgm-spec-06.txt), which is becoming the de facto enterprise standard for obtaining a degree of reliability on top of IP multicast.

In MSMQ 3.0, when an application sends a real-time multicast message, a single copy of the message is sent on the network, but this message can then be received and processed by numerous destination applications. This method is extremely efficient if the same message has to be delivered to a huge number of receiving applications.

Real-time multicast messaging supports only an "at-most-once" quality of service. In particular, it does not support transactional sending. To send a real-time multicast message, the existing Send API has been extended so that the sender can specify a target IP multicast address. The receiver's programming model is not affected at all. An IP multicast address property can be attached to a queue, and subsequently every message sent to that multicast address will be inserted into the queue by MSMQ 3.0. Applications continue to read messages from the queue using standard MSMQ APIs and methods.

From a security point of view, standard MSMQ authorization and authentication continue to be available, but MSMQ 3.0 does not provide specific encryption or decryption for real-time multicast message.

Sounds like the Spammer's Full Employment Act to me!

And here is a performance issue I didn't know before:

Use Windows Security Sparingly

MSMQ uses the standard Windows security model. You can configure queues to permit only senders and receivers with appropriate security privileges. The tradeoff for including Windows security in messaging is that it takes about 2.5 times as long to send the same messages. By configuring MSMQ not to send the security descriptors associated with the sending application, you can obtain significant performance gains. To configure MSMQ not to send a security descriptor with the message, set the message property AttachSenderID to False (the default is True):


mq.DefaultPropertiesToSend.AttachSenderId=false;

11/22/2004

Another Believer! (EXEC sp_name param, 'param') -Not!

Vis-a-vis my last number on why not to use "Exec spname paramvalue" etc. -- my friend figured out that all the places he was doing new SqlDataAdapter("exec mysp 1,5 'hello'"); are open to SQL injection attack (he thought they were immune to it). Why? Simple- if an attacker can get access to the SQL string they can just add ";DELETE TABLENAME". DOH!

So now he is happily rewriting everything to use SqlHelper and turning all those inline text strings into object[] parms={1,5,'hello'}; object arrays.

You see that? You are only dumb when you think you know everything....

11/12/2004

ADO.NET and SQL Server Optimization

Recently a compatriot and I got into this diatribe over the use of CommandType.Text and "EXEC spName param, param, ..." to make SQL Server stored procedure calls.
I had chimed in on his answer to a poster who was having difficulty with a sproc call, and he suggested "you don't need to use the Command Object" and proceeded to show an illustration like the above.

Even after I had provided some pretty good background material on why this is not a good idea, he continued to hammer on little pieces of the subject, in effect missing the big picture. There have been many times I've been corrected. As I've grown older (and hopefully wiser) I've learned to put my ego in my back pocket and thank the person for the education. And you? I guess what I am trying to say is this; you have two MVPs who didn't know about this. The only reason I found out about it is because I was lucky enough to attend Tech-Ed 2004 and sit in Gert Drapers' presentation. So if two C# MVP's weren't aware of it, how many other people do you suppose are writing whole applications using Language Event processing instead of RPC Event processing as they should?

The bottom line is, you should never use CommandType.Text and concatenated "EXEC SPNAME PARM PARM" convention. In fact, even if you are not using stored procedures at all - you should parameterize your text queries - because the SQL Server engine is smart enough to "Template-ize" them - and cache the compiled execution path for re-use. What I posted was Gert Drapers' PowerPoint Presentation to illustrate. Gert Drapers, for those who don't know, is a Software Architect in the SQL Server Development group at Microsoft. He is involved in the architecture design and performance tuning of the most challenging applications and large-scale database deployments around the world; worked on both SQL Server 7.0 and 2000; first as Software Design Engineer in the SQL Server Storage Engine and later as the Product Unit Manager of the SQL Server Management Tools team and as Architect of SQL Server DTS. So, he certainly has the credentials, IMHO.

CommandType.Text = Language Event
You should not execute stored procs using CommandType.Text , that's what your "EXEC spName param param" example is doing.

You should use RPC Event for All procedure invocations
"CommandType.StoredProcedure" and add the correct SqlParameter objects to the Command.

There are numerous reasons to do this, many are illustrated in the downloadable Powerpoint Presentation, which you are welcome (and encouraged) to view.

Here are some of the notes (slightly edited) that I took of Mr. Drapers' presentation at Tech-Ed 2004:

First, we need to make a decision which command type to use. It is NOT a convenience issue. From a server persepective, there is a significant difference.:

IN SQL Server you have language events, and RPC events, This is a split code path. In other words, If you want to run a stored procedure, you want to be sure that you both start and end inside the RPC Event source code path. You do this by explicitly setting commandType.StoredProcedure in the ADO.NET SQLClient class. You don't want to execute it as a Language Event, because that is not going to trigger the most optimal code path inside SQL Server.


If SQL Profiler shows you a SQL Statement starting event and then you see anything else than INSERT, UPDATE, DELETE, or if you see "EXECUTE " you are probably dealing with a stored procedure. That means that your program is actually choosing the wrong code path.

Parameter re-use: If you send in a "EXEC" - type select concatenated text vs Parameterized Command, the execution plan gets recompiled. But , when you use named parameters in ADO.NET, you are actually creating a Template. This Template is how SQL Server achieves plan re-use and does not have to recompile the execution plan for the query.

You can actually see this plan re-use and optimization by querying select * from master.dbo.syscacheobjects . In SyscacheObjects, there is a Sql column and a usecounts column. Sql holds the sql fragment, and usecounts shows how many times the SQL Engine has been able to re-use the code path without having to recompile, resulting in more optimized code and lower CPU utilization.

So, for example if you run 10,000 stored proc invocations with different (or even the same) parameter values using CommandType.Text and "EXEC SPNAME param1 param2" etc., these will be usually recompiled every single time (depending on whether autoparameterization is used by SQL Server). This burns up CPU cycles. Event though the performance difference may not be readily apparent, you can bet that this is an inefficient way to do it. When you use CommandType.StoreProcedure with SqlParameters, it gets compiled ONCE - and re-used by the Sql Server engine every subsequent time. This ain't rocket science!

Parameterized select statements are normally significantly faster than "text - only" select statements.

And of course, with Stored Procedures and selects / inserts, using fully parameterized execution results in significant performance increases.

Client - side Parameter Caching

You can also use a Client-side caching mechanism for SqlParameters. The MS Data Application Blocks "SqlHelper" class already has a very nice one built in. By caching the parameter set for each stored procedure, I've seen performance increases ; it seems that the creation of ADO.NET SqlParameter objects is expensive, and if we can cache and re-use them , there are substantial economies of throughput to be obtained.

If a multi-insert operation is bracketed within a transaction, the deferred writing to the transaction log creates much enhanced throughput. Drapers illustrated this in real time in his Tech-Ed presentation and I was, well - trust me, its hugely faster! Also client side Parameter Caching (a.k.a. SqlHelper) can increase performance.

What others have to say about the subject:


Kirk Allen Evans (Excerpts):

Databases create a query plan or "internal road map" to determine the quickest and most efficient route to deliver the requested data. The database's cache manager compiles the query plan and stores it in its cache when you execute a query for the first time. If you execute the same query text again, the database retrieves the query plan from the cache and uses it to retrieve the data. But if the query differs by even one character, such as the data in a WHERE clause, the database must compile the query again, generate a new query plan, and store the query and plan in its cache.

If an application searches for a customer by name, many different cache listings will appear in syscacheobjects for essentially the same query.

A more efficient approach than forcing the cache manager to recompile queries each time is to reuse the same query multiple times using parameters. This saves CPU cycles because it avoids recreating cache entries. SQL Server provides an excellent mechanism for doing this through its implementation of stored procedures. Create a stored procedure that accepts a variable and use the variable in the SQL WHERE clause.

The real performance difference occurs with complex SQL statements that are inlined, where the WHERE clause varies only by its values. This causes discrete cache entries into the cache plan, meaning the query is parsed for execution plan each time the query is run.

The Prepare statement caches the statement and indicates to the provider that the same statement will be run many times. The provider may or may not perform optimimizations to support this, which SQL Server does. By executing this each time within the loop, you cause extra processing. Move this out of the loop, because you only have to prepare it once.

Second, you will get better performance by using a parameterized SQL statement by replacing the literal values with question marks and using parameters in place of the literal values. The parameters can then be reused as well, where you simply set the values during each iteration.

Third, you can enhance performance a little more if your provider supports the .Prepared property for the Command object. The .Prepared property pre-compiles the SQL statement and caches it, avoiding parses for invaalid SQL statements.

One more comment: you are not going to see a noticeable time difference between using parameterized SQL queries that simply call a stored proc over using a CommandType of StoredProc. The real difference occurs back on the server, where the procedure is parsed and cached.


Jesse Ezell:

In any case, ALWAYS use parameterized queries. Anything else is a rediculously [sic] lame decision.


Barry Gervin:

In terms of SQLServer and Oracle, there are no implicit performance improvements between a stored proc and a parameterized inline sql statement. Both of them have their execution plans compiled at first execution - and then reused.

In SqlServer - with non-parameterized sql - the syntax will be parsed and auto-parameterized - so 2nd & nth executions on similar sql (different hard coded values) should exhibit performance on scale with parameterized sql (or stored procs).

Sometimes the auto-parming doing by Sql isn't as efficient as a developer could manually parm it. Think of a case when you have a constant in the query that neverchanges: where inactive="n" and state="CA". SqlServer will auto-parm the "n" and the "CA" but you know that "n" will never change. That would lead to not as efficient parameterization.

And Finally, an example of using the Prepare Statement--

public void SqlCommandPrepareEx() {
int id = 20;
string desc = "myFirstRegion" ;
SqlConnection rConn = new SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer");
rConn.Open();
SqlCommand command = new SqlCommand(null, rConn);

// Create and prepare an SQL statement.
command.CommandText = "insert into Region (RegionID, RegionDescription) values (@id, @desc)" ;
command.Parameters.Add ( "@id", id) ;
command.Parameters.Add ( "@desc", desc) ;
command.Prepare() ; // Calling Prepare after having set the Commandtext and parameters.
command.ExecuteNonQuery();
// Note- this command is now "Templatized- if you change parameter values and call it again,
// SQL Server will re-use the execution path without needing to recompile.

// Change parameter values and call ExecuteNonQuery.
command.Parameters[0].Value = 21;
command.Parameters[1].Value = "mySecondRegion";
command.ExecuteNonQuery();
}




Some useful links about all this:

SyscacheObjects( MSDN):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_3dyr.asp

Kimberly Tripp:
http://www.sqlskills.com/blogs/kimberly/CategoryView.aspx?category=Optimizing%20Procedural%20Code

Kalen Delaney ("Inside SQL Server 2000"):
http://www.winnetmag.com/SQLServer/Articles/ArticleID/6113/pg/2/2.html

Andrew Novick:
http://www.databasejournal.com/features/mssql/article.php/2203601

SQL Server 2005:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

The Bottom Line!
The lesson from all this is that just because something "works" and you have gotten used to doing it doesn't mean you automatically have the right to become a self-proclaimed "guru" and recommend it to everybody else in the face of facts, figures and arithmetic that explain why what you are doing is NOT "best practices code". And if you are obstinate and refuse to accept the facts, digging in to protect your already indefensible position, shame on you! Don't use CommandType.Text to execute stored procedures. If you are lazy and you don't like to type, use the SqlHelper class with (connectionstring,spname,object[] parmvalues) and your SqlParameters will be automatically CACHED for you! Finally, ALWAYS parameterize your text sql queries with parameter placeholders and REAL SqlParameters!

Obstinacy is the result of the will forcing itself into the place of the intellect.

--Arthur Schopenhauer 1788-1860, German Philosopher .

11/11/2004

SWIG - Creating Managed Wrappers

Today I discovered SWIG - which makes writing managed C# wrappers for unmanaged C++ libraries and DLLs hugely easier. I am in the process of studying managed C++ wrapper code, "It Just Works" and P/Invoke to handle some needs related to exporting methods from telephony board APIs.

Although I haven't gotten very deep into it, one look at all the C# code SWIG generated from a single imported *.h header file has me convinced!

11/06/2004

To Deficit - or Not To Deficit? (Don't believe everything you hear):

One of the biggest Bush-bashers during the 2004 Presidential Campaign was the "Huge Deficits" that Pres Dubya recklessly ran up, right? Not!

Drops in revenues and increases in outlays occur automatically during a cyclical downturn and then reverse themselves during a cyclical upturn. There is also a "cyclically adjusted deficit". The cyclically adjusted surplus or deficit is calculated to show the underlying outcome of the federal budget when those automatic movements are removed.

Policy actions by the Congress and the President, such as tax or spending legislation, create changes in the total budget surplus or deficit that are distinct from the automatic cyclical movements. The cyclically adjusted surplus or deficit includes the effects of those legislated changes.

The CBO calculates a different measure, the standardized-budget surplus or deficit, that attempts to remove those factors as well as the effects of the business cycle. As a result, the standardized-budget surplus or deficit is the more speculative. That is the one the Democrats were screaming about, trying to scare the pants off the average American, who really doesn't know much about economics.

Calculations of cyclically adjusted budget measures attempt to remove the effects of the business cycle on revenues and outlays (that is, the cyclical part of the budget). For example, cyclically adjusted revenues exclude the loss of revenues that automatically occurs during recessions. Likewise, cyclically adjusted outlays exclude the additional spending that follows automatically from a rise in unemployment. The difference between those two measures is the cyclically adjusted surplus or deficit.

CBO estimates that the cyclically adjusted deficit will increase from 2.6 percent of potential GDP in 2003 to 3.2 percent in 2004 and then decrease to 2.8 percent in 2005 --an increase of 0.2 percent. A good look at the chart of the Cyclically adjusted deficit below shows that we are quite clearly well within the historical range, and given the fact that we are still emerging from the nasty stock market bubble and recession that Dubya inherited, along with the effects of 9/11, it isn't reckless at all.


CBO Cyclically Adjusted Deficit Chart
CBO Historical Chart of Adjusted Surplus/Deficit


11/03/2004

Did the Republicans have a Gamma Ray Burst?

or was it just crappy exit polling?

At any rate, the popular vote was representative of the largest voter turnout in American history, and George Soros is probably looking for a monastery he can move into.

If I sound partisan, it's because I have become so, with America becoming so incredibly polarized. Now maybe the Bush-hating left wing elite media establishment will shut up for the next four years and we can get on with business! These people have become so arrogant over the years, they have really become out of touch with mainstream America, as the election has proven. And the exit polls - boy that's just a whole 'nuther issue entirely!

Q: What is 50,000 Liberals at the Canadian Border?
A: A good start....

11/02/2004

Telephone Pet Peeve

Don't you just LOVE these people that leave answering machine messages like they are gonna give you the history of the world, or something? I do most of my business work with my cell phone, leaving the main phone free for the Significant Other. Calls come in all the time when she's out, and since that phone is just off my office and I don't bother answering it, I get to hear all the dumb messages people leave.

I mean, "Hello, this is who I am , this is why I am calling, and here's my phone number", right? Oh, NO! They gotta tell you everything and take up 10 minutes of time with all their B.S., when all that can ever happen is you are either gonna call them back or not, right?