Blog ala Vidar
SQL, AppFrame and other cool technologies
Category Archives: Published
Query Optimization
Posted by on July 10, 2008
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
Posted by on July 10, 2008
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
Posted by on July 3, 2008
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!
Posted by on July 3, 2008
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
TVS2Public
Posted by on June 10, 2008
Are you excited to get starting with CR3, but is tied up with old CR1 applications or similar? Now I’ve got the solution for you 🙂 You can start developing your own personal applications on a new server: TVS2PUBLIC
I’ve set up a database, and a service-site. When web3 is ready, you can use this server to host your personal sites. For example: http://vidar.webmail.no
The only thing you need to do is start developing! 😀
If you are excited to start, just contact me and you’ll be ready to start within minutes!
Various
Posted by on June 7, 2008
Jon Jahren visited Omega at Wednesday. After that, we were at Hatteland to have a look at their server-park (a bit bigger than ours :-P), and then we had our first NNUG meeting.
Torgeir have written about the biggest points, so I’m not going to bore you with the same information.
We’ve started using windows server 2008 and IIS7 in Omega. First server out was AFS14, which is running service.marketmaker.no (the web-service site for CR3).
TVS1 (virtual host-server, for virtual training servers) is now also running 2008, with Hyper-V, instead of VMWare.
TVS2WEB4 hosts test.teamdocuments.com, and is running 2008 with IIS7. Seems to work very good 🙂
Now we’ve started converting SQL servers to (windows) 2008 too. The server that are going to run the Pims-databases (CMS, Cost, DC etc) is 2008.
I think this is a good progress, and we’re converting servers every week.
SQL Server 2008 RC0 is out, so I’ve installed that on TVS2SQL1 for testing. When the release comes (31. July), we’re going to convert our SQL Servers to 2008. I’m looking forward to that! 😀
News from Ølen
Posted by on May 29, 2008
Now, almost a month after the release of R3 I think it’s time for news about the area I’m responsible for: the database
First, something even more important:
- I’ve been elected to the leader of NNUG (Norwegian .NET User Group) Haugesund. Our first meeting is June 4th. Microsoft sends us their true database guru, Jon Jahren 🙂
- Microsoft says that they will release a RC (release candidate) of SQL Server 2008 in the second quarter of 2008. That means, within a month! REALLY looking forward to it!
R3 Database
There’s mainly three differences between R2 and R3 databases.
- R3 is much more clean (deleted MANY old SPs, views and tables, for example MSAccess, MSAccessCR2)
- New namespaces
- DDL trigger
New namespaces
- Database – Used by the DDL-trigger
- Security – Only stored procedures. Users_Create etc
- WinClient – Used by CR3 and CR3 Reports
DDL Trigger
Data Definition Language. This triggers on CREATE, ALTER, DROP and similar. We log everything to the stbl_Database_% tables to have version-control of objects, keep track of who’s updated what etc.
Pizza and Learning
Posted by on January 9, 2008
On the 29th of January, we’re planning to have Pizza & Learning in Ølen. The topic is LINUX!
Everyone that’s interested should come to Ølen, and join us 🙂
SQL Query tips
Posted by on January 9, 2008
Happy new year, guys!
I’ve been very busy lately. In Ølen we’re making a new framework, based on winforms. The first thing we did was DROP TABLE stbl_MSAccess%. So long, suckers! Now there’s no turning back! (PS!! We will still support access in AppFrameR2). So, what are we doing? Well, we’re making a framework, based on .NET 2.0 and controls from DevExpress. We have already started making some forms and it’s starting to look pretty nice! Today I did a quick test. Made a simple form with a couple of subforms. Took me not more than 15 minutes! I hope everyone’s looking forward to this new framework 🙂
I ran into a little problem when I was making a form today. In one of the subforms we need to list all mails from/to one address, with from, to, cc, subject and the body. The problem was that the users want’s CCs in ONE column, comma separated, and it’s stored as one record pr address in the table. How did I solve this? Of course, with a little help from Torgeir and some XML-queries
SELECT M.FromEMail, M.Subject, MC.TextHTML, M.PrimKey,
(SELECT (SELECT [data()] = EMail + ','
FROM stbl_Mail_MessagesRecipients
WHERE MessageRef=M.PrimKey
AND Type='To'
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS ToEMail,
(SELECT (SELECT [data()] = EMail + ','
FROM stbl_Mail_MessagesRecipients
WHERE MessageRef=M.PrimKey
AND Type='Cc'
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS CCEMail
FROM stbl_Mail_Messages AS M
INNER JOIN stbl_Mail_MessagesContent AS MC ON M.PrimKey = MC.MessageRef
Blog ala Vidar 