My first impressions on Windows Phone 7

Before you start reading this, you should know this is a very negative review, coming from a .NET developer, that works exclusively with Microsoft technologies (and enjoys it:))

Being a Microsoft enthusiast and .NET developer I wanted to go for the new Windows Phone 7, instead of an Iphone or Android.

After playing the entire day with my new wp7 here are my first impressions:

At first, I said wow! The graphics are cool, especially the animations. The look and feel of the whole OS is great. I’m not complaining here, though I have to admit the graphics are inferior to the ones on iphone and android. It just doesn’t have the same glassy look. Knowing how much Microsoft obsesses on glass interfaces in Windows Vista and 7 I though they would at least try to do the same on WP7, especially since Iphone and Android do. Let’s leave it at that…the interface is cool but it could have been much better.

Browsing on it is very easy and pleasant. It’s probably what I like best at it.

The Outlook on the phone is certainly more finger friendly that it used to be on windows mobile 6.1 and 6.5, but I find it a lot more difficult to quickly select a large number of email and mark them as read, and even worse, there is no select all button. Huge downside imho. I’m using the phone to check an account I also use on the PC, so most of the mail I will have already read on the PC, so I need to quickly mark it as read without too much fuss.

Another really cool thing is the facebook integration in the contacts application (now called People). I like it because I can quickly import the pictures of everybody in my list and link it to my existing phone book. If you have the First Name and Last Name set properly it will be done automatically. If not you will have to link manually, though the suggestion mechanism reduces this to a few clicks only. Very cool feature.

And now we are getting the the unpleasant stuff:
1. You can import your contacts from your sim card, but you can’t save them on the SIM! The contacts get store on the phone and on your windows live account. Ouch. I tend to move my sim card to various phones and I can’t carry my address book around. It seems very useful to switch sim card between the business phone and personal phone, but only if you can prevent the address books from mixing. This one is almost a deal breaker. Let’s say though I could live with this.
2. The contacts are synced automatically on your live id. In theory it’s a nice thing, but when I want to make a call I DON’T WANT TO SEE THE EMAILS FROM MY LIVE ID. I would want phone numbers. Can’t delete them either because you delete them from the live id. This sucks big time.
3. Once you’ve entered a live id account you can’t change it anymore, unless you factory reset your phone and loose everything: photos, documents, emails and contacts (yes contacts because they are not stored on the SIM!!!) I really don’t understand what Microsoft team was thinking here? Or not thinking at all to be more accurate……
4. I can’t buy anything in the market place. Looks like Romania is not on the list of countries that can use the marketplace (and it looks like it’s a short list). Why the f are you selling this crappy phone here then? Imho a smartphone is good because you can install anything on it and extend it’s functionality limitless. Not with a Windows Phone….I’m stuck with whatever Microsoft decided to put on it and a few free apps. Now this really is a deal breaker!

My advice is: buy an iphone or an android. Really! If you are not living in the US when you can definitely buy apps then just avoid it. For the rest of us it’s not even a smartphone!

Advertisements

Rewriting NOP_ProductLoadAllPaged with CTE to solve the performance problems in NOPCommerce 1.7

As a .NET developer, every time I was asked to create an online shop I turned to NopCommerce, since it was free and had every feature one could image. The code is very professional and the architecture a beauty.

Well the last few weeks I also saw the ugly side of NopCommerce. Apparently the devs only tested it on “Mom and pop’s” shops with few products. My project required integration with some distributors APIs and automatic importing of products. 27000 products later NopCommerce was shacking, and the VPS that was running it struggling with hard cpu usage and huge memory consumptions. Considering other e-commerce applications have no problem with this number of products my attention tured at profiling and to the SQL queries used.

