a question about query speed....

[CHAOSinACT]

this is a complex situation, so bare with me here...

we have a project management database, split in 2 (tables live on serve, front end on users pc's). we've decided to link this to the company MYOB info. there are 4 myob company files, we need to tally them ALL. direct linking is slow and unnecessary (updates to data every 24 hours do) so we delete and RE-IMPORT needed table every 24 hours to some other access files on server and import them into main as well (this gives me the change to rename tables, otherwise all come in with same names).

the main financial page, well, clearly it takes a while to load. right now i'm using a bunch(read: A LOT) of pre-saved queries and DSUM statements, then i add the DSUM results in vba and project to form.

can i speed up this process? is DSUM particularly slow?

 

[Galaxiom]

Every DSum is essentially a query so using them in large numbers will always be slow especially used inside a query where it would be applied for each record.

Try to replace the multiple DSums with queries.

Index fields that are used in the critreia or lions of any queries.

Use recordsource queries that return only the immediately required records rather than the whole table.

 

[CHAOSinACT]

so when i DSUM a query i'm making a query of a query? i just want to total a field in a query, DSUM seemed the easiest way. the queries are already filtered as much as possible i just need the total of a field in it....

 

[Galaxiom]

A Sum aggregate field inside the query itself would be faster as it would be performed during the query. Or a Sum([fieldname]) function as a control source of a textbox in the footer of the form.

 

[CHAOSinACT]

A Sum aggregate field inside the query itself would be faster as it would be performed during the query. Or a Sum([fieldname]) function as a control source of a textbox in the footer of the form.

if i go the first option, how do i retrieve the aggregate summations from the query with vba code? i need them to tally with several queries on way to form....

 

[Galaxiom]

how do i retrieve the aggregate summations from the query with vba code?

Open a recordset on the query.

 

[CHAOSinACT]

ok ... i will experiment with that, thanks!!

 

[gemma-the-husky]

the fastest solution is to use a query/series of queries - if you can formulate them.

this will most likely depend on the structure of the data within the myob files

 

[CHAOSinACT]

i can get the queries, but i would have THOUGHT ( i was wrong i guess) that tallying a query column with a simple DSUM was fastest...

loading it into recordset memory in vba and processing all that seems slower or more intesive and a lot of coding just to get a total.

 

[Galaxiom]

i can get the queries, but i would have THOUGHT ( i was wrong i guess) that tallying a query column with a simple DSUM was fastest...

loading it into recordset memory in vba and processing all that seems slower or more intesive and a lot of coding just to get a total.

A DSum is a function that creates a recordset based on the Domain (the table or query in the second argument). Behind the outward simplicity of the Domain functions there is the same process going on.

The query will produce the Sum as part of the first process. The DSum has to separately reprocess the data retieved by the query.

Loading a recordset is simply the technique to retrieve the record from the query using VBA. The recordset is created as part of the query. The first record will contain the Sum aggregate so it is a very basic recordset task.

 

[CHAOSinACT]

OK, i've used recordsets before, not as up to date with them as i like but clearly will be soon lol. to be clear i'm loading about 35-40 summed querries for 1 page (no i don't have a choice!) so that means loading and unloading that many queries in code. DSUM works to test but is SOOOOOOO slow. i will rework asap.

 

[Galaxiom]

to be clear i'm loading about 35-40 summed querries for 1 page (no i don't have a choice!) so that means loading and unloading that many queries in code.

Consider the possibilities of first combining the data from different companies into temporary tables where the data would be more suited to directly generating the reports with simpler queries.

I would also be seriously thinking about naming conventions and/or functions to allow a loop through the multitude of queries without having to use repetitive code.

 

[CHAOSinACT]

I would also be seriously thinking about naming conventions and/or functions to allow a loop through the multitude of queries without having to use repetitive code.

lol i consider it but reality is i have a VERY tight schedule and pretty non-understanding managers. we can meet the deadline with slower software and make it faster after deadline though. i'm painfully aware i will be looking over this for some time to improve it. - btw prob with fusing into one table is they want to see where all the data comes from as right now no one can find a thing or figure out how expenditure has been spread accross the files (its different for nearly every job as the myob data entry and company files "evolved" more than were planned)

tragically its all working great but slow. but we'll get there....i greatly appreciate all the input. it won't all make into this version pass but will eventually

 

[Galaxiom]

DSUM works to test but is SOOOOOOO slow.

It may be that the amount of data involved is going to always take a long time. Don't be fooled by the apparent speed of the queries. Queries on large datasets don't return all records immediately. Scroll down and watch the records being calculated as required. Or apply a filter that will only return a screen full or less.

Once you ask for the DSum the query must return all the records. Include the Sum aggregate in the query and it will take a lot longer too because it needs all the results. However the Sum aggregate will be faster than the Domain function.

 

[Galaxiom]

btw prob with fusing into one table is they want to see where all the data comes from as right now no one can find a thing or figure out how expenditure has been spread across the files

When combining the data include another field to indicate the source of each record. However unless there is a major restructure and consolidation of the data involved in the new tables then it won't really help much.