John's Brain Dump - All Comments

re: 5 Reasons to use Indexed Views

John — Wed, 30 Jul 2008 14:23:12 GMT

Fred,

On your first question, I'm not exactly sure how to answer without seeing your actual tables & queries. Based on what you've said, I don't think that you need a new index on your second view. Linking customer ID from your indexed view to the customers table should be a trivial operation that won't benefit much from using an indexed view, so I wouldn't worry about putting an index on the second view.

On your second question, you should be able to request the second view. If implemented according to my article, your second view will reference the first (indexed) view, so your query will gain the benefit of the view index.

Hope that helps! Keep the questions coming.

John

re: 5 Reasons to use Indexed Views

frederic_eynard — Wed, 30 Jul 2008 12:41:21 GMT

[comment moved from my old blog]

Hi John,

Thanks for your article.

I tried to apply your solution, but I encountered some difficulties.

To Summary, I have 3 tables: date, customer and order table. In the first one, I have the YEAR column ( to simplify.. ) the second one, I have the cust_id, first name, last name. And the last one, I have a ref to date table, a ref to customer table, and a ORDER_AMOUNT column.

I have created a first index on YEAR, SUM(ORDER_AMOUNT), CUST_ID.

This works fine. The sql optimizer uses my index.

I try to use your method to get the customer name with the previous result.

So I created a new view as mentioned in your articles. But after that, I did not able to create the new index, because on sqlserver 2005, the index can not be created on a view.

The other question I have. If you want to obtain YEAR, SUM(ORDER_AMOUNT), CUST_ID and LASTNAME, do You need to make the request directly on your second view or do you leave the SQL optimizer rewrite the query?

Thanks in advance

Fred

NDoc 2.0 is (Not Quite) Dead!

John's Brain Dump — Wed, 23 Jul 2008 20:59:01 GMT

Many of you were saddened by the news that Kevin Downs quit development on NDoc , the .NET documentation

re: 5 Reasons to use Indexed Views

John — Tue, 15 Jul 2008 18:15:42 GMT

You're right Ryan--the second query couldn't be indexed. But I'm not suggesting that the second query be indexed. In order for this example to work, you'll need both views. (Sorry if that's not clear.)

The first view (vwAccountTransactionBalance_Indexed) is the indexed view, where your performance gains are made. The second view (vwAccountTransactionBalance

) uses the first view and includes the LEFT JOIN to ensure that no accounts are missed because they have no transactions. The second view also ensures that you're using the index on your view by specifying the (NOEXPAND) hint.

You should always use the second view in your application queries, and you'll get the benefit of the index on the first view.

I hope that clears things up!

re: 5 Reasons to use Indexed Views

Ryan — Tue, 15 Jul 2008 17:51:48 GMT

You would not be able to index the second query in #4, because of the LEFT JOIN.

re: Finding the Current and Last Page Numbers of a Crystal Report in ASP.NET 1.1

Gosvami — Tue, 04 Dec 2007 16:19:03 GMT

I found this solution looking for something else: [code] CrystalDecisions.Web.ViewInfo vi = crViewer.ViewInfo; pagenum.Text = vi.PageNumber + " of " + vi.LastPageNumber; [end code] URL of the forum: "http://forums.asp.net/p/1078417/1589473.aspx#1589473" it almost worked. I tried putting the code inside the init, or in the page_load, but it displays the correct last_page, only if you navigate manually to the 2nd page. I hope it can help someone else to find a best solution...

re: Code Formatter for Windows Live Writer

Steve Dunn — Tue, 02 Oct 2007 18:19:29 GMT

Hi, version 1.0.0.3 is now available with more languages and compatible with the latest beta of Windows Live Writer. Cheers, Steve

re: TableAdapterManager in ADO.NET Orcas

Andrey Titov — Wed, 08 Aug 2007 19:48:38 GMT

thanks, good review

re: Finding the Current and Last Page Numbers of a Crystal Report in ASP.NET 1.1

Michael Yousupov — Mon, 04 Jun 2007 09:43:56 GMT

I Can not Find the Current , Last Page Number and sum pages in a Crystal Report in ASP.NET 1.1

If someone can help me I will be gratefull?

re: GMail vs. Windows Live Mail

Sat, 02 Jun 2007 01:28:15 GMT

gmail is very nice except for one HIGHLY annoying (to me) feature called conversations. it actually drove me to live mail, which I have never used (or hotmail for that matter). The only things that keeping me with gmail is that you can access it via pop3 in outlook, and the hope that gmail will allow you to turn the conversation feature off. if it wasn't for the pop3 access I would ditch it immediately. Some people really like conversations, but to me they utterly confuse my inbox.

re: TableAdapterManager in ADO.NET Orcas

John — Tue, 01 May 2007 21:45:20 GMT

John, don't forget that with the DST you not only get this functionality now, but supported in 1.1 framework as well.

TableAdapterManager in ADO.NET Orcas

TrackBack — Tue, 01 May 2007 20:32:21 GMT

TableAdapterManager in ADO.NET Orcas