BEST PRACTICES -> SQL with DAO

[Irish_Griffin]

Hi guys,
I've discovered excel can do SQL !!! It feels like I'm the primitive caveman discovering fire

Well lets not talk about how much time I've wasted writing code that would have been soooooooo much easier in SQL .... Instead, lets talk about BEST PRACTICES using SQL in EXCEL

I would like to make this thread a good reference for others ( not much online surprisingly ). I'll make this a sweet thread if I can get the guru's help.

So first and foremost:
#1 - Is DAO the right choice ?

Remember to take learning one bite at a time:

 

[Craig Ottley]

That's a pretty big increase! If you need to speed it up even more use PL/SQL. all the processing will get done on the server and not locally.

 

[Blade Hunter]

That's a pretty big increase! If you need to speed it up even more use PL/SQL. all the processing will get done on the server and not locally.

I am using PL

Private Sub GetData()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
    Debug.Print "Database call for: " & DataSheetName(Y) & " started at: " & Now
    Set Data = Sheets(DataSheetName(Y))
    Data.Select
    Cells.ClearContents
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & _
    "USER ID=" & UID & ";PASSWORD=" & PWD
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    sqlText = sqlCommand(Y)
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 0 To numCol(Y)
        Data.Cells(1, X + 1) = RS.Fields(X).Name
    Next
    If RS.RecordCount < rows.count Then
        Data.Range("A2").CopyFromRecordset RS
    Else
        Do While Not RS.EOF
           Row = Row + 1
           For Findex = 0 To RS.Fields.Count - 1
             If Row >= 65510 Then
                Exit For
             End If
             Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
           Next Findex
           RS.MoveNext
        Loop
    End If
    Debug.Print "Database call for: " & DataSheetName(Y) & " ended at: " & Now
End Sub

Obviously I can't post the actual SQL though

 

[Craig Ottley]

Use the Recordset Object, you can force the query to run on the server, if you know you're never going to get more than 65k rows you can change the cursor type to adOpenForwardOnly (Fastest method). Lock Type can be important if you're going to edit a recordset as well, but by default it gets set to read only...

Set RS= New ADODB.Recordset
   With RS
      .CursorLocation = adUseServer
      .CursorType = adOpenKeyset
      .LockType = adLockReadOnly
      .Open Cmd
   End With

The Joys of ADODB hey, i'm just waiting for .NET to makes it's way in. Data Adapters are so much better.

 

[Blade Hunter]

you can force the query to run on the server.

Does it not by default? Where does it run otherwise? When it comes to this stuff I know enough to get by but I am keen for further performance tuning tips.

My next performance increase will be from rewriting the matching routine. There are many cases where we will get sales reported on 6 products and they are the same but they have been spelled slightly differently. I currently scan through and do a fuzzy check on each cell for each of the top 50 cells and as you can imagine, 20,000 rows of data multiplied by 50 check routines = 1,000,000 checks to be performed, this is done on multiple sheets too so it grows exponentially.

I have had an idea which is to add a helper column with just the first letter of the column, sort the column based on this and only check that subset of data where the first letter is an exact match. It shouldn't be too hard to do this and I reckon there will be a big performance boost.

 

[Craig Ottley]

Does it not by default? Where does it run otherwise?.

By Default it uses the client machine, so you'll have different elasped time executions on different machines.

 

[Norie]

Blade

I don't think that SQL would work for the rows thing, I'm not aware of a Rownum property/variable/whatever built in.

Perhaps you could use DCount, but I would first start with the obvious question.

Why would you be exporting 65K+ records in the first place?

 

[Blade Hunter]

I have a weekly sales report which reports sales of tracks on iTunes for our record label. That is over 65,000 unique tracks when it comes to the SSR section.

Unfortunately the data is not as clean as we would like so I actually need to poll through the SSR and find any like matches then add them together. The report now takes 20 mins all up to run. If I can increase the performance of the matching (Which is what I am currently working on) then I will be a happy man, The DB calls are down to 90 seconds so I am happy with that .

 

[Norie]

Blade

I don't know exactly what type of data you are dealing with, but why not try 'cleanng' it up in Access before you export to Excel?

Access has most(if not all) the text functions that Excel has which you could perhaps use them.

It also has various functions that you could perhaps use to 'poll' the data.

Really depends what you actually want to do.

 

[Blade Hunter]

Blade

I don't know exactly what type of data you are dealing with, but why not try 'cleanng' it up in Access before you export to Excel?

Access has most(if not all) the text functions that Excel has which you could perhaps use them.

It also has various functions that you could perhaps use to 'poll' the data.

Really depends what you actually want to do.

I don't have access installed, I "could" install it but it seems like overkill for 1 weekly report. The data is read from an Oracle DB straight in to excel then the data is massaged.

 

[Norie]

Oops, my bad - I assumed the data was coming from Access.