Arsen Pereymer wrote: > Currently, we have a large Microsoft SQL 2000 Database > containing over 100 > tables with over 100+ million rows. The purpose of the > database is to house > a datawarehouse. It currently takes about 20hrs to run the > ETL to populate > the database. > > We have been doing research and found that using MaxDB the > performance can > greatly increase. Thus we started a benchmark test . We moved > 5 tables to > MaxDB each containing approximately 800k rows. We re-wrote > the SQL script > mentioned below into MaxDB. > > INSERT INTO "DBA"."tbBenchmarkData" > SELECT > D."DistrictID", > 0, > 0, > BM."gradeID", > BM."subjectID", > SD."StudentID", > T."BMSEQNUM", > SD."ObjectiveID", > 1 as ObjectiveTypeID, > avg(SD."GotRight")*100, > count(SD."QW_ID") > FROM > "DBA"."tbTests " T, > "DBA"."tbStudentData" SD, > "DBA"."tbUserOrg" UO, > "DBA"."tbDistricts" D, > "DBA"."tbBMTestinfo" BM > Where > SD."TaskID" = T."TaskID" > AND > UO."UserID" = SD."StudentID" > AND > D."DistrictID" =UO."OrgID" > AND > BM."SEQNUM" =T."BMSEQNUM" > AND > T."BMSEQNUM" >0 > GROUP BY > D."DistrictID", > SD."StudentID", > BM."subjectID", > BM."gradeID", > T."BMSEQNUM", > SD."ObjectiveID" ; > > > When running the below script on Microsoft SQL server it > takes approximately > 5 minutes to insert 1.9 million rows. When running the same > script on MaxDB > takes over 1hr to insert the same amount of rows. > > Why is the MaxDB performance so slow?
Hi, what version of MaxDB are you using? With version 7.5.0.12 there were some major changes in join execution. In most cases we get better performance with this new join implementation. But in some cases, without good join transitions, it might gets even worse. Would you mind providing the explain plan of the select statement? So we could check if the new or the old join is used. Additionally you could try to update the optimizer statistics and create some indexes to improve join transitions. Best regards Holger SAP Labs Berlin -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
