Whats taking the most time ?

[El-d]

Hi All,

I've been working on my DB for a while and expanding on functionality with charts, calculations etc.

It is becoming slower to update my charts, much slower than I would expect. Chances are I'm probably doing something wrong/bad practice but is there a way of determining what's taking up the most time?

i.e. is query B taking 5 seconds or 10 seconds?

I figure that if I can see whats taking time then I can start trying to figure out why.

Cheers,

El-d

 

[jdraw]

It's difficult for readers to offer any suggestions since you have not described the database/application. We have no idea of your business, your database structure, your database knowledge or skills. in fact we don't know whether you have a web application or a desktop application, nor what version of Access you are using.

If you what focused advice/suggestions, I suggest you give us some relevant info.

 

[Solo712]

Hi All,

I've been working on my DB for a while and expanding on functionality with charts, calculations etc.

It is becoming slower to update my charts, much slower than I would expect. Chances are I'm probably doing something wrong/bad practice but is there a way of determining what's taking up the most time?

i.e. is query B taking 5 seconds or 10 seconds?

I figure that if I can see whats taking time then I can start trying to figure out why.

Cheers,

El-d

With jdraw's caveat, generally you need to be mindful of a number of two things: 1) beware of 'Cartesian product' (this includes unlimited SELECTs of large number of records (loading of tens of thousands rows will slow things down.) , 2) avoid recursive and aggregate functions on large data sets.

Best,
Jiri

 

[spikepl]

It is becoming slower

Common primary reason for slowing of processing is a growing data set that lacks indexing. But without any solid info all this is just pie in the sky.

 

[El-d]

Thanks for response.

It wasn't actual suggestions on what to fix (yet ) , hence no details.
Although version would help, Desktop 2013 and I'm a relative beginner to DB and scripting.

It was more a question as to there being some way of quantifying what process is taking time.

If I run my queries individually , they run pretty fast but when I'm running them via script,updating combos, updating charts etc , things seem to slow down. As several operations are occurring I can't really see if 1 in particular is taking longer than expected as I just see the final result. I figure I've got some bottle neck that if I can determine at what point in the updating process it is then I can start looking into a solution.


Is there some built in functionality to Access or would I be looking at adding script to debug.print start/end time for process/functions etc?


El-d

 

[spikepl]

There isn't and you would.

Also look up the DateDiff function.

 

[jdraw]

Slow is a relative term --how slow (acceptable/not acceptable)?
Can you show us some of the scripts involved? It's possible to put in some debug.print s with start and end times.
It could be related to data structures, lack of indexing, improper looping....

If you can show some scripts with some indication of which ones are slowing then readers may have focused advice.

 

[El-d]

Cheers.
I'll try some debug.prints with times.

El-d

 

[gemma-the-husky]

try this basic idea.

Public Declare Function Mygettickcount _
Lib "kernel32" Alias "GetTickCount" () As Long

dim timers(100) as long

timers(1) = mygettickcount
do stuff
timers(2) = mygettickcount
dostuff
timers(3) = mygettickcount

etc


this will build up a list of the times each step started, accurate to milliseconds, which may help you trace any bottleneck.

elapsed time = times(x) - timers(x-1) /1000 'seconds.