SterlingPorter.net

June 25, 2008

DATETIME parameters cause slow execution plan in SQL Server 2005

Filed under: .NET, SQL Server — sterling @ 8:18 pm

Today I wrote a monster query to give me the abandon rate for each step of our checkout process at Sewell Direct. We’ve been using good old Google Analytics, but we really want to see the data up to the minute, hour, etc. Anyway, running it in SQL Server Management Studio took about 4 seconds for a date range of one week. After I was satisfied, I plopped it in a stored procedure and ran it again for good measure–39 seconds. What?!! I thought (as many others do) that stored procedures are supposed to beat dynamic queries, especially on the first run before the dynamic query’s execution plan has been cached.

After a little digging, I discovered a few things. First, when running a dynamic query, SQL Server Management Studio (or whatever client you’re using like ADO.NET) actually prepares the execution plan. Apparently, SSMS was doing a better job than SQL Server proper was when the stored procedure was compiled. I found this forum post that explains why. Basically, the parameter types play a huge role in the preparation of the execution plan. My stored procedure was using two DATETIME parameters (e.g. @StartTime, @EndTime) whereas my dynamic query just CAST two string-based representations of the dates into DATETIME variables for use in the query. When I changed the parameters to VARCHAR(23) and passed in string-based dates, the stored procedure ran in 4 seconds, just like the dynamic query!

Before:

CREATE PROCEDURE CheckoutProcessAbandonRateReport
@StartTime datetime,
@EndTime datetime
AS
BEGIN
...

After:

CREATE PROCEDURE CheckoutProcessAbandonRateReport
@StartTimeStr varchar(23),
@EndTimeStr varchar(23)
AS
BEGIN
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @StartTime = (CAST(@StartTimeStr as datetime))
SET @EndTime = (CAST(@EndTimeStr as datetime))
...

I’m going back though a few of our other hefty queries to see if this tweak can speed those up as well.

December 22, 2006

.NET Remoting connection issues over VPN

Filed under: .NET — sterling @ 5:38 am

Lately I’ve been working on a “smart client” application that connects to a company application server. The app server acts as a write-through cache on the way to the back-end database. Multiple smart clients can interface with the app server as well as a few web sites. All communication between the app server and its clients is done using .NET remoting. One of the advantages of the smart client and how is uses .NET remoting is that I can enforce role-based security using the WindowsPrincipal.IsInRole function. I can prevent anyone who is not a member of a designated security group from even running the smart client. Cool, eh?

Unfortunately, a problem arose when users tried to run the smart client to connect to the app server over a VPN. In this situation, the call to IsInRole() failed every time, even though the user had provided his domain credentials when logging into the VPN. I assumed that Windows would simply be able to check a given group (e.g. DOMAIN\Security Group) against all the known identities, i.e. local account and the domain account authenticated when the VPN connection was made. However, this is not the case. Apparently, there is no way to use the credentials that are used to authenticate on the VPN. Simply put, if you call IsInRole() on the client machine, it will use the local account, and unless the local machine is a member of the domain in which exists the said group (or if the domain is trusted by the first domain), then IsInRole() will fail.

Of course, the point here is to provide single-sign-on authentication. In other words, only make the user provide credentials once–when connecting the VPN. After a lot of reading and a support incident with Microsoft, I arrived at a solution.

My first mistake was to try to implement role-based security in the smart client. For many reasons, including security risks, role-based security should be done on the server in a client/server architecture, NOT in the client. Secondly, the domain credentials used to authenticate over the VPN are only used for data going OUT over the VPN. In other words, local applications (including the smart client) are run under the local account, but remoting calls headed to the app server arrive in the context of the authenticated domain account. So, with some proper configuration, the app server can perform role-based security checks on this account context.

The key is how you instantiate the TcpServerChannel object when setting up your .NET remoting calls on the client and server. The following code is an example of how to do it on the SERVER:

// Setup the configuration parameters through a dictionary
IDictionary properties = new Hashtable();
properties.Add(”port”, channelPort); // e.g. 8080
properties.Add(”secure”, true);
properties.Add(”impersonate”, true); // allows us to do role-based security on the server

// Create an instance of a channel
TcpServerChannel serverChannel = new TcpServerChannel(properties, null);
ChannelServices.RegisterChannel(serverChannel, true);

Here is how you do it on the CLIENT:

// Setup the configuration parameters through a dictionary
IDictionary properties = new Hashtable();
properties.Add(”secure”, true);
properties.Add(”connectionTimeout”, 5000);
properties.Add(”tokenImpersonationLevel”, “Impersonation”);

TcpClientChannel clientChannel = new TcpClientChannel(properties, null);
ChannelServices.RegisterChannel(clientChannel, true);

The “impersonation” properties on both the client and the server make it so when a remoting call occurs, the app server executing the method will assume the identity context of the caller (e.g., the domain account being used over the VPN). With this great feature, you can call IsInRole() and perform your role-based security. Problem solved.

Side note: I encountered another problem that was actually caused by this particular fix. The problem had to do with connecting to another server from the app server, specifically Sql Server. I’ll post this scenario and how I fixed it later.

Powered by WordPress