CODEWITHSUNDEEP
sqlvbaexcel
Tomasz Jaszczuk
Tomasz Jaszczuk

Reputation: 37

VBA with sql query - optimization

All,

I have two tabels: Cases(IdCase, Name) and Alerts(IdAlert, refIdCase, Profile). One case can have multiple alerts connected by refIdCase. I'm dipslaying the list in VBA listbox which shows case name and profiles assigned to this case.

Firstly I download 50 cases. Then for each recordset I'm finding profile names. Unfortunately it takes some time :( Is there any faster way to achieve that?

set rsCases = cn.Execute("SELECT TOP 50 * FROM Cases") Do Until rsCases.EOF Set rsProfiles = cn.Execute("SELECT DISTINCT TOP 50 Profile FROM Alert WHERE refIdCase = " & rsCases.Fields("IdCase").value & ";") rsCases.movenext

UPDATE: I believe the problem is with our connection to sql server. We are located in Poland and the server is in North America. I performed the same action from computer located in NA and it took only 4 sec, but here from Poland it takes around 45 sec.

Thank you, TJ

Upvotes: 0

Views: 464

Answers (1)

Dale M
Dale M

Reputation: 2473

The problem is that you are sending 51 requests to the database. Send 1:

set rstCases = cn.Execute("SELECT c.IdCase, c.Name, a.IdAlert, a.Profile
                           FROM   Cases c
                                  INNER JOIN
                                  (SELECT TOP 50 IdAlert, Profile
                                   FROM   Alerts
                                   ORDER BY ???) a
                                  ON c.IdCase=a.refIdCase
                           ORDER BY ???")

(Linebreaks are for clarity - don't put then in your code)

Upvotes: 1

Related Questions