System Time and WCF service

It was one of the strange errors and I debugged it for hours. The error started occurring all of a sudden and did not have much detail. The same services were working on the other server. We were using wshttp binding and the server was hosted on IIS 7. I started thinking if this is something to check in IIS 7 since this is the only server we were using IIS latest version. Here is the error description

System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail. ---> System.ServiceModel.FaultException: An error occurred when verifying security for the message.

After some research found that it happens when the server time is not sync with the client system. When I checked the server, it was 20 sec delayed time than other servers on the same domain. I was not sure if someone changed it purposefully or how it got changed. All I did was change the time to sync with other servers. Then the service started working.

Updated 1/29:

Above were all my assumptions when I got this error first time, and since it fixed the problem, I was kind of in impression that the server time change was the issue. But it happened again, so its not only the server time. I reviewed it some more and found that this happens only when the client and server time is not in sync. The same application which is using the services on the same domain was working fine but when it was accessing from outside domain it had a problem. The client which was accessing the service was in IST time zone and the services hosted on one US server which was on EST time zone. When I observed the time difference, the client system was almost 7 minutes ahead than the EST. I went and just reset the client time to sync with server that is change to 7 minutes less. The application started working. But this is still kind of temporary solution or a work around. The WCF allows only 5 min time difference.

As a permanent fix, we can use a custom binding with
• LocalClientSettings.MaxClockSkew
• LocalServiceSettings.MaxClockSykew

But I haven’t tried using that yet. But would want to try and incorporate with application instead of dealing with these time changes because if the application is used by many different networks, it gets very difficult to track.

WCF binding and Proxy

I had many applications consuming my WCF services since long time. It was all fine even in production. Recently we had a windows service which had to use the same service. And we did refer the WCF as usual and deployed the service on one of the test box. The service started breaking all of a sudden with below error.

System.ServiceModel.EndpointNotFoundException: Could not connect to http://[IP]/Service.svc. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [Different IP]:3128. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [Different IP]:3128
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
--- End of inner exception stack trace ---
at System.Net.HttpWebRequest.GetRequestStream()
at System.ServiceModel.Channels.HttpOutput.WebRequestHttpOutput.GetOutputStream()
--- End of inner exception stack trace ---

