Blog ala Vidar

SQL, AppFrame and other cool technologies

Monthly Archives: July 2008

FTP & FTPINTERNAL

This is a VERY IMPORTANT message for those of you that uses FTP and FTPINTERNAL.omega.no

All files OLDER THAN ONE WEEK will be deleted automatically. We are going to start this job this weekend. If there is anything you need on this server, please download it or notify me to take backup of it.

Files older than one week WILL BE deleted!

TechEd Barcelona 2008

Registration for this years TechEd is now opened! This is available from Advantage+. It’s only 6 registered participants now.

I recommend this VERY much to all system engineers. It is very interesting, motivating and of course fun! I’ve talked to Rune Grothaug (Microsoft MSDN-dude) and he’s already booked Hard Rock Cafe 😉

My God (Bob) is also coming!

 

PS!!! I’m going to order tickets etc 30th July, so please sign up before that!

http://test.teamdocuments.com/Application/BenefitAccount/Default.aspx

Query Optimization

When doing query optimization, there is a couple of things to remember:

NEVER USE “OR”!
This KILLS performance! Instead of using OR, use UNION (ALL) or IN

UNION vs UNION ALL
The performance difference between these are big. UNION first groups and then orders. UNION ALL just adds the resultsets together

EXISTS vs IN
In many cases, you can use:

SELECT *
	FROM table1
	WHERE Login IN (SELECT Login
				FROM table2)

instead of using:

SELECT *
	FROM table1
	WHERE EXISTS (SELECT *
			FROM table2
			WHERE table2.Login = table1.Login)

Subselects vs JOIN
In some cases, like in the queryoptimizing webcast, subselects are MUCH quicker than using JOIN. This is not the case in all queries, so test both!

WITH (NOLOCK)
When you use NOLOCK, you read uncommited data from the table. This means that there MIGHT be data that are not written to the table yet. In most of our cases this is not a problem.

DO NOT USE *
… unless you need it. When using *, you can’t use indexes to select fields. Using a * forces the execution plan to select from the table.

Indexes and Statistics
Always make good indexes! Make sure indexes and statistics are rebuild when they need to! I’ll post an input here later on about indexes and statistics.

TechNet and MSDN Live

Microsoft have published the agenda, and opened up for registration for both TechNet and MSDN Live. They’re visiting Stavanger, Bergen, Trondheim and Oslo.

Here’s the agenda for Stavanger, 4. Sep:
TechNet
MSDN

 

BTW!!! NNUG Haugesund were mentioned in a mail from the MSDN Flash newsletter:

Ny NNUG-avdeling med knallstart
Norwegian .NET User Group har fått et nytt skudd på stammen.
NNUG Haugesund er en splitter ny avdeling av Norges største brukergruppe.
Vidar Nordnes er leder for avdelingen, som hadde hele 36 medlemmer på sitt aller første møte. Vi gratulerer!

Webcasts

I’ve been asked by Johnny to have a webcast in the near future (probably Wednesday). It will be about tips and tricks in SQL Server. You got any suggestions to topics, something you’d like me to show you that you think might be valuable for others? Please let me know!

This is my list so far: (random order)

  • Backup and restore, also with point in time-restore
  • Shrinking log-files
  • Indenting
  • Query Optimizing
  • Looping recordsets
  • Making backup of a-objects
  • DT and CTE (Derived Tables and Common Table Expressions)
  • DMF and DMV (Dynamic Management Functions and Views)

Busy times!

I’ve been a bit busy lately. We’ve got a lot going on in Ølen nowadays; CR3, web3, PimsR3 +++

NDC 2008
Me, Nils Arne, Jørn and Arild were at NDC (Norwegian Developers Conference) two weeks ago. I liked it very much. There were not too many interesting sessions, but we had “private” sessions (no, nothing like that!) with Anders Føyen about Windows Live and we “hang out” with Jon Jahren.. I was at a couple of “Agile Development” sessions, which is pretty interesting. There’s a couple of leading theories. Lean and Scrum. I went to both.

Lean, with Mary Poppendieck (cool name btw..) were very interesting. The name of the session was “Thrashing” which basically is getting rid of the queue. We all know that trying to make the computer do 100 things at once just slows it down, so why are we trying to do 100 things at once ourselves? Her point were to get rid of the queue, and doing work by priority. For example setting up a list at the beginning of the month with the customer, then working on that list from top to bottom WITHOUT GETTING DISTURBED by the customer. At the end of the month, you’ll have all the issues and features ready, and ready to start with your new list. I think this is a very interesting thought. Maybe we in Omega should think more about this? I know I’m using VERY much time “overloading”.

You can listen to an interview by Scott Hanselman (cool guy, who had the keynote at NDC) with Mary and Tom Poppendieck here.

Scrum, with Ken Schwaber, I didn’t totally get.. What I did understand though were that one of the key points of Scrum is to help developers (or other “workers”) to think themselves, and to make decisions themselves. Go to youtube and you’ll find some videos about it.

Listen to Scott Hanselman’s interview with Ken Schwaber here.

AppFrameR3
In the beginning of September, release 2 of AppFrameR3 will be released. This includes the first version of web3, many fixes and new features to CR3 and performance (and of course bug-) fixes on the database.

Pims_R3
We’ve been working with Pims Basis the last month. This is meant to be a “80% solution”, where you can take the modules you need to the customer, and customize them to their needs, instead of starting from scratch every time. The modules that are under development (planned release is end of July) are: Contracts, Cost Management, Doc Ctrl, Finance, Monthly Reporting, Personnel, Procurement and Uncertainty Management.

SQL Server 2008
I’ve been using SQL Server 2008 about a month now. I LOVE IT! There’s still bugs, but nothing critical. At least not that I’ve found. Me and Arild have reported 7 new bugs and 5 suggestions to SQL Connect. I thought I had written about the new features before in my blog, but my goldfish memory lied once again… Anyhow, here it is:

  • Debugging! Yeah, just like in visual studio, you can step through your code, watch variables+++.
  • Intellisence, like in VS.
  • Policy Management. With this you can enforce for example table names to start with atbl or stbl.
  • Resource Governor. This helps you to make sure no one’s stealing resources. For example you can set a job (that’s running every cutoff) to NOT use more than 10% of your CPU and RAM, so your users won’t feel any performance drops.
  • Powershell support.
  • Data compression.
  • Transparent Data Encryption. You don’t have to change any of your applications, but the mdf file and .bak is encrypted. Therefore, no one can steal the database, or for example open the mdf-file in notepad to extract classified data.
  • Table Valued Parameters. Pretty cool with stored procedures or UDFs.
  • MERGE statement. This combines INSERT, UPDATE and DELETE in one statement
  • New datatypes.
    • date, time, datetime2 (more accurate than datetime), datetimeoffset (timezone “aware” datetime)
    • hierarchyid (with methods on the field.. getparent, getchildren, getsiblings etc)
    • spatial (geometric, geographic)
    • sparce columns, typically used for tables with many columns that usually are NULL

I’ve gone through the list of deprecated features to see if there’s anything that we’re using. It says they’ll remove SETUSER, but it’s still working on RC0, so I’m unsure. Except that, I can’t find any features that we use