Application efficiency

[Malcy]

I have a complex reporting process that I worked out using ten temporary tables - and I know it works. My question is am I better to use permanent tables, deleting prior to the reporting, then appending the relevant records or would I be better creating make table queries?
As a general rule I clear down the temporary tables after the reporting to stop unneccessary storage. I am just not sure which approach would be more effective and speedy. This is becoming relevant since the application is now getting so complex it is beginning to slow down a bit.
Thanks for any pointers

 

[Rabbie]

Rather than create the temporary tables from scratch each time you may find it more efficient to dlete all the records from them and then repopulate the tables when you need them. In any case you will need to compact/repair your DB regularly to avoid your DB becoming bloated.

 

[Malcy]

Thanks Rabbie
That is effectively what I have been doing - I call them temporary tables since they hold no permanent data but the structure stays there.
I thought this would be likely to cause less bloat but wanted to check.
Hopefully you still celebrate Hogmanay even from exile?
HNY

 

[supercool]

through my understanding, instead of deleting the table each time and create tables, delete those records might be more effective.
actually, u can put a timer inside ur source code, to check which way takes lesser time.
hope it is useful

 

[Malcy]

Thanks Supercool
I might explore the timer route since if I can set it up to record without too much sweat then it might prove a useful diagnostic tool - without slowing things down further hopefully!
Best wishes

 

[boblarson]

Yes, deleting records and appending new records will take less time and will also save on database bloat which would otherwise occur when you create a table and then delete the whole thing and then recreate it again. If the structure of the data is always the same then it would definitely be beneficial to just use a:

Delete * From YourTableName

and then append the new records.

 

[Malcy]

Thanks Bob
That is what my instinct said, but it is good to have it confirmed!