0

The code below runs perfectly fine for me, but as I increase the number of rows from hundreds to 70k, the performance degrades to a point where it is not usable. Here is my code.

'Find the last non-blank cell in column A(1)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & LastRow)

    For Each cell In rng
        myRange = myRange & "'" & cell.Value & "'" & ","
    Next cell

' remove last comma from concatenated string
myRange = Left(myRange, Len(myRange) - 1)

'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
StrQuery = "SELECT * FROM myTable Where BB_ID IN (" & myRange & ") AND myDate < '12/11/2019';"

'Performs the actual query
rst.Open StrQuery, cnn
'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
Worksheets("Sheet1").Range("A2").CopyFromRecordset rst

How can I take a list of IDs in ColumnA and VERY QUICKLY convert it to comma-delimited string so I can feed it into a SQL query? Thanks.

braX
  • 11,506
  • 5
  • 20
  • 33
ASH
  • 20,759
  • 19
  • 87
  • 200
  • Arrays and possibly parameters but I'm suspicious any array/comma joined values you dump into that query that appears to be running from access is going to have fairly poor performance anyways. – Mike Dec 11 '19 at 23:21
  • its not possible to send more than 2000 parameters at a time imho. and also not a good idea. Can't you just fetch the whole table, and filter it in your vba? you can loop over the full table rs and just pick the rows you want, to copy to your worksheet. To your Question, you can speed up your string concat if you put it like this `myRange & ("'" & cell.Value & "'" & ",")` then it will only concat one time, instead of 4 times. You can also split your queries into chunks and fetch max 2000 rows at a time, then you wont have any problem with your string concats... – Charles Dec 11 '19 at 23:26
  • See the first listed answer here for how to make the range -> array ->Join happen https://stackoverflow.com/questions/8482759/simple-vba-array-join-not-working – Mike Dec 11 '19 at 23:26
  • Well, the performance won't be lightning fast, I'm sure. For now, I'm just trying to do away with the looping process. I tried this: arTesting = Range("A1:A" & LastRow).Value. That's super-fast, but when I try to concatenate commas ar the end of each variable, I get an error. So, this doesn't work: arTesting = Range("A1:A" & LastRow).Value & "," – ASH Dec 11 '19 at 23:28
  • Yeah, Charles, that's pretty fast, at least on 20k records. I'll probably need to up-it to 70k sometime soon. I'm pretty confident that VBA will fall down long before then. How can I run this in batches of, let's say 2k-4k IDs at a time? I think that's the best path forward. – ASH Dec 11 '19 at 23:41
  • What about using only SQL with a subquery?`Select * FROM myTable WHERE BB_ID IN (Select id FROM [Sheet1$A1:A" & LastRow & "]) AND myDate < '12/11/2019'` That should work in SQL-Server too. – ComputerVersteher Dec 12 '19 at 00:27

1 Answers1

2
Dim s
s = "'" & Join(Application.Transpose(Range("A1:A2000").Value), "','") & "'"

Application.Transpose has a limit of about 65k items, but your SQL likely has a much lower limit...

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Awesome! That's fast enough for me Tim! How can I loop through my list, from beginning to end, in chunks of 500 rows at a time? I think doing this in batches of 500 IDs at a time is safe enough. Even 2000 is probably too much for Excel to handle. Finally, how do I handle less than 500 IDs. I'm sure the last batch will not have exactly that number. I just tested SQL, and if you enter '' as an ID, you get an error. – ASH Dec 12 '19 at 00:26