Scalable “Cursors” in SQL

July 28, 2010

In T-SQL stored procedures and user-defined functions, there will be times when you need to iterate through a recordset/table, to perform some operation on each record.

That’s what cursors are made for. Except that they don’t scale at all, so you should never use them for sets of more than a few dozen records.

What you can do instead is to “build your own cursor” using a temporary table or table variable, which does scale, even to processing millions of records:

DECLARE @Count int
DECLARE @AccountID char(12)
DECLARE @UserField1 varchar(128)

CREATE TABLE #tblAccount (
     [AccountID] [char](12),
     [UserField1] [varchar](128)
)

CREATE INDEX PIndex
ON #tblAccount (AccountID)

INSERT INTO #tblAccount
SELECT AccountID
FROM sysdba.Account
ORDER BY AccountID

SELECT @Count = Count(*) FROM #tblAccount

WHILE @Count > 0
     BEGIN
          SET @UserField1 = iCount

          SELECT @AccountID = MIN(AccountID) FROM #tblAccount

          UPDATE sysdba.Account
          SET UserField1 = @UserField1
          WHERE AccountID = @AccountID

          DELETE FROM #tblAccount
          WHERE AccountID = @AccountID

          SET @Count = @Count - 1
     END

DROP TABLE #tblAccount

Deleting each record after you’ve processed it speeds up the processing by (IIRC) around an order of magnitude. Using a temporary table and putting an index on the field you’re using for the primary key has similarly positive effects. (You can’t explicitly put an index on a table variable, but you can do so implicitly just by setting a primary key for it.)

Advertisements

Outlook Class

July 5, 2010

This is a simple class wrapper for creating/displaying Outlook email items with attachments:

Option Explicit

' MailItem CreateItem Type
Const olMailItem = 0        ' = Email

' Attachment Types
Const olByValue = 1         ' = By Value
Const olByReference = 4     ' = By Reference
Const olEmbeddedItem = 5    ' = Embedded Item

Class OutlookEmail
    Private objOE
    Private objMailItem
    Private objAttachments

    Private Sub Class_Initialize
        Set objOE = CreateObject("Outlook.Application")
        Set objMailItem = objOE.CreateItem(olMailItem)
        Set objAttachments = objMailItem.Attachments
    End Sub

    Private Sub Class_Terminate
        Set objAttachments = nothing
        Set objMailItem = nothing
        Set objOE = nothing
    End Sub

    Public Sub AddAttachment(FilePath, DisplayName)
        objAttachments.Add FilePath, olByValue, 1, DisplayName
    End Sub

    Public Sub Display()
        objMailItem.Display()
    End Sub
End Class

If you just have one attachment for the email, you could use Application.BasicFunctions.QueMessage instead: It will pop up your default email client, populated with whatever parameters you’ve passed.