[SQLServer] Indexing and Scalability Tips

Recently I spent a lot of time messing about with indexes. Not because I like it - but because there was a little bit of a fuckup at work with scalability issues on a DB. After messing around with SQLProfiler (real life-saver in this case) I gained a bit more of understanding about indexes as I never got the chance to do before.

What I learned about indexes and scalability can be summarized in 2 points:

1) You don't wanna have a clustered index on the primary key on a table once it gets huge (just make it nonclustered) if you plan on running loads of inserts

This is not a problem when the table is small - and since it's good for fast selects everyone's cool with it. Once the table gets populated with LOADS of records or it gets HUGE in size this WILL KILL YOU on any insert or update operation.

Just to give a few figures:

- A clustered index on a table with 45 million records was killing my SQL server - every insert from code was resulting in nasty timeouts (it was taking more than the default 30 secs for standard inserts - Increasing your timeout tolerance won't solve your scalability problems!)

- A clustered index on a HUGE table (15 gigs) with a few million records was slowing down inserts to the point it was taking 15 times as much as it was taking on an empty db (we're talking mSecs but on big batches it's gonna kill you)

2) You wanna have a non-clustered index for frequently selected fields on any table you plan on running extensive selects on (add the fields you select often to a single index)

- On a medium size table (40k records) select time on a given set of fields was doubling in time compared to the same table with a lot less data before adding a triple-index on the set of given fields.

Following the points above I was able to solve my scalability issues (the process I was running is now taking the same time on a huge db compared to an empty one). Obviosuly these rules are not applicable in every case - but understanding the points above might help people when 'scalability' hits the fan.

[VS2008] Theming STILL sucks!

This is no breaking news - but I feel like ranting.

I just noticed theming STILL sucks in Visual Studio 2008.

If you apply your theme to your webSite/webApp from web.config with:

<system.web>
<pages theme="myKickAssTheme">
</pages></system.web>

you STILL don't get to see your CSS stylesheet(s) applied in design view - also, as part of the same problem, if you set the CssClass property of your controls it doesn't always 'see' CSS classes defined in your theme (sometimes this seems to work).

Done ranting.

[T-SQL] How to REPLACE on TEXT field

The REPLACE T-SQL function can be used only with VARCHAR - if you try to run REPLACE on a TEXT field you'll notice you're pretty much screwed (and that's probably how you ended up on this page in the first place).

There are ridiculously complicated ways to overcome this but the easieast solution I was able to find around is the following:

UPDATE myTable
SET myField = REPLACE(SUBSTRING(myField, 1, DATALENGTH(myField)), 'string2replace', 'replacementString')

SUBSTRING is returning VARCHAR so this way it works like charme.

I HATE SQL but I am getting to know more than I would like about it.

UPDATE:

as pointed out by people in the comments, the above will truncate to 8000 characters. This wasn't a concern for me at all because I knew my strings where pretty short. If you're worried about truncation you can use a CAST (check comments for more details).

[VS] Collapse all projects in a solution + How to setup Macro

This is not about increasing productivity - it's about being awesome.

How many times have you been collapsing manually all the projects in your solution ranting about the fact there's no option to do that automagically? Loadza times is the answer - unless you only work on small projects (which is not a crime btw). 

Anyway - after swearing at the screen a number of times I decided it was about time to do something about it and I googled it.

I found loads of awesome VS Macros to do what I was looking for but I hadn't a clue about how to create a VS Macro so I thought I'd share the whole thing.

First thing you need to go to Visual Studio Tools --> Macros --> Macro Explorer. Once you got that right click on MyMacros and create a new module (I called it CollapseAll).

Now edit the new module (double-click on it) erase whatever is in there and paste this stuff into it (if you want you can even try to understand what it does - but it's optional):

'Awesome Macros ripped-off 
'from http://www.milkcarton.com/blog/

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics

Public Module CollapseAll

Public Sub CollapseTopLevel()
   DTE.Windows.Item(Constants.vsWindowKindSolutionExplorer).Activate()

   Dim solutionWindow As EnvDTE.Window = DTE.ActiveWindow
   solutionWindow.Visible = False
   Dim solution As UIHierarchyItem = GetSolution()

   CollapseHierarchy(solution.UIHierarchyItems, True, True)
   solutionWindow.Visible = True
   DTE.StatusBar.Clear()
   DTE.StatusBar.Progress(False)
End Sub

Private Sub CollapseHierarchy(ByRef items As UIHierarchyItems, ByVal IsRoot As Boolean, ByVal OnlyCollapseRootLevel As Boolean)
   For i As Int32 = 1 To items.Count
       If IsRoot Then DTE.StatusBar.Progress(True, "Collapsing", i, items.Count)
       If (items.Item(i).UIHierarchyItems.Count > 0 And Not OnlyCollapseRootLevel) Then
           DTE.StatusBar.Text = "Collapsing " & items.Item(i).Name
           CollapseHierarchy(items.Item(i).UIHierarchyItems, False, False)
       End If
       items.Item(i).UIHierarchyItems.Expanded = False
   Next
End Sub

Private Function GetSolution() As UIHierarchyItem
   Dim win As Window = DTE.Windows.Item(Constants.vsWindowKindSolutionExplorer)
   Dim uih As UIHierarchy = win.Object
   GetSolution = uih.UIHierarchyItems.Item(1)
End Function

End Module

Now, the hard work is done - You'll see your CollapseTopLevel as a child of the CollapseAll module in you Macro Explorer and you can run it double clicking on it but we all know that sucks so the cool thing to do now is to assign an hotkey chord/combination to our kick-ass macro.

To do this you need to go to Tools-->Options-->Environment-->Keyboard. Pick your macro from the listBox with all the default VS stuff (remember it will be there like MyMacros.CollapseAll.CollapseTopLevel) and then assign a hotkey combination or chord to it (I used CTRL+K, CTRL+Y since it's not used by VS) and save. 

That's all.

P.S. I'd really like to thank Dan, who wrote the snippets I brutally ripped-off. The original article is awesome and there's plenty of nitty-gritty cut and paste potential there for developers in need.

[C++] What's the difference between BSTR and _bstr_t?

I stumbled upon this recently while maintaining some old code where BSTR and _bstr_t were being used interchangeably. This was generating a nasty (to say the least) bug which you can read all about here.

The main thing to understand here is that BSTR is a dumb pointer while _bstr_t is a class that wraps BSTR. The difference between the two is pretty much the same between char* and std::string.

_bstr_t also works like a smart pointer, so it will free the allocated memory when the variable is destroyed or goes out of scope. Another thing to say is _bstr_t has reference counting, increased every time you pass the _bstr_t variable by value (avoiding unnecessary copy) and decrement when it is no longer in use. Whenever all references are destroyed, the allocated memory for the string is freed up.

More about _bstr_t here, more about BSTR here.

That's it folks - lousy as ever.