You can never display thousands of items of data at once, so consider a system whereby only a subset is on view, and of this subset only the summary data is shown (i.e. enough to tempt a user to click on it to go further).
The user can then use next/previous functionality to browse futher. If display is not an issue, the only solutions I can think of are: i) Good indexing on the tables (Access?). Not sure how it will affect pulling all the data ii) Cacheing data iii) De-normalising the data into a reporting table, i.e. one table holding all the data, updated from the 'main' tables by triggers or timed update scripts. This at least avoids a join. Sooner or later you have to get around the problem of pulling thousands of records from a db over the network -----Original Message----- From: Luca Ventura [mailto:[EMAIL PROTECTED]] Sent: 16 May 2002 11:08 To: [EMAIL PROTECTED] Subject: Retrieve data in Microsoft Access (off-topic) Hello everybody! I hope someone can help me to solve this problem (with an efficient solution) and I excuse with everybody if it is "off-topic" but I posted it in many other mailing lists receiving no reply an I have a great need to solve it...:-( I use Microsoft Access as database and a JDBC-ODBC driver to access to it. I don't know how to retrieve all the data I need in an efficient way from the tables I defined. The tables stores information about some companies and the special offers for sale they have for their clients. The tables are (in pseudo-syntax): TABLE "COMPANIES": CompanyName: String IDCompany: long --> Primary key Address: String Region: String .. (other information) TABLE "SPECIAL_OFFERS" NameProduct: String IDCompany: long --> Foreign key references "COMPANIES" PriceOffer: double [price in euro] PriceProduct: double [price in euro, it is greater than PriceOffer] ..(other information) Of course a company can have and sell more special offers (one to many relation) for sale but a special offer belongs only to a company (one to one relation). I want to be able to retrieve from the database all the information about some particular companies (for example all the companies that are present in a given region) and for each of them the special offers they have. I though to the following solutions: 1) Execute a query like this: select * from Companies, Special_Offers where Region="Italy" and Special_Offers.IDCompany=Companies.IDCompany; The problem of this solution is that I would obtain as result a table with the information about a company repeated as many times (and put in different rows) as the number of the special offers it has, like this: CompanyName IDCompany Address Region ... NameProduct IDCompany PriceOffer PriceProduct... Company 1 1 Address1 Region1 Product1 1 120.32 150.23 Company 1 1 Address1 Region1 Product2 1 110.32 140.23 Company 1 1 Address1 Region1 Product3 1 10.32 20.23 And if I have thousands of companies and many special offer for each of them this solution waste a lot of memory beacuse many information are repeated. 2)Execute two different queries: the first one to retrieve all the information about the companies I need (from the table Companies) and the second one to retrieve the special offers of each of them (from the table Special_Offers, using the ID of each company). Here I have two problems: a) Where can I store all the IDs of the Companies that I obtained executing the first query (consider that I can have thousands of IDs)? In memory or in the disk? b) I would have to execute a query using the IDs obtained from the first query, like this: select * from Special_Offers where IDCompany=1 OR IDCompany=5 OR IDCompany=6 ... IDCompany=876... 1,5,6,876 are some of the IDs I obtained from the first query. The problem is that I can have thousands of IDs and for this reason the query string to execute would be very long and I don't know if it would be able to execute it. To avoid this problem I could execute so many queries as the IDs I have but this solution would be very inefficient.... Besides a very IMPORTANT THING: I would like to have the results of the second query in the same order of the IDs I obtained from the first query. I mean: if the first query gave me the ID of company1 (eg: 123) and then that one of the company2 (e.g: 75) between the results of the second query I would like to have the special offers of Company1 (with ID number 123) before that ones of Company2 (with ID number 75). Thanks a lot to everybody in advance! Luca ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html This e-mail is confidential and intended only for the addressee(s) shown. If you are not an intended recipient, please be advised that any use, dissemination, forwarding or copying of this e-mail is strictly prohibited. Internet e-mails are not necessarily secure and the AXA Group does not accept responsibility for changes made to this message after it was sent. Please note that incoming and outgoing electronic mail messages may be monitored. Should you receive this transmission in error, notify the sender immediately. Thank you ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
