11Jun 2009 by Grant Fritchey 2 Comments

More Spatial Headaches

Spatial Data, T-SQL
I keep thinking I've got a handle on spatial data and I keep finding out I'm wrong. I rewrote the cursor that my co-worker used into a query that joined the list of test criteria onto our list of locations. I used an OUTER APPLY to more or less run a check for each of the test criteria since, except for the proximity to the locations, there's no actual relationship between the test criteria and the location data for me to join on. The query looked something like this: SELECT a .CriteriaDesc ,a.CriteriaLoc ,l.[Location].Lat AS LocationLatitude ,l.[Location].Long AS LocationLongitude ,l.LocationDesc FROM dbo.Alert AS a OUTER APPLY (SELECT x.[Location] FROM dbo.MyLocs x WHERE x.OrgID = 42 AND x.[Location].STDistance(a.AlertLocation) < 50000) AS l The cursor was taking almost a full minute to…
Read More
10Jun 2009 by Grant Fritchey 9 Comments

PASS Summit Session Preview

PASS
The Pre/Post Conference Sessions and the Spotlight Sessions for the PASS Summit 2009 have been announced. Go check it out. This is going to be a fantastic conference this year based on the topics and the people presenting (and I'm still humbled to be included with that group). I wasn't aware that they had shifted the structure of the conference so that it takes place from Tuesday-Thursday instead of Wednesday-Friday. I'm not sure I like that. I'm not sure I dislike that. In addition to the Pre/Post conferences, a special new session, and program, has been put together by Andy Warren on networking. Since networking is one of the very best reasons to attend the PASS Summit (although pure geek joy can be had in the sessions), this should be very…
Read More
09Jun 2009 by Grant Fritchey 3 Comments

Spatial Indexes and a Lack of Data

Spatial Data, T-SQL
I was feeling quite confident about my new-found abilities with spatial indexes so I did a presentation for my team, to share what I had learned. I had also been sharing with one co-worker as I developed the knowledge of spatial indexes. While I was preparing my presentation, he was preparing his. I had focused on finding a set of data that showed it's proximity to a test location and then showing how retrieving that set of data was faster because of the spatial index. He took a different approach. He took the idea of saying, here's a list of different test locations, let's see which one of our internal locations meet the proximity test. At the same time, he tried three different spatial indexes, one with high granularity, one with medium and a final…
Read More
08Jun 2009 by Grant Fritchey No Comments

Microsoft SQL Server Premier Field Engineers

SQL Server, T-SQL
Joe Sack has started a new team blog for the Microsoft SQL Server Premier Field Engineers. If you don't know who they are, you should. The first post is just introductory, but this blog is likely to become a great resource. These are the guys that MS zip lines into tough situations with the expectations that they'll improve them. I'd strongly suspect these are fellows worth listening to.
Read More
05Jun 2009 by Grant Fritchey 6 Comments

Coconuts?

Misc
I'm a bit of an old school geek, I prefer stone knives & bear skins... “So You’re On A Deserted Island With WiFi and you’re still on the clock at work.  Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island.  Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?”  Brent Ozar has passed me this interesting little question, and called me a noob…
Read More
03Jun 2009 by Grant Fritchey 7 Comments

SQL Server Central Track at Connections

Misc
This year at SQL Connections, there will be a new track, the SQL Server Central track (scroll down). I've been honored to be selected to present two different sessions on that track, MUQt or More Unecessary Query tuning (pronounced MUCK) and Scouting Out Execution Plans. I'm on a list with a bunch of speakers that... well, WOW is all I can say. I almost wish I wasn't presenting (almost) so I can just attend their sessions & learn stuff. Anyway, for those that won't or can't make the PASS Summit in Seattle, I hope I catch up with you in Las Vegas (where I'm told that anything that happens will remain in place, or something, is it a transaction rollback do you think?).
Read More
03Jun 2009 by Grant Fritchey 2 Comments

ORDER BY Speed

T-SQL
I answered a question on SSC with the comment that while an INT would perform better than a DATETIME in an ORDER BY query, assuming each has a viable index, that the difference wouldn't be all that terribly substantial. Then I realized, maybe that's not true. So I ran up a quick test, just to see. First I created a little test table with the right indexes and loaded it with data: CREATE TABLE dbo.IntDate (IntCol INT NOT NULL, DateCol DATETIME NOT NULL); CREATE INDEX ixInt ON dbo.IntDate(IntCol); CREATE INDEX ixDate ON dbo.IntDate(DateCol); SELECT TOP 10000 IDENTITY( INT,1,1 ) AS n INTO #Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2; INSERT INTO dbo.IntDate ( IntCol ,DateCol) SELECT t.n, DATEADD(dd,- t.n,GETDATE() ) FROM #Tally AS t; DROP TABLE #Tally; Then I ran these…
Read More
29May 2009 by Grant Fritchey 3 Comments

It’s a major award

Misc
I try to shy away from humor in any of my posts because what I think is funny, others think is irritating or stupid (examples: I LOATHE Adam Sandler and I thought Hudson Hawk was funny. So there. I'm completely out of the main stream of America where comedy is concerned). I wanted to post something funny about getting an upgraded listing over at SQLRockStar's Blog Rankings. I've started it several times, looking to the Oscars and trying to  imitate Sally Fields or maybe that little Italian guy, toss in a Native American being really sombre & sad, a few streakers, an old guy doing push-ups (ohe-handed, even though he cheated a bit on the form, I still can't do those, even cheating)... None of it worked. I do appreciate wit, I just…
Read More
28May 2009 by Grant Fritchey 3 Comments

Spatial Index & Performance & Brain Pain

Spatial Data, T-SQL
In my previous post, Spatial Data Hurts My Brain, I showed how a query used the spatial index, but hurt performance. Several people jumped in to try to help out. Thanks to Bob Beauchamin, Isaac Kunin, Gail Shaw, and Valerie Yakich (via Twitter). I've definately learned a bit more than I knew previously about spatial data and spatial indexes. I've posted about it before, but it bears repeating, if you're just starting out, I can't recommend Alistair Aitchison's book, Beginning Spatial with SQL Server 2008, enough. Here's where I'm at. I think I was confusing/confused/confounded/something about what to expect from a spatial index. I'm going to present two queries and two indexes and try to explain why each works well, or not together, mainly as a means for enabling my…
Read More