The worst part was the admin interface. The entire product management is centered around the same stored procedure that searches and fetches products by a complicated set of criterias, such as categories, min and max prices, keywords, stock, etc. This proc, named NOP_ProductLoadAllPaged, is called on every single page and there is no caching on this. Not even per request (not saying that caching per request would help anything – I’d try sql dependency cache if I was NopCommerce dev team). So I open out sql profiler and to my horror the query took 21 seconds to run. Can you guess why I though the admin panel was unusable?

After some googleing it turns out I’m not the only one to notice this. Jared Nielsen wrote on his blog here http://www.fuzionagency.com/post/2010/09/18/NOP-Commerce-Product-Administration-Slow-with-Large-Number-of-Products-and-Variants.aspx

Jared didn’t have that many products, but he had a lot of variants. He noticed that the actual search query ( a very two page long query) joins too many tables for it’s own good, in an attempt to get all the required information right from the start, and then started to work on the paging with two temporary tables and some queries. The problem with joining every product with categories, variants, manufacturer mapping, product mapping, tags etc is that even with a few hundred products you quickly end up doing matches on 1 million rows. That’s a real performance killer. Jared’s solution was to do the search and only extract productId. He fetches the actual fields only after the paging and everything done, right before the rowset is returned.

Initially I was very happy with his idea, but after I’ve implemented it I realized how different our situations were. I didn’t have lots of variants (one for each product), nor tags, etc. Turns out that in my case it only shaved out 1 sec from the 21 seconds the stored procedure required 😦

After looking at the execution plan I immediately understood what was wrong. Let me explain how NOP_ProductLoadAllPaged works:

First there is a very large query that searches everything that matches the criteria. Going to product admin in the admin panel, calls this proc immediately with no criteria, returning all 27k products. All results from this query is entered in a temp table, with the purpose of executing a query on them and adding and extra column with a rownumber:

CREATE TABLE #PageIndex
	(
		[IndexID] int IDENTITY (1, 1) NOT NULL,
		[ProductID] int NOT NULL
	)

	INSERT INTO #PageIndex ([ProductID])
	SELECT ProductID
	FROM #DisplayOrderTmp with (NOLOCK)
	GROUP BY ProductID
	ORDER BY min([ID])

See the identity column? It’s their way of number the results, so that in the next query they can select the results from any page (for instance results from row nuber 20 to 30):

SELECT
		p.ProductId,
		p.Name,
		p.ShortDescription,
		p.FullDescription,
		p.AdminComment,
		p.ProductTypeId,
		p.TemplateId,
		p.ShowOnHomePage,
		p.MetaKeywords,
		p.MetaDescription,
		p.MetaTitle,
		p.SEName,
		p.AllowCustomerReviews,
		p.AllowCustomerRatings,
		p.RatingSum,
		p.TotalRatingVotes,
		p.Published,
		p.Deleted,
		p.CreatedOn,
		p.UpdatedOn
	FROM
		#PageIndex [pi]
		INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID
	WHERE
		[pi].IndexID > @PageLowerBound AND
		[pi].IndexID < @PageUpperBound
	ORDER BY
		IndexID

So the stored procedure just created two temporary tables with 27000 products each, and all this just to create another column with the rownumber. The irony is that exactly this operation, of inserting huge amount of data on a table with an identity index is causing the performance problem. I’m not saying the other temp table isn’t a bad idea either but this not the way to number rows. That’s what the ROW_NUMBER() function is there for!!!

So if we don’t need a temp table to number rows we can rewrite the whole thing into one large query with either a derived tabled or CTE (Common table expressions). I rewrote everything without temp tables and i managed to get the time down to 3 seconds for a full not-pages result set, and a few milliseconds for a 15 item page of results. The problem was that now I didn’t have a way to return the total number of items before the paging, since you can mix data retrieval and variable assignments in the same query. This mean that I did have to split the query in two with a temp table. So I did, but the difference is that my temp table only contained the results from one page (by default only 15 results), and only the productId column with an extra ‘TotalResults’ column, with no indexes to make inserting slow.

The resulting stored proc executes on my server in 4 sec with a large page of 1 mil items and in 1 sec for 15 item page. This is a massive improvement from the original 21 sec.

