Parameter Range In SQL VBA Code

[geekette]

Hi,

I have written the code below (fudged together!) to firstly look at a column in my excel sheet, fill the column into an array/string. Then run an SQL query using the array/string to pull through relevant records into excel.

The query works, however, it can be a long list of criteria that is in my excel column and it is really slow to run it or if there are too many in the list it times out.

Am just looking for some advice to see if there is a better way to do this or structure my code to make it run more efficiently???



Sub TestData()

Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim query As String
Dim row As Integer
Dim Rng As Range
Dim cnt As Long
Dim p As Long, e As Long
Dim Param As String

Sheets("Data_Selection_1").Range("A3:H65536").ClearContents

j = Sheets("Date_&_Skill_Selections").Range("D1000").End(xlUp).row
For i = 2 To j
cnt = cnt + 1
If cnt = 1 Then
Param = Sheets("Date_&_Skill_Selections").Cells(i, 4)
Else: Param = Param & "','" & Sheets("Date_&_Skill_Selections").Cells(i, 4)
End If

Next i
Param = "'" & Param & "'" & Sheets("Date_&_Skill_Selections").Cells(i, 4)
'MsgBox Param

Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

cs = "DRIVER=SQL Server;"
cs = cs & "DATABASE=TEST;"
cs = cs & "SERVER=1.223.56.79,9101"

conn.Open cs, "USERNAME", "PASSWORD"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

query = " select StartTime, sum(ACD_Calls), sum(ABN_Calls_ALL), sum(Calls_Offered), Skill, Date "
query = query & "from reporting.dbo.REPORT_AVAYA_SKILL "
query = query & "where convert(char(12),Date)+ convert(varchar(12),Skill) IN (" & Param & ")"
query = query & "group by StartTime, ACD_Calls, ABN_Calls_ALL, Calls_Offered, Skill, Date "

rs.Open query, conn

row = Sheets("Data_Selection_1").Range("A1").End(xlDown).row + 1
Sheets("Data_Selection_1").Cells(row, 1).CopyFromRecordset rs

rs.Close

Set rs = Nothing
Set conn = Nothing
Set cn = Nothing

End Sub

 

[Norie]

Is it definitely the query that's causing the problem?

You mentioned parameters but as far as I can see you aren't actually using any.

Isn't the 'paramater' bit just created the values for the IN operator in the WHERE clause?

How long does the query, or a similar query, take to run in SQL Server itself when you execute it manually?

The reason I'm asking is because I'm wondering if it could be possible that the GROUP BY clause is slowing things down somehow.

Have you considered just using a 'straight' SELECT query to get the data without summing or grouping?

You could then do that in Excel.

Or are there too many records for that?

PS Before I saw all the code I was going to suggest using IN, I thought you might have been running an individual query for each value from Excel.

 

[geekette]

Hi Norie,

It's probably my terminology! Yes my parameter bit is the part that fills the IN statement.

Normally I use MS Query to return records using the parameter options in there. But unless I create over 300+ [] parameters and link them to specific cells in excel I looked for an alternative solution.

Have just tried it by removing the sums and the group by statement and that has made it run a lot quicker (is still a couple of mins).

Originally I had reasearched to somehow include my excel column and join it as a table in my SQL statement but don't think that is something that can be done?

Thank you...

 

[Richard Schollar]

Have you tried running the SQL directly in SQL Server Management Studio? If it's slow there too, you will need to rethink how you query the data rather than tweaking with the VBA.

 

[geekette]

Hi Richard,

I have tested my query in SQL first and it runs ok...but I have to run the sql in Excel as we have restrictive access rights to SQL and other users will be running the code that won't have relevant permissions....

 

[Norie]

geekette

When you ran the query in SSMS did it have as many values for the IN clause as it does when run from VBA?

You could the string 'query' and try running it in SSMS.

It could be possible to run the query using the data from Excel as a table but it might be a bit complicated.

I've done it before but only with very simple criteria.

The code itself wasn't particularly complicated but getting things right was tricky as you had to specify drivers, sources etc.

That might have just been me though.

 

[geekette]

I've just run it manually in SSMS with 750 items in my IN statement....it took 4 mins 30 to run! So the way I am querying it obviously an issue...

I'm probably trying to be too clever.

Basically I have built a calendar in excel so a user can easily select up to 365 dates. They can then choose a selection of skills to look at. I then need to run my query to return all records that match date and skill.

So to get it to work I've concatenated the date selection with the number of skills to create my range to include in the parameter part (e.g date 20 sep 2010 skill 10, date 20 sep 2010 skill 11, date 21 sep 2010 skill 10 etc...) This was why I originally thought about creating and joining the excel list into a table in sql so it can easily retreive records based on the varying amount of selections the user may make.

 

[Richard Schollar]

I think the Converts will slow down the SQL - so you should lookto trying to exclude them if at all possible.

 

[geekette]

Wow!

I have removed the converts (and placed them in the original SP that creates the reporting table I am querying).

The code now runs in literally seconds.....even when I have 7,000 different selections in my IN statement.

Norie and Richard thank you both for you help....it's so appreciated and saved me so much time :O)