Query Speed
in [Queries]
|
From: ErikFM via AccessMonster.com on 23 Mar 2010 09:39 Hi there! I am setting up a database for our branch offices to use in a distributed (FE/BE) format, and am wondering which would be better for speed: 1. A distinct table for each branch, with a UNION ALL query forming the basis for regional/countrywide reports. 2. One massive data table that queries would launch SQL against. Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: Jerry Whittle on 23 Mar 2010 10:38 One table - please! Union queries are slow and inefficient. Union All is somewhat faster but there's always a chance of duplicate data being retrieved. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ErikFM via AccessMonster.com" wrote: > Hi there! > > I am setting up a database for our branch offices to use in a distributed > (FE/BE) format, and am wondering which would be better for speed: > > 1. A distinct table for each branch, with a UNION ALL query forming the basis > for regional/countrywide reports. > > 2. One massive data table that queries would launch SQL against. > > Thanks! > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 > > . >
From: ErikFM via AccessMonster.com on 23 Mar 2010 10:59 Thanks, Jerry! One question... what if I want unique receipt numbers by branch? For instance Receipt Number Branch 1 Houston 2 Houston 3 Houston 1 Dallas 2 Dallas 3 Dallas Should I have an AutoNumber on the Master Table, and then in the form have some VBA alchemy to scan for the MAX(Receipt Number) WHERE Branch = [Branch], then set the next Receipt # to that number +1? Auto Number Receipt Number Branch 1 1 Houston 2 2 Houston 3 3 Houston 4 1 Dallas 5 2 Dallas 6 3 Dallas Jerry Whittle wrote: >One table - please! Union queries are slow and inefficient. Union All is >somewhat faster but there's always a chance of duplicate data being retrieved. >> Hi there! >> >[quoted text clipped - 7 lines] >> >> Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: Jerry Whittle on 23 Mar 2010 11:51 The autonumber PK is a good idea. Adding +1 to the highest number sounds like the way to go. One fly in the ointment is if multiple people can be adding similar records at the same time. Depending on when the +1 is figured, like at the start of the record entry, more than one record could have the same Branch/Receipt Number. You probably also want to make the Branch and Receipt Number field combination a unique index. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ErikFM via AccessMonster.com" wrote: > Thanks, Jerry! > > One question... what if I want unique receipt numbers by branch? > > For instance > > Receipt Number Branch > > 1 Houston > 2 Houston > 3 Houston > 1 Dallas > 2 Dallas > 3 Dallas > > Should I have an AutoNumber on the Master Table, and then in the form have > some VBA alchemy to scan for the MAX(Receipt Number) WHERE Branch = [Branch], > then set the next Receipt # to that number +1? > > Auto Number Receipt Number Branch > > 1 1 Houston > 2 2 Houston > 3 3 Houston > 4 1 Dallas > 5 2 Dallas > 6 3 Dallas > > Jerry Whittle wrote: > >One table - please! Union queries are slow and inefficient. Union All is > >somewhat faster but there's always a chance of duplicate data being retrieved. > >> Hi there! > >> > >[quoted text clipped - 7 lines] > >> > >> Thanks! > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 > > . >
From: ErikFM via AccessMonster.com on 23 Mar 2010 16:00 Thanks, Jerry! I did something similar in house for small team of 4 analysts... time to ramp up to the tune of 40 branch offices! =) Jerry Whittle wrote: >The autonumber PK is a good idea. Adding +1 to the highest number sounds like >the way to go. One fly in the ointment is if multiple people can be adding >similar records at the same time. > >Depending on when the +1 is figured, like at the start of the record entry, >more than one record could have the same Branch/Receipt Number. You >probably also want to make the Branch and Receipt Number field combination a >unique index. >> Thanks, Jerry! >> >[quoted text clipped - 31 lines] >> >> >> >> Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
|
Next
|
Last
Pages: 1 2 Prev: Query to add fake records from table Next: Odd Parameter request in Crosstab |