Measuring performance of each part of large query

[Stormin]

I have a db that takes a lot of 'dirty' imports from different systems. I have a lot of queries and reference tables that comb through the data, clean it up, fill in missing data, run calculations, and transform it into a unified format.

Some of the 'final' select queries run quite slow. For example, one brand that needs a lot more cleanup takes twice as long as another brand that doesn't have as big of a chain of queries.

Is there a way to programmatically run a query but split it up into its component parts (down the query chain) and measure the time each part takes to run? I've done as much optimisation as I can in the areas I can think of, and unfortunately it is just simply not practical to check and run each part manually.

Let me know your thoughts. Cheers.



Edit: even cycling through to run and time every single select query in the db could help, as I can set it off and leave it for however many hours it takes. Hmmm...

 

[jdraw]

There is no way that I am aware of to break a query into component parts. However, if you are running query1, query2...queryx, you can time individual queries.

Good luck with your project.

 

[Ranman256]

I have a tLog table,
In vb, I grab the start time, run the query, grab the end time, then post the results....
EVENT, RUNDATE, MIN
QsAddTax, 1/1/2017 11:13, 12

Sub btnRun_click()
  Dim vStart,vEnd
Docmd.setWarnings false

  VStart = now()
  Docmd.openQuery "qsAddTax"
  VEnd = now()
  PostLog "qsAddTax",vStart, DateDiff("n",vStart,vEnd)

  VStart = now()
  Docmd.openQuery "quUpdClients"
  VEnd = now()
  PostLog "quUpdClients",vStart, DateDiff("n",vStart,vEnd)

End sub

Sub PostLog(pvEvent,pvStart, pvElapse)
  SSql = "insert into tLog (EVENT, RUNDATE, MIN) values ('" & pvEvent & "',#" & pvStart & "#," & pvElapse & ")"
  Docmd.runSql sSql
End sub

 

[Stormin]

Thanks. I guess I was thinking if there was an 'order of operation' like you can find inside .xlsx files for calculation chains.

I that case I will brute force it and time every query individually, then draw a flow chart for my output queries and optimise from there.

Thanks for code Ranman. I promise to use and abuse it