So what I basically did is to put the large search query inside a CTE (Common Table Expression), added rownumbering with ROW_NUMBER() OVER (ORDER BY p.ProductID), counted the initial result set with COUNT(*) OVER(PARTITION BY NULL) and make a simple paging query on the CTE, that inserts only the content of the page in a small temp table. Then I retrieve the total results set from, which I now have as an extra column in the temp table and put it as the return value of the stored proc. At the end I simply return all the results from my temp table.

We started with two large 27000 items temp tables, and ended up with only one, 15 items table with no indexes to slow down inserts. The time dropped from 21 sec to 4 sec, so that’s a 500% reduction in time. Not bad. Of course it can still be cut in half (to 1-2 sec) if you implement proper paging in the C# code of the NopCommerce. Right now the gridview gets ALL results and does the paging himself, which is not optimal. In a future article I might show you how I implemented the custom paging in NopCommerce. In the mean time you can read this article about custom paging: http://www.codeproject.com/KB/aspnet/ASPNET_DataGrid_Paging.aspx

Here is the optimized stored procedure, that I wrote starting with from Jared Nielsen’s version. I’ve kept some of his ideas but decided that it’s better to join another table (Nop_ProductVariant) that to run 2 scalar functions on each row, each doing a select. I’m not sure why Jared though scalar functions get optimized by the server. As far as I know they don’t get included in the execution plan at all, so using them not only is an O(n*n) operation like the join he tried to avoid, but you loose the benefit of execution plan optimization and compiling.