Server stack trace:
at System.ServiceModel.Security.IssuanceTokenProviderBase`1.DoNegotiation(TimeSpan timeout)
at System.ServiceModel.Security.SspiNegotiationTokenProvider.OnOpen(TimeSpan timeout)
at System.ServiceModel.Security.WrapperSecurityCommunicationObject.OnOpen(TimeSpan timeout)

I had a WCF hosted with http://[IP]/Service.svc. When I try connecting to this service it was throwing error “TCP error code 10060”. And the error message used to show different IP which is not the actual host where the WCF is hosted. I have changed that as [Different IP] and it was using some other port. I did verify the proxy settings and the proxy was some other IP. I was not sure from where it was picking the proxy ip and trying to connect.

But as a temporary solution I made the config change not to use default proxy by setting useDefaultWebProxy="false". This setting solved the error.

<binding name="WSHttpBinding_IService" closetimeout="00:10:00" opentimeout="00:03:00" receivetimeout="00:10:00" sendtimeout="00:03:00" bypassproxyonlocal="false" transactionflow="false" hostnamecomparisonmode="StrongWildcard" maxbufferpoolsize="2147483647" maxreceivedmessagesize="2147483647" messageencoding="Text" textencoding="utf-8" usedefaultwebproxy="false" allowcookies="false">
<readerquotas maxdepth="2147483647" maxstringcontentlength="2147483647" maxarraylength="2147483647" maxbytesperread="2147483647" maxnametablecharcount="2147483647">
<reliablesession enabled="false" ordered="true" inactivitytimeout="00:10:00">
<security mode="Message">
<transport realm="" clientcredentialtype="Windows" proxycredentialtype="None">
<message clientcredentialtype="Windows" negotiateservicecredential="true" algorithmsuite="Default" establishsecuritycontext="true">
</security>

</binding>

Common Table Expressions, Subqueries and Views

I am sure you are aware of different types of joins if you have used Sql server or any Database. Joins can be used on tables to get cumulated record set. If we have a requirement to get a sum or average or any type aggregate function, we use group by with combined record set. If you have come across a situation where you need to join the table but you have to get some additional records which might require some other table join or aggregation, the normal approach would be creating a function and return as table or create a view. If you are not clear what am I trying to say, I am going to take an example.

Lets consider a Employee and EmployeeLeaves tables

CREATE TABLE [dbo].[Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[Status] [char](1) NULL,
)

CREATE TABLE [dbo].[EmpLeaves](
[EmpID] [int] NULL,
[EmpLeave] [int] NULL,
[EmpLeaveType] [varchar](5) NULL,
[Date] [date] NULL
)



Now lets use a straight forward query and get all employees and their leave details. The below query does the same thing.

[Query 1]
select * from Employee, EmpLeaves where Employee.EmpID = EmpLeaves.EmpID

Lets say I need to get number of total leaves applied by each employee, I would simply write as

[Query 2]
select Employee.EmpID,EmpName,EmpLeaveType,SUM(Empleave) as NumberOfleave from Employee, EmpLeaves where Employee.EmpID = EmpLeaves.EmpID
group by Employee.EmpID,EmpName,EmpLeaveType
order by 1 ;


Ok. Now if I need sum with the above record set (Query 1), how do I get? One option to use subquery

[Query 3]
select *,(select SUM(Empleave) from EmpLeaves where EmpID = empl1.EmpID and EmpLeaveType = empl1.EmpLeaveType) as NumberOfleaves
from Employee, EmpLeaves empl1 where Employee.EmpID = empl1.EmpID
order by 1;

So till now we are all good and this is the simplest way we could achieve this. Now lets say I need one more column in my result set say Number of times the leave was applied. That is nothing but number of rows for each employee based on leave type.


[Query 4]
select *,(select SUM(Empleave) from EmpLeaves where EmpID = empl1.EmpID and EmpLeaveType = empl1.EmpLeaveType) as NumberOfleaves,
(select COUNT(Empleave) from EmpLeaves where EmpID = empl1.EmpID and EmpLeaveType = empl1.EmpLeaveType) as NumberOfTimes
from Employee, EmpLeaves empl1 where Employee.EmpID = empl1.EmpID
order by 1;

but here we are using a subquery which is using the same table but used twice. Can we fine tune this anyway? If you have used views, you could create a view and use that in your query

CREATE VIEW [dbo].[vWGetNumLeave]
AS
SELECT COUNT(EmpLeaveType) AS NumberofTimes, SUM(EmpLeave) AS NumberofLeaves, EmpLeaveType, EmpID
FROM dbo.EmpLeaves
GROUP BY EmpID, EmpLeaveType

GO


Now lets use this view in our query


[Query 5]
select Employee.*,EmpLeaves.*, empcount.NumberofLeaves ,empcount.NumberofTimes
from Employee, EmpLeaves, vwGetNumLeave empcount
where Employee.EmpID = EmpLeaves.EmpID and empcount.EmpId = Employee.EmpID and empcount.EmpLeaveType = EmpLeaves.EmpLeaveType
order by 1 ;


So now we get both NumberOfleaves and NumberOfTimes in our query without using any aggtreation in our query. All the aggregation part of logic has been moved to view. If we need any additional logic or any new column that needs to be added, we can add it on view or function and plug that in our query.

But now what if we are using this only in one place? Is it worth creating view? Consider you have several incidents where you need such aggreations but then you keep adding views? So here is the use of common table expressions newly introduced with sql 2005. I am sure if you understand the use of it, you will start using it in many places.

[Query 6]
with EmpCount AS (Select COUNT(EmpleaveType) as NumberofTimes, SUM(Empleave) as NumberofLeaves, Empleavetype, Empid from
EmpLeaves group by EmpID, EmpLeaveType)
select Employee.*, EmpLeaves.*,EmpCount.NumberofTimes,EmpCount.NumberofLeaves from Employee
inner join EmpLeaves on Employee.EmpID = EmpLeaves.EmpID
left outer join EmpCount on EmpCount.EmpID = Employee.EmpID and EmpCount.EmpLeaveType = EmpLeaves.EmpLeaveType
order by 1;

Common Table expression (CTE) can be thought of as a temporary result set within the execution scope. So now if we need to get any specific logic which is not referred anywhere, we have a option to create as CTE.

The basic syntax for CTE

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

The statement to run the CTE is:
SELECT
FROM expression_name;

This can also be used as recursive which I will try to explain sometime later. I have taken this example to explain how CTE is different than normal joins and subqueries. This example is only to give a basic idea, if you come across any better examples or implementations you can share with me :-)

DateTime.ParseExact bug when converting yymmdd format :-)

I have not seen many applications use yymmdd format but I had to get some date from our legacy server which had yymmdd date on one of the column. I always used DateTime.ParseExact method to parse the date and get the format which I wanted. Here also I had used the same.

Recently, one of the rows had a date like 300101 which is actually 2030-01-01 but this method was converting it to 1930-01-01.

objDateTime = DateTime.ParseExact(“300101”, "yymmdd", System.Threading.Thread.CurrentThread.CurrentCulture);

Strange!!

This seems to be odd and I tried passing 290101 and it converted correctly as 2029-01-01. I am still kind of thinking that this is a bug in this method but I am not sure if this method was intended to do this.It was converting to Century as 19 only when the year has more than 2030. But anyways I have changed my prgram to change the format to yyyymmdd if the year is more than 20 years from now. Just thought it might help if someone using yymmdd.

Indexes and Order by clause

Have you ever thought about “order by” clause that any of your query is using in your application when designing index for table? If not you may start checking it after reading this post. You will also know if there is any relationship between index and order by clause.


If you are using any index with single column and using order by in your query it doesn’t make any difference. I have created a sample table called Items just to explain different scenarios.

Here is my table

CREATE TABLE [dbo].[Items](
[ItemiD] [int] NOT NULL,
[CreatedDate] [datetime] NULL,
[ClosedTime] [datetime] NULL,
[ItemType] [int] NULL,
[Status] [char](1) NULL
)

Add few records just get something when we do the query.

Lets add below indexes.

CREATE NONCLUSTERED INDEX [IX_Itemtype] ON [dbo].[Items]
(
[ItemType] ASC
)
CREATE NONCLUSTERED INDEX [IX_Dates] ON [dbo].[Items]
(
[CreatedDate] ASC,
[ClosedTime] ASC
)

I have two indexes created now. One with single column and another with two columns. Let me go through some cases with different combinations.

Before executing below queries, enable Executaion plan window. You can do this by Right click on Query panel and select Include Actual Exection plan.

Query: Select * from Items order by Itemtype

Result Execution plan

Query 2: Select * from Items order by Itemtype desc

If you look at the Estimated cost for above queries, both the queries has the same value and ASC and DESC has no effect on performance.

Now let us check the same with multiple columns.

Query 3: Select * from Items order by createddate, closedtime

The cost still the same.
Query 4: Select * from Items order by createddate desc, closedtime desc

Even with this query, everything looks ok and no effect on performance. Now let us look at the last option

Query 5: Select * from Items order by createddate, closedtime desc





Here you can find a major difference with the cost and also if you observe the execution plan, you can notice that the Index Scan step is missing. The indexes are created with asc order for both the coumns. When the query uses both the coulumns with different orders, index will not be used. You need to be careful when you are defining indexes for multiple columns especially when those columns are used on order by clauses. This may degrade the system. Most of the time we will be in impression that the indexes are created for making the search faster and do not have to worry on Order clause. Indexes are also important for order by clause and we should consider different possibilities before creating multiple column indexes.