Application with access database

Question

I am building something like (shop) application. All products are stored in database, and when the seler sels something, it is also writen in database in different table. for example
one table for all products (articles) named tblArts, and other table for every sale in tblSales
tblArts contain this columns (art_id, art_name, art_price...)

tblSales contain this columns (sale_id, seller_name, sale_price, sale_date...)
now we know that first table (tblArts) almost never change since you enter all articles.. (maybe one in a week adding a few more.. ) but however it probably not be containing more tnat 2 000-3 000 records..
but the other table (tblSales) fills rapidly (it will probbably grow for 200-300 records every day) so my question is what aproach you thing is the best for doing this..

since there would be queryes like ("Select * from tblSale where saler_name = ? and sale_date between ? and ?" after few monts (not to mention years) of using, this application would perform this query very slow..
i was thinking to use different table for let's say every year.. but would it solve the problem?? every year exequte query for creating table, where the name of the table contains the current year... what do you think???

any sugestions would be wellcome
i am using c# programing language, and access database ( i am limited with time and since i don't have experience with sql databases yet, for this project i must stick with access database), and i also would like to know if sql would do it faster so i start learinng it for future projects
thank you

Mahesh Chand · Answer

1. First thing you can do is put your column names in your SELECT query. SELECT * is much slower than putting SELECT column1 column2... even you have 100 columns. If you do have 100 columns your database design is wrong. 2. At a given time an application cannot show more than say 100 records. So you need to only select say 500 records at a time. Don't find all (millions of records). Next bulk 500 you get when you need them. 3. If you already know some queries you can cache them in different tables. 4. You can use VIEWS to store different data. Umm .. not sure if Access support Views. I think NOT. Using a new table for each year is OK but not a good idea. It is kind of where you have to change or hard code your queries.

Koteswararao Mallisetti · Answer

1. for data base side use triggers in my knowledge we must use one tringger on that sales table which creates a yearly table form that existing table 2.use the caching concepts off course by using this first time take some time but cumming request will server fast cache dependencyexpiration ... by combining both of this we achieve better performance