select vs make table 1

[y2k1981]

Hi all,

I was just wondering, when should you use a make table query instead of using a select query? I have a number of make table queries and I'm wondering if I should have used select queries instead?

 

[KenReay]

HI

You use a make table query when you want to make a 'persistent' copy of the selected data.

You use a SELECT query when you want to extract the data there and then, with no 'persistent' copy

For example, you might use a Make table query, if for example you wanted to extract a 'snap shot' of some data at a point in time for reporting purposes, conversely if you wanted to report 'as it is right now' you would use a select query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JackOfAllSoftware]

From a performance standpoint, if you are dealing with a large number of records processed by a select query that runs frequently, you may want to rework your db to use a make table query where you control how often you update the output table. In that case, loading the output table would likely be much faster than the select query.

For "smaller" dbs where I am not dealing with as many records, and I don't care as much about performance tuning, I usually use select queries, since I don't have to manage a lot of extra tables I don't need then.

Also, I often find that if I am willing to take the time to experiment with how I build my select queries, I can speed them up quite a bit. Sometimes eliminating intermediate steps in a select query built on other select queries can speed things up, sometimes not. Sometimes, I actually need to "decompose" a large, complex select query into multiple select queries to get the desired speedup. It is a matter of determining what the intermediate select queries are really buying you.

However, what is the "best performance" is obviously a matter of your taste. As they say, "your mileage may vary".

Steve