# Monday, July 14, 2003

Efficient Data Retrieval?

Is that a good title for an MSDN WebCast? Who knows? But then again, when have I ever did anything normal?

For those of you who did not see this popular session in Dallas at TechEd or can't make it on out to Kuala Lumpur, Malaysia for TechEd in late August, you can catch this session Thursday at 2pm EST via an MSDN WebCast.

When Microsoft asked me to put together this session back in February for TechEd Dallas, I had no idea how to approach it. Target the DBA, TSQL Programmer, or VB/C# programmer? I am not a DBA, not even close. I also don’t think that I am a full time “TSQL Programmer”, even though I spend hours each day writing TSQL. I use to be a full time VB/C# developer, but now as “CTO” of my company, I spend more time architecting and managing than coding, but I still do write a fair amount of C# code each day. (Epically when my developer calls in sick and we have deadlines!)

So I took a radical approach (or what I call the Bill Clinton approach) and tried to be all things to all people. Most small firms in this economic downturn don’t have a full time DBA, SQL Programmer and VB/C# developers on staff. Some times the DBA is the TSQL Programmer and sometimes the VB/C# developer is the TSQL Developer. When I polled the over 700 people who attended this session at TechED, only a handful of the crowd was only one of these jobs, a vast number were all three. This is bad, I am a firm believer in splitting the role of DBA and TSQL programmer as full time positions. But I understand the economic times so this session was born.

So the WebCast? We start with some TSQL code to make data retrieval from SQL Server easier and smarter. (We will talk about the obscure but totally powerful The Rozenshtein Method that my pal Richard Campbell showed me and like being unplugged from the Matrix for the first time I had an epiphany about my relationship with TSQL). After we talk about TSQL we will hit some classic DBA issues, l mostly about indexes and file groups. Then we will move on into taking advantage of this stuff from ADO .NET, all the tips and tricks that the VB/C# developer will enjoy. So there is something for everyone, hope to see you there. You can sign up for it here.

Here is the abstract:

Take a look at how to optimize using Stored Procedures for efficient and secure data retrieval in the middle tier of your Web, Windows, mobile and Web services applications. Learn how to do crosstab queries for reporting that take seconds to execute instead of hours, exploiting sub-queries and taking advantage of self-joining. Explore performance tuning from the perspective of stored procedures used for data retrieval. Look at how to get a higher cache-hit ratio, efficient index creation and utilization and how to guarantee that ADO.NET takes advantage of these optimizations. Focus on squeezing the last bit of performance out of ADO.NET. Look at how to best architect your application to take advantage how ADO.NET was designed to work with data in a disconnected. Look at comparisons between the DataSet and the DataReader, and an explanation of the best use for each of these objects. Look at techniques for using multiple result sets in one DataReader or DataSet for efficient client databinding, using stored procedures for dynamic sorting, proper connection pooling, optimizing connections with the DataAdapter, and ExecuteScalar vs. ExecuteNonQuery comparisons.  

posted on Monday, July 14, 2003 11:53:45 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [15] Trackback
# Sunday, July 13, 2003

The USS Ronald Reagan (CNV 76)

On Saturday I watched the with some friends the commissioning of the USS Ronald Reagan. The USS RONALD REAGAN was built by Northrop Grumman Newport News in Newport News, Virginia, the only company in the United States with the unique ability to build nuclear powered aircraft carriers. RONALD REAGAN is the ninth Nimitz class aircraft carrier. Being nuclear powered, it can operate for more than 20 years without refueling.

I did feel old when my friend's daughter asked us: "Who was Ronald Reagan?"

posted on Sunday, July 13, 2003 8:30:43 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, July 11, 2003

Who Needs No Stinkin’ Transaction Log (Well Sometimes)


I have a problem. I have to transform 33 million rows from one table to another in my staging SQL Server. The problem is that my Insert Into statement is getting logged and it takes over 10 hours to run due to File I/O. I want it to run faster since the data is not 100% ready and I will have to make some changes to the raw data then retransform in. I called my teammate and pal Andy Catlin (who claims that 1.1 million people read this blog daily) for some advice on how to have the insert statement without getting logged to the transaction log. So here is my insert statement that takes 10+ hours:


Insert into dbo.RPT_ADNPMP12

Select CCODE as County_ID, SICGP as SIC_Code, Rpt_Year, Rpt_Quarter, SpendOrig, SpendDest, 10 as MediaType_ID, 'Other Publications' as MediaType, 11 as Sort_Order,

0 as PercentOfTotal,(ONLNAT+ONLDIR) + ONLLOC  as TotalSpending_AMT,

ONLLOC as Local_AMT,  ONLNAT+ONLDIR  as National_AMT

