RE: Data Warehouse in SAPDB or MYSQL?Kevin & Noah, Thanks for the responses. I have installed it as OLAP, but if I remember correctly, I saw a message a while back from one of the developers indicating they have not implemented that feature yet, and there is no difference in any of the types of db's.
I'm not sure what to try next to "work through it". If the db must cache (rebuild) each table it joins, then it is unfeasible to use with any medium sized data warehouse. I hope I'm doing something wrong, but I don't know what it would be. I do have auto log turned on. In order to answer the most basic business questions, a typical query would need to join Accounts, Products, Sales Reps, Time, and Sales together. It seems like people are saying this is too many tables to join in SAPDB. There often will be a limit (entry in the Where clause) for only Accounts and Time, or maybe for only Products and Time. Joe -----Original Message----- From: Kevin Wilson [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 11:22 AM To: 'Noah J SILVA '; 'Joe Garrett ' Cc: '[EMAIL PROTECTED] ' Subject: RE: Data Warehouse in SAPDB or MYSQL? Forgive me if you already thought of this but there are several "types" of sapdb installations, one of which is geared for data wharehousing...visit link below. http://www.sapdb.org/7.4/htmhelp/df/dd8c63c6bc11d5993e00508b6b8b11/content.h tm Currently I am struggling with a HD I/O issue when auto-logging is enabled for our OLTP instance so "I feel your pain" but I think the work will pay-off in the long run. -----Original Message----- From: Noah J SILVA To: Joe Garrett Cc: [EMAIL PROTECTED] Sent: 9/19/2003 10:57 AM Subject: Re: Data Warehouse in SAPDB or MYSQL? Hi, I have run into this issue before, and it has usually been because I used several outer joins at once, or I didn't have enough space free. SAPDB does seem to need an unreasonable amount of temp space for queries and views sometimes, On the other hand we have run a reasonably large data warehouse type multi-user database that is used for "important stuff" 24/7 for months now with no problems. If the data is read-only (and you have a backup), and the queries used will be simple, then perhaps MySQL isn't too bad of a choice. If changes are made, complex views and queries are used, and/or the data is super important, then mySQL probably isn't such a good choice. I do wish that result space problems would be looked at more closely. In the last case I wrote about, the database I had was actually reporting _negative_ free space. Now there's a first! (It turned out that there was a lot of "temp" space used for.. something, and increasing the database size a little helped.) Thank you, Noah Silva IS&T - Programmer Analyst (215) 419 - 7916 "Joe Garrett" <[EMAIL PROTECTED]> 09/19/2003 11:07 AM To: <[EMAIL PROTECTED]> cc: Subject: Data Warehouse in SAPDB or MYSQL? Hi, I'd like to use SAPDB for small to medium data warehouse systems. I've been testing it and can't get it to work properly. Any help would be appreciated. Please correct me if I'm wrong. I'm running SAPDB 7.4 on my Win XP laptop. DB size began as one 2Gb file. I have 4 tables: Accounts, Products, Sales Reps, Time, and Sales. Sales has about 600,000 records in it, Accounts about 90,000, and Products about 500,000, Sales Reps about 60,000, Time about 50,000. AT first, any query with a join would run out of space. After reviewing the many messages on the space issue, I added another 2 Gb file to data. It still didn't work. I finally added more files, so there are 4 2 Gb files of data and 1 Gb file of log, and I added views that consist of only the identifier columns I need, so each record is not large. Now, I don't get the space error, but I am still unsuccessful in joining. When I joined the sales table to the time table to update the sales table with the appropriate time key based on the sale date, it did so in about an hour. I can live with that. But, when I try to join the sales table with one of the larger tables (like accounts), it kept running out of space. I am trying to update the sales table with the appropriate account key value by doing the following query: UPDATE T_STG_SALES SET ACCT_KEY_CURR_1 = (SELECT A.ACCT_KEY_CURRENT FROM JOIN_ACCOUNTS A WHERE ACCT_ID_1 = A.ACCT_ID AND SALE_DATE BETWEEN A.ACCT_REC_BEGIN_DATE AND ACCT_REC_END_DATE) This query ran for 12 hours before I killed it. What scares me about SAPDB is that it appears to rewrite each table when a join is needed, and seems to need more than double the size of the data to do it. In a data warehouse situation, users often like to browse the data to determine what questions to ask. This browsing involves several joins on large tables. It appears to me that SAPDB will not allow this due to the extrordinarily long time it takes to cache each table for a join. If I'm understanding what's going on properly, SAPDB can not be used for data warehousing. I hope I'm wrong. Please advise. Maybe I'll try MySQL too. Joe Garrett Minnesota, USA -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