-- =============================================
-- Author: www.bitstarsolutions.com - Tudor Carean
-- =============================================
ALTER PROCEDURE [dbo].[Nop_ProductLoadAllPaged]
(
	@CategoryID			int = 0,
	@ManufacturerID		int = 0,
	@ProductTagID		int = 0,
	@FeaturedProducts	bit = null,	--0 featured only , 1 not featured only, null - load all products
	@PriceMin			money = null,
	@PriceMax			money = null,
	@Keywords			nvarchar(MAX) = '',	
	@SearchDescriptions bit = 0,
	@ShowHidden			bit = 0,
	@PageIndex			int = 0, 
	@PageSize			int = 20, --2147483644,
	@FilteredSpecs		nvarchar(300) = null,	--filter by attributes (comma-separated list). e.g. 14,15,16
	@LanguageID			int = 7, --0,
	@OrderBy			int = 0, --0 position, 5 - Name, 10 - Price
	@TotalRecords		int = null OUTPUT
)
AS
BEGIN
	
	--init
	SET @Keywords = isnull(@Keywords, '')
	SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'

	SET @PriceMin = isnull(@PriceMin, 0)
	SET @PriceMax = isnull(@PriceMax, 2147483644)
	
	--DECLARE @PageIndexTable TABLE  
	CREATE TABLE #PageIndexTable
	(		
		[ProductID] int NOT NULL,
		[total] int
	)
	
	--filter by attributes
	SET @FilteredSpecs = isnull(@FilteredSpecs, '')
	CREATE TABLE #FilteredSpecs
	(
		SpecificationAttributeOptionID int not null
	)
	INSERT INTO #FilteredSpecs (SpecificationAttributeOptionID)
	SELECT CAST(data as int) FROM dbo.[NOP_splitstring_to_table](@FilteredSpecs, ',');
	
	DECLARE @SpecAttributesCount int	
	SELECT @SpecAttributesCount = COUNT(1) FROM #FilteredSpecs

	--paging
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)	
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1;
	
		
	WITH DispOrderCTE (ProductIDCTE, /*RowNumber,*/total) AS
	(
		SELECT DISTINCT 
			p.ProductID, 
			--ROW_NUMBER() OVER (ORDER BY p.ProductID),
			COUNT(*) OVER(PARTITION BY NULL)				
			FROM Nop_Product p with (NOLOCK) 	
			INNER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID		
			LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID 
			LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID 
			LEFT OUTER JOIN Nop_ProductTag_Product_Mapping ptpm with (NOLOCK) ON p.ProductID=ptpm.ProductID 
		WHERE	
		( 
			--P.ProductId = 9
			--AND 
			( 
				-- See if product is published
				@ShowHidden = 1 OR p.Published = 1 
			)
			AND
			( 
				-- See if at least one product variant is published
				@ShowHidden = 1 OR (SELECT Max(Convert(int,Published)) FROM nop_ProductVariant where ProductID=p.ProductID) = 1 
			) 
			AND 
			( 
				p.Deleted=0 
			) 
			AND
			(
				-- Make sure the product is listed between a certain price range
				(SELECT Min(Price) FROM nop_ProductVariant WHERE ProductID=p.ProductID GROUP BY ProductID)
				BETWEEN IsNull(@PriceMin,0) AND IsNull(@PriceMax,999999999999)
			)
			AND 
			( 
				@CategoryID IS NULL 
				OR @CategoryID=0 
				OR 
				(
					pcm.CategoryID=@CategoryID 
					AND 
					(
						@FeaturedProducts IS NULL 
						OR pcm.IsFeaturedProduct=@FeaturedProducts
					)
				) 
			) 
			AND 
			( 
				@ManufacturerID IS NULL 
				OR @ManufacturerID=0 
				OR 
				(
					pmm.ManufacturerID=@ManufacturerID 
					AND 
					(
						@FeaturedProducts IS NULL 
						OR pmm.IsFeaturedProduct=@FeaturedProducts
					)
				) 
			) 
			AND 
			( 
				@ProductTagID IS NULL 
				OR @ProductTagID=0 
				OR ptpm.ProductTagID=@ProductTagID 
			) 			
			AND 
		( 
	-- search standard content 
			patindex(@Keywords, isnull(p.name, '')) > 0 
			or patindex(@Keywords, isnull(pv.name, '')) > 0 
			or patindex(@Keywords, isnull(pv.sku , '')) > 0 
			or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0) 
			or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0) 
			or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0) 
			-- search language content 
			-- commented out localized product variants to save speed
			--or patindex(@Keywords, isnull(pl.name, '')) > 0 
			--or patindex(@Keywords, isnull(pvl.name, '')) > 0 
			--or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.ShortDescription, '')) > 0) 
			--or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.FullDescription, '')) > 0) 
			--or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pvl.Description, '')) > 0) 
		) 
		AND
			(
				@ShowHidden = 1
				OR
				(getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999'))
			)			 
			AND 
			( 
		--filter by specs 
				@SpecAttributesCount = 0 
				OR 
				( 
					NOT EXISTS
					( 
						SELECT 1 FROM #FilteredSpecs [fs] 
						WHERE [fs].SpecificationAttributeOptionID NOT IN 
						( 
							SELECT psam.SpecificationAttributeOptionID 
							FROM dbo.Nop_Product_SpecificationAttribute_Mapping psam 
							WHERE psam.AllowFiltering = 1 AND psam.ProductID = p.ProductID 
						) 
					) 
				) 
			) 
		) 
	)		
	INSERT INTO #PageIndexTable (ProductId,total)
	SELECT templist.ProductIDCTE,templist.total from 
	   (select  fulllist.ProductIDCTE,
				fulllist.total, 
				ROW_NUMBER() OVER (ORDER BY fulllist.ProductIDCTE) as RowNumber
		from DispOrderCTE fulllist		
		) templist
		WHERE
			RowNumber > @PageLowerBound AND 
			RowNumber < @PageUpperBound
		ORDER BY
		RowNumber
	
		
	
	SELECT  
		p.ProductId,
		p.Name,
		p.ShortDescription,
		p.FullDescription,
		p.AdminComment,
		p.ProductTypeId,
		p.TemplateId,
		p.ShowOnHomePage,
		p.MetaKeywords,
		p.MetaDescription,
		p.MetaTitle,
		p.SEName,
		p.AllowCustomerReviews,
		p.AllowCustomerRatings,
		p.RatingSum,
		p.TotalRatingVotes,
		p.Published,
		p.Deleted,
		p.CreatedOn,
		p.UpdatedOn		
	FROM
		#PageIndexTable [pi]
		INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID	
	
	if ((select COUNT(*) from #PageIndexTable)>0)
		SELECT @TotalRecords=max(total) from #PageIndexTable
	else
		select @TotalRecords = 0
	
		
	SET ROWCOUNT 0

	
	DROP TABLE #FilteredSpecs
	DROP TABLE #PageIndexTable
/*
--- Test Harness
exec [dbo].[Nop_ProductLoadAllPaged] @PageSize=2147483644
*/
END

servers with multiple ips and HttpWebRequest

I’ve been working on a windows service that uses HttpWebRequest to scrape some data from a website. It was however important to use a certain IP from the 5 that the server had, and never touch the rest of them. The HttpWebRequest class doesn’t seem to provide with an obvious way to do this, or expose the socket it’s using for the connections.

Right when I was about to write my own HttpWebRequest class using nothing but sockets, I stumbled on the  ServicePoint.BindIPEndPointDelegate property. Aparently you can use that to set a callback at the time the socket receives it’s IP, and change it.

You can do something like this:

HttpWebRequest request = (HttpWebRequest) WebRequest.Create("http://MyServer");
request.ServicePoint.BindIPEndPointDelegate = new BindIPEndPoint(BindIPEndPointCallback);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

Then just declare the callback, and return any local IP you want.

public static IPEndPoint BindIPEndPointCallback(ServicePoint servicePoint, IPEndPoint remoteEndPoint, int retryCount)
{
     return new IPEndPoint(IPAddress.Any,5000);
}

Also make sure you check the addressfamily of the remoteEndPoint. If your request goes to ipv6 don’t return an ipv4 ip :))

Viewstate compression – the right way

I’ve just made a very nice looking asp.net page that lists some orders in a gridview. What the client wanted was to see a summary of all orders in his online shop, and then to quickly click on an expand button to see all the details, and even modify them, all without a full postback. No Problem. I made a component that uses a ajax toolkit collapsible panel to switch between the summary and order details, put an edit button that turned all labels into textboxes/dropdowns/etc, put the control in a gridview and binded it to the DB. Nice and easy. It works beautifully except that it’s slow. Dead slow.

After a quick investigation I was shocked to discover the viewstate has over 100KB in size. I guess that’s what happens when you load so much data in a gridview. Should have known better. After obvious optimizations, such as not binding order details that haven’t been expanded yet I managed to reduce the viewstate to 60KB. Still very bad. So I decided to compress it.

The first thing I did was google for “viewstate compression”. I found this article: http://www.codeproject.com/KB/viewstate/ViewStateCompression.aspx

At first I though I was saved. It looked like a great article. So I gave it try.

This is his method:

protected override object LoadPageStateFromPersistenceMedium() {
    string viewState = Request.Form["__VSTATE"];
    byte[] bytes = Convert.FromBase64String(viewState);
    bytes = Compressor.Decompress(bytes);
    LosFormatter formatter = new LosFormatter();
    return formatter.Deserialize(Convert.ToBase64String(bytes));
  }

  protected override void SavePageStateToPersistenceMedium(object viewState) {
    LosFormatter formatter = new LosFormatter();
    StringWriter writer = new StringWriter();
    formatter.Serialize(writer, viewState);
    string viewStateString = writer.ToString();
    byte[] bytes = Convert.FromBase64String(viewStateString);
    bytes = Compressor.Compress(bytes);
    ClientScript.RegisterHiddenField("__VSTATE", Convert.ToBase64String(bytes));
  }

I made a new BasePage inheriting from Page and overwrited LoadPageStateFromPersistenceMedium() and SavePageStateToPersistenceMedium(). I copy-pasted the compress and decompress methods, made my page inherit the BasePage and hit F5. Suprize! Ajax is broken. The author didn’t take into consideration that postbacks are of two types: syncronous and asyncronous.
ClientScript.RegisterHiddenField() only works for normal postbacks. No biggie. We can fix by replacing his call to RegisterHiddenField with:

System.Web.UI.ScriptManager sm = System.Web.UI.ScriptManager.GetCurrent(this);
if (sm != null &amp;&amp; sm.IsInAsyncPostBack)
	System.Web.UI.ScriptManager.RegisterHiddenField(this, "__VSTATE", vState);
else
	Page.ClientScript.RegisterHiddenField("__VSTATE", vState);

When it finaly seems to be working with AJAX too (and getting good results with the compression – just 7 KB) I hit a snag: the viewstate wasn’t working propperly inside my usercontroll. I have some dropdownlists that no longer get restored 😐 After one hour of painfull debugging and playing with Fiddler I gave up and decided to write my own viewstate compression.

After a bit of reading in msdn I found out that Microsoft had very different ideas on how this should be achieved. Apparently the class Page has a property PageStatePersister, that is being used to define the ViewState behavior. This is my new Basepage, that replaces the standard PageStatePersister, with my own.

public class BasePage : Page
{
    private ViewStateCompressor _viewStateCompressor;

    public BasePage(): base()
    {
        _viewStateCompressor = new ViewStateCompressor(this);
    }

    protected override PageStatePersister PageStatePersister
    {
        get
        {
            return _viewStateCompressor;
        }
    }
}

All that is left is to write a new PageStatePersister:

public class ViewStateCompressor : PageStatePersister
{
    public ViewStateCompressor(Page page)
        : base(page)
    {
    }

    private LosFormatter _stateFormatter;

    protected new LosFormatter StateFormatter
    {
        get
        {
            if (this._stateFormatter == null)
            {
                this._stateFormatter = new LosFormatter();
            }
            return this._stateFormatter;
        }
    }

    public override void Save()
    {
        using (StringWriter writer = new StringWriter(System.Globalization.CultureInfo.InvariantCulture))
        {
            StateFormatter.Serialize(writer, new Pair(base.ViewState, base.ControlState));
            byte[] bytes = Convert.FromBase64String(writer.ToString());
            using (MemoryStream output = new MemoryStream())
            {
                using (GZipStream gzip = new GZipStream(output, CompressionMode.Compress, true))
                {
                    gzip.Write(bytes, 0, bytes.Length);
                }

                //base.Page.ClientScript.RegisterHiddenField("__PIT", Convert.ToBase64String(output.ToArray()));
                ScriptManager.RegisterHiddenField(Page,"__PIT", Convert.ToBase64String(output.ToArray()));
            }
        }
    }

    public override void Load()
    {

        byte[] bytes = Convert.FromBase64String(base.Page.Request.Form["__PIT"]);
        using (MemoryStream input = new MemoryStream())
        {
            input.Write(bytes, 0, bytes.Length);
            input.Position = 0;
            using (GZipStream gzip = new GZipStream(input, CompressionMode.Decompress, true))
            {
                using (MemoryStream output = new MemoryStream())
                {
                    byte[] buff = new byte[64];
                    int read = -1;
                    read = gzip.Read(buff, 0, buff.Length);
                    while (read &gt; 0)
                    {
                        output.Write(buff, 0, read);
                        read = gzip.Read(buff, 0, buff.Length);
                    }
                    Pair p = ((Pair)(StateFormatter.Deserialize(Convert.ToBase64String(output.ToArray()))));
                    base.ViewState = p.First;
                    base.ControlState = p.Second;
                }
            }
        }
    }
}

That’s it folks. This one works even under my complex AJAX scenario and with user controls. I believe this is how Microsoft intended things to be.