From dbo.PreptoUpload


Andy said to use with caution the following, setting the recovery mode (using alter database) before and after the insert statement. Not logging made the 33 million rows run in less than 2 hours from the 10 it took with logging. Here is the same code not logged:


Alter Database TestStageToProduction SET Recovery Simple


Insert into dbo.RPT_ADNPMP12

Select CCODE as County_ID, SICGP as SIC_Code, Rpt_Year, Rpt_Quarter, SpendOrig, SpendDest, 10 as MediaType_ID, 'Other Publications' as MediaType, 11 as Sort_Order,

0 as PercentOfTotal,(ONLNAT+ONLDIR) + ONLLOC  as TotalSpending_AMT,

ONLLOC as Local_AMT,  ONLNAT+ONLDIR  as National_AMT

From dbo.PreptoUpload


Alter Database TestStageToProduction SET Recovery Full


So my advice to you is to use this with CAUTION, the transaction log is a GOOD THING, but in a rare case like this when we are just transforming data from one table to another life is good without the log.

posted on Friday, July 11, 2003 6:30:04 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [18] Trackback
# Thursday, July 10, 2003

Outlook Auto Complete is Evil

I am sure that most of you have a problem with having way too many email addresses and more importantly a lot of old email addresses. Or have a hotmail account for MSN IMs and a real account that you actually read.

Well, if you ever reply to an email via Outlook, Outlook will save that email address in its cache and when you type in a name in the TO line in the future, Outlook will auto complete for you to that email address. A feature? Well what if you also sometimes, rarely, send email from an account that you don't want people to send to you. Meaning I run a list server and it comes from my hotmail account. Two friends started to send mail all the time to my hotmail account and it would go to my junk folder (but herbal Viagra and porn came right in to my inbox). I begged and pleaded with them and they did not know what to do.

Well the Dutch to the rescue. My pal Remi Caron steered me to a KB article (http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b287623) that will show you how to clear this cache. The steps below are how to completely clear this cache. I can not figure out how to edit this cache at this point since I can't understand the file format when I open it in UltraEdit32.

Use the steps in the following section to reset the Outlook 2002 nickname cache for both Microsoft Windows 2000 and Microsoft Windows Millennium Edition (Me).

  1. Quit Outlook.
  2. Start Windows Explorer.
  3. On the Tools menu, click Folder Options, and then click the View tab.
  4. Under Advanced Settings, click to select the Show hidden files and folders check box.
  5. Click OK.
  6. Click Start, point to Search, and then click For Files or Folders.
  7. In the Search for Files or Folders box, type *.NK2 in the File Name box.
  8. In the Look In box, click to select your local hard disk.
  9. Click Search Now.
  10. Right-click the .NK2 file with the name of the profile that you want to reset, and then click Rename.
  11. Rename the file to profilename.bak, and then press ENTER.
  12. Quit Windows Explorer.
  13. Restart Outlook.

Outlook will generate a new nickname cache.



posted on Thursday, July 10, 2003 10:58:25 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [5] Trackback
# Wednesday, July 9, 2003

WS-Federation Security Specification

IBM and Microsoft (companies that are not known to be friends) released yesterday the WS-Federation Security Specification. It has not been released to the W3C for specification (but most likely will), mostly because the W3C is moving too slow and IBM/BEA/MS want the business world to start using this new standard. WS-Federation also follows IBM & Microsoft’s by-now familiar way of delivering WS- specifications with partners, signing-up ISVs with expertise in specific markets. Verisign and BEA put their names to WS-Security and Business Process Execution Language for Web Services (BPEL4WS) for example.

I really like the approach of Microsoft and IBM, standards bodies are not moving fast enough and we need this technology now. WS-Federation will really allow users of future Services to interoperate much easier with better security-by federating the user’s identity across several Web Services. This is a good spec. The problem is that since the W3C is sitting on its hands, there is competition in the standards space. That competition comes from the obstructionist company called SUN Microsystems.

SUN Microsystems cried foul today to the release WS-Federation security specification. Will this near bankrupt obstructionist company just stop acting all bitter and spiteful and kill off the competing Liberty Alliance specification for once and for all and join with IBM, BEA and Microsoft with the WS enhancements. Jeeze, if IBM and Microsoft can agree, why can't SUN bury the hatchet for the good of the industry. Liberty has the smell of CORBA to me (remember that specification that nobody understood or used).

I think that Liberty is dead in the water.

Some background:

"There's got to be some overlap there," said Britta Glade, vice chairwoman of the Liberty Alliance Project's business and marketing expert group. "It focuses on federated identity. That's what we've been focusing on for two years."

