Blog ala Vidar
SQL, AppFrame and other cool technologies
Tag Archives: SQL
6. Nov; The Battle Against Time
Posted by on November 6, 2007
Øystein, which has a 3 months old baby at home is used to get up EARLY. I hoped he would sleep ABIT longer, but that wasn’t going to happen!
At 07:44 my phone called, and woke me up. We agreed on getting to CCIB before 9 to get to the first session; “The Next Release of Microsoft SQL Server: Overview of SQL Server 2008”.
Met a Belgish guy from Microsoft at the hotel and he told us about a much quicker way to get to the Metro. Of course, this way we had to change lanes, and of course walking around the corridores
between the lanes for HOURS. I guess you’d probably found out I’m not a fan of walking by now… Anyhow, swetty and HOT we arrived at CCIB later than expected; 09:10, so we missed the first session 😥
We sat down at the HOL, and took a couple of exercises there. DAT02, 04 and 05, to be precise. I even managed to fuck up the virtual machine, without purpose, and learned once again that not all
spanish can make themselves understandable. Anyhow, after a “fresh air”-sessions (Lithuanian guys know what I’m talking about), we (me and Nils Arne) got to the second session:
DAT309, Best Practices for Optimizing SQLCLR in SQL Server 2005 and Beyond, with Bob Beauchemin
He’s still a great speaker! I’m not going to bore you with 15 A4 pages of summart, so here’s a short summary of the session; If there exists a function for it in T-SQL do it in T-SQL. If not, do it in CLR.
Of course, you can bend this rule abit, but let’s not go into details. For those who ARE interested in details, contact me on MSN next week!
Rating: 3
After lunch, and a quick walk in the exhibition hall, we ended up in room 112:
DAT312, Programming SQL Server 2008, with Carl Perry
Not the best speaker, but he made at least me understand a couple of the new features. Filestreams, new date/time types and table valued parameters.
I really believed tvp’s was available in 2005. Probably mixing 5 and 8!
Rating: 5
I desided to skip the next session (DAT306) since this was about Compact version of SQL Server, in advantage for the exhibition hall. I really love walking around talking geek-stuff.
I bet I could do this ALL week, but I also want to go to some sessions. Luxus problems! I also had time for a couple of HOL, specifically DAT09 and 18 till the others showed up after a WPF session.
I think Øystein is very just as impressed by WPF as I’m with SQL Server!
Next session was in room 117:
DAT315, T-SQL Querying: Tips and Techniques, with Stephen Forte
This must be the most energetic speaker, EVAH. Not only was he speaking about something very interesting for me, he even got Sidsel (one of Ronny’s little helpers) who is NOT interested in computers, interested in SQL Server!
THAT’S impressive! As a wanna-be-some-day-speaker-for-TechEd I’m not only watching the slides, I’m also looking for ways to interact with the people listening, and I think he were VERY good at this!
Enough about Stephen (yeah, we’re on first-names, or at least I am!). He introduced me to a couple of new things I already knew abit about, but had not tried. Common Table Expressions which is almost the same as derived tables,
XML with XQuery and Pivot. I have to locate him this week and have a chat! Also got a question prepared for him 🙂 What’s the difference between CTE and derived tables? Anyone?
Rating: 1.5
After almost 10 hours, we found out it was best to get back to the hotel. Tried finding an ok restaurant, which I thought should be pretty easy. Turns out, it’s very difficult, unless you’re near Hard Rock Cafe. Everything else looks “shabby”.
We eventually found a place, which very very cheap and then headed back to the hotel to debriefe (read: have a beer before bed).
5. Nov; The beginning
Posted by on November 5, 2007
My day started at 8am, when Øystein called; “Nå gidde eg ikkje å gå ront aleina lenger. STÅ OPP!” (English: I don’t wanna walk around alone anymore. GET UP!)
We went down to have breakfast, egg and bacon, and then waited till 9 for the hotel-pool to open. We had packed our bikinies (shorts, for those of you who don’t like the sight of me in bikini), and were READY to take a swim, but when we got to the pool we got disappointed!
It wasn’t big enough for a walrus like me! But, we moved on. Got a shower, woke up Jan Leon and headed for CCIB. Now we knew which metro-station to get to, and where to get off!
We started with lunch (right after breakfast you might ask? Well.. To get as big and strong as me, you have to eat like a horse!) at 11:30. After lunch we had a couple of hours to kill, so we got to the HOL (Self-Paced Hands-on Labs), where they have set up computers in areas for learning different things. The SQL-area was full, so we sat down at powershell. Also VERY interesting, since I’m used to Linux (and console) before starting in Omega. Finally you can start scripting almost like in bash! I’ve missed this. The great thing about HOL is that the computers are set up in a way you can’t do anything else than you’re supposed to (working with powershell), and you’ve got a bunch of experts in all areas!
After about an hour in HOL, we desided to find the auditorium where the keynote speak with CO Vice President of the Developer devision in Microsoft; Mr. Somasegar, were starting at 14:00. He’s probably from India or something. We had some problems with understanding his accent, but understood the point of “welcome to TechEd 2007”. The auditorium was filled with geeks like ourself. I’d guess about 500-1000 geeks! That’s more than we’ve got in Ølen!
After the keynote speech, a couple of smokes and a cup of coffee, we got to the first session:
DAT308, Best Practices for Optimizing Service-Based Code in SQL Server 2005 and Beyond, with Bob Beauchemin (from SQLSkills).
Great speaker! I’d love to have a couple of words with him within this week! He were talking about the Service Broker “tool” in SQL Server, which is like a queue. You can use this to run queries asyncronous. FINALLY WE CAN GET RID OF DO ADMIN JOBS!
Rating: 2.5 (1 till 10, where 1 is best, as always 😉
After a smoke, I got “down to earth” again and we managed to get to another session:
SEP203, Introductory Walkthrough of Windows Communication Foundation (WCF) using Visual Studio 2008, with Steve Maine
He was more of a kind of nerd. Didn’t have 100% control over presentations (when error appeared, he got silent and just tried to fix everything asap zulu, instead of starting next topic.
This was not to interesting for me, as I’m more of a database-dude, but I had at least my eyes planted on the big-screen, trying to understand everything.
Rating: 7
When SEP203 were finished, we went to the opening of the exhibition hall, where we (finally) got some snacks. Got to speak with a girl in MSDN, which actually had norwegian grand-parents. Her last-name was Olsen. Same as Øystein 🙂 Got some free caps and some other stuff, and then went home. We were only three at this time; me, myself and … No. Øystein and Jan Leon. All of us thought we had sense of direction, but we were proven wrong! We walked in all directions, because each person we asked for directions pointed in different directions! After a while we found out that trying to find the way to Hard Rock Cafe was much easier than trying to find the way to the longest street in Europe! Who would have thought that?! Ended up at the hotel after a couple of hours walk, got a couple of beers and now I’m off to bed! Good night!
Tuning SQL code
Posted by on July 18, 2007
Have you ever though of tuning your car? Yeah, why not?
It makes your car run faster and use less fuel. Well, for starters it’s a bit expensive. But, tuning SQL is free, and it’s even more fun than tuning your car. Tuning your SQL means your queries run faster and use less fuel (CPU, memory etc)!
I’m at Conocophillips Indonesia at the moment upgrading to SQL Server 2005. I’ve used a couple of days to tune different queries, and at the most I got a query to run from an average of 4 minutes, to about 10 seconds, with basically the same code! Do NOT tell me that this was a temporary solution, because my solution was even easier! 🙂
When writing SQL code, keep these simple rules in mind:
Do not use sub-queries unless it’s ABSOLUTELY necessary
If you are setting several variables from the values of fields in tables/views, use one SELECT statement instead of three SET statements.
For example, do not use:
SET @Variable1 = 'Test' SET @Variable2 = (SELECT Test FROM Test1 WHERE Test3='asdf') SET @Variable3 = (SELECT Test2 FROM Test1 WHERE Test3='asdf')
Use:
SELECT TOP 1
@Variable1 = 'Test',
@Variable2 = Test,
@Variable3 = Test2
FROM Test1
WHERE Test3 = 'asdf'
You can also use SELECT instead of SET to set static values
If possible, do not use CASE or UNION (ALL)
Use EXISTS, instead of 0 < (SELECT COUNT(*) something)
Use TOP 1. Always remember that a sub-query MAY return more than one record. Your query might then fail if you’re using Field = (SELECT SubField FROM subtable).
These rules are things that every developer should keep in mind when writing SQL code.
Of course, there might be places where you’ve followed every rule, but it still takes minutes to run. Then you might want to see the execution-plan. There are a couple of major things too look for here. For example, Scans (Index Scan, Clustered Index Scan, Table Scan etc) is NOT good. Seeks are a good thing.
You might also want to see what’s really happening when you’re running your query. To turn on execution-plan in Server Management Studio, press ctrl + M and rerun your query. It will appear as a new tab beside Results and Messages.
And, as always; Profiler might be a good tool. If you let it run for a couple of hours you will discover queries which take longer time than others. For example, you can set a filter on the Reads field, for >= 50000.
Do you have any other simple roules you’d like to share with us? Please mail them to me, and I’ll publish them here!
Indenting
Posted by on May 11, 2007
Have any of you ever had problems with understanding a query because of the (lack of) indenting? I’ll bet the answer is “Yes, MANY times!”.
Today, as you’ve probably already understood, I’m going to take a look at how easy indenting is, and how much time it saves you, me, Bill Gates and all other poor creatures. Some may call me strict when it comes to indenting, but I think it’s a good thing. I’m not saying everyone should follow my standard. I just want to spread the word and make people understand.
We can start with an example from an old view in AppFrameR2 (aviw):
Before:
select d.title as "teamdoctitle", e.*, (select count(*) from stbv_calendar_events e2 where e2.copiedfromeventref=e.copiedfromeventref and e.copiedfromeventref is not null) copycount, cast(case when exists(select * from stbl_calendar_attendance where login = suser_sname() and eventref = e.primkey) then 1 else 0 end as bit) as userattend from stbv_calendar_events e (nolock) left outer join stbv_teamdoc_documents d (nolock) on d.primkey=e.teamdocref where e.primkey = @primkey
After:
SELECT D.Title AS TeamDocTitle, E.*,
(SELECT COUNT(*)
FROM stbv_Calendar_Events AS E2
WHERE E2.CopiedFromEventRef=E.CopiedFromEventRef
AND E.CopiedFromEventRef IS NOT NULL) CopyCount,
CAST(CASE WHEN EXISTS(SELECT *
FROM stbl_Calendar_Attendance
WHERE Login = SUSER_SNAME()
AND EventRef = E.PrimKey)
THEN 1
ELSE 0
END AS BIT) AS UserAttend
FROM stbv_Calendar_Events AS E WITH (NOLOCK)
LEFT OUTER JOIN stbv_TeamDoc_Documents AS D WITH (NOLOCK) ON D.PrimKey = E.TeamDocRef
WHERE E.primkey = @PrimKey
As you see, it’s difficult to see what’s SQL and what’s not. It is also very hard to know where the subqueries end.
Imagine you have a MUCH longer query. Sviw_MSAccessCR2_MyMenuObjects for instance. In this view you have alot of case, subqueries (with joins) and both inner and outer joins. Try reading that without indenting, syntax highlight and uppercasing SQL-commands! That’s not even possible! About a year ago we had some performance issues with this view. I remember my thoughts:
– ”EHM! Where do I start?!”
It was about 13 kilometers longer than the one today, it had alot of unions and other ”fancy” stuff. I think I used almost a day only ”getting to know” (indenting/casing) the view! If possible, take a look at it, remove all indenting and change all text to lowercase (mark it and press ctrl + shift + L). Copy it to notepad and try to figure out where the subqueries end, where each case ends etc. It’s almost impossible.
Very often I get questions like ”Could you help me with a SQL-query?” on mail and/or MSN. To understand the logic, I always start with indenting (if it’s not already indented). I’ll bet $100 that most of these questions wouldn’t be asked if the person already had indented the query, because it’s so much easier to find the issue with, than without.
It takes me more time to write a query WITHOUT indenting and casing than WITH, because I’m so used to this method
.. and it’s MUCH more difficult to read. My advice: Use indenting and uppercase on SQL-commands. If it’s ”my” standard or some improved (if that’s possible 😉 standard does not matter. ANYTHING is easier to read than one-line-plain-sql-queries!
Blog ala Vidar 