VBA with sql query - optimization

Question

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

Interesting solution - what is the business requirement being addressed by this (non-performant) solution? — Pieter Geerkens
– Pieter Geerkens, Commented Sep 20, 2014 at 22:02
1) Where are the tables located -- in an Access database, or in some other RDBMS? Do you have some kind of index on the Alerts table? 2) Do you need exactly 50 records from the subquery? If it returns more is that also acceptable? — user20416
– user20416, Commented Sep 20, 2014 at 22:05
I have created this macro in outlook. We have monitoring team which reacts on alerts. This macro help them (creating escalation email based on alert body, creating ticket in external system etc.). Additionally we would like to keep history of actions in SQL. Whenever user runs macro, this code checks history and displays cases with assigned alerts. It takes about 1 sec to search profiles for each case. About 1 minute to display history. It is a little bit to long :/ — Tomasz Jaszczuk
– Tomasz Jaszczuk, Commented Sep 20, 2014 at 22:13
Zev: Database is located on SQL server. It can return more, no problem with that. I have created index now, but it won't help or i'm doing it wrong: CREATE INDEX refIdCase_index ON Alert (refIdCase) — Tomasz Jaszczuk
– Tomasz Jaszczuk, Commented Sep 20, 2014 at 22:42
On a side not it might be worth pointing out that using TOP without an ORDER BY clause means the rows returned will be random which might not be what you actually want. — jpw
– jpw, Commented Sep 21, 2014 at 0:19

Dale M · Accepted Answer · 2014-09-23 04:56:41Z

1

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)

answered Sep 23, 2014 at 4:56

Dale M

2,4851 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA with sql query - optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related