Sun--whose two biggest rivals are Microsoft and IBM--launched Liberty at the behest of Visa International, but the effort now is controlled by many companies. There is no unwillingness to incorporate others' technology in Liberty, Glade said, adding that the group incorporated some of the work by an earlier Microsoft-IBM Web services effort, WS-Security.

posted on Wednesday, July 9, 2003 11:04:29 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [11] Trackback
# Tuesday, July 8, 2003

Another RD returns to the Mother Ship


Steve Lasker, the Microsoft Regional Director for Dallas, TX has joined the collective and is going to work for Microsoft as the Program Manager for the VB .NET team. He has started a few days ago and so far he is happy (but Seattle has not gotten any rain yet!). Good luck Steve!


After Chris Sells and other RDs moved to the evil empire this year, more and more good people are making the move to Microsoft.


This raises the question, will I ever work for Microsoft? Not anytime soon.

posted on Tuesday, July 8, 2003 4:04:16 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [21] Trackback
# Monday, July 7, 2003

The Future of Microsoft Access


The most difficult session I had to do at TechED in Barcelona was DAT231: Migrating Your Mission Critical Access Applications to SQL Server. The reason why it was hard was I had to convince a roomful of Access developers about the merits of upsizing to SQL Server and that since the two products architectures are so different, upsizing your application is actually going to be a rewrite in .NET (Windows or ASP .NET) or COM/ASP. The crowd did not boo me off the stage and I actually got pretty good scores and comments-it was a very highly ranked session on the Data Management Track. With some distance in Paris and time to reflect on long plane journeys, I started to think about the future of Microsoft Access.


It use to be that an Access developer can make the jump from Access VBA development to VB/COM development with not that much effort. That migration path does not exist anymore. Access and VB/COM had lots of things in common, including the language VBA (yes VB 6 developers, VBA is the language behind the Product you know as Visual Basic 6.0.) But .NET is just way too different.


Not sure what has to happen, maybe an Access .NET?? Who knows.

posted on Monday, July 7, 2003 6:04:47 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [14] Trackback
# Sunday, July 6, 2003

Detour for le Tour


Homeward bound from TechEd Europe and in Paris watching Stage 1 of the Tour de France. Cheering on Lance Armstrong and all of US Postal. After the Stage, went to the top of the Eiffel Tower and then walked to the start of the Champs-Elysées at the Arch de Triumph and then had some dinner on and strolled down the Champs-Elysées all the way to the Plaza de la Concorde and the Louvre.

Tomorrow is back to work in New York! Can’t wait to get home…


posted on Sunday, July 6, 2003 4:53:32 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [13] Trackback
# Saturday, July 5, 2003

And then there were two….


As fast as it started, TechED Europe is now behind us. Most everyone has went home today, of the crew we have been hanging out with this week, only myself and Bart DePetrillo the CEO of NewTelligence are left and we are about to hit the beach.


TechED was such a great conference, average session evaluations were up and the attendance was up year over year. The 10th Anniversary of TechED Europe was very special. The Imagine Cup was just well done and the contestants so impressive and inspirational. I have seen many of the contestants in the halls and parties and talked in length to them about their career goals and ambitions after they graduate. The sessions were terrific and my two sessions on Thursday and Friday went super. Surprisingly all tired from Thursday’s party, Friday’s session was very highly ranked, one of the best in the data track overall. (Thanks everyone who attended for the great scores and remarks!)


Thursday’s Attendee party was such a hoot. RD’s rocked the house with some out of this world Karaoke, Clemens Vasters, Christian Weyer and I sang Madonna’s “Like a Virgin”. At one point Christian stripped on stage and Clemens and I got on the ground on top of each other singing up to him. There are photos and videos of this someplace, will post them soon. There was a lot of love between the German RDs and their New York colleague.


After my sessions on Friday, went out sightseeing for a few hours with my Dutch pals and saw the unfinished Cathedral and climbed to the top and took some awesome overhead photos of the city. Then we went to the speakers dinner at some over the top fancy place. Sat with my Dutch pals Remi and Mark, and speakers at the table included RDs: Goskin, Billy Hollis, Joval Lowey, and MS speaker and pal Kevin Collins and the very popular and soon to be RD Kimberly Tripp. We then went out and partied like rock stars. Kimberly was worried about her reputation partying with Clemens and I until sunrise…(Kimberly, it can go either way!) I got home so late that I was able to take the subway home and have breakfast in the Hotel restaurant (when in Barcelona…)


Tomorrow I fly to Paris for the Tour de France, then home. Having been on the road for the majority of the last two months and the last 2 weeks straight, it is time for a break. Looking forward to going home.


See you all in Amsterdam next summer for TechED Europe 2004!

posted on Saturday, July 5, 2003 9:31:54 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [19] Trackback