Could you please send also tempsap ? Thanks and regards, Jocelyn
----- Original Message ----- From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:55 PM Subject: RE: MySQL vs. Oracle (not speed) > this is a read only datawarehouse database.... we refresh monthly from the > antiquated mainframe we have > > ok here are the tables.... > > Adminhierarchy 6.5 million records > CREATE TABLE adminhierarchy ( > Source char(2) NOT NULL , > WritingAgentID varchar(15) NOT NULL , > CoverageID varchar(15) NOT NULL , > AgentLevelID varchar(15) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerTypeID varchar(5) NOT NULL , > PercentOfCase double , > RegionCode varchar(5) , > CoverageIdSbc varchar(10) NOT NULL , > WritingAgentSlot int(11) , > RadDistributionCode varchar(5) , > KEY WritingAgentIDIndex (WritingAgentID), > KEY AgentLevelIDIndex (AgentLevelID), > KEY CoverageIDIndex (CoverageID), > KEY ProducerIDIndex (ProducerID), > KEY ProducerTypeIDIndex (ProducerTypeID), > KEY CoverageIdSbcIndex (CoverageIdSbc), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > ); > > admin coverage 1.5 million > CREATE TABLE admincoverage ( > Source char(2) , > IsRider char(1) , > CoverageID varchar(10) NOT NULL , > CoverageIdSbc varchar(10) NOT NULL , > ParentCoverageID varchar(10) NOT NULL , > GroupID varchar(10) NOT NULL , > EmployeeID varchar(10) NOT NULL , > ProductId varchar(11) NOT NULL , > OriginalCertificateNumber varchar(20) , > StatusID varchar(10) NOT NULL , > ApplicationDate date , > effectivedate date NOT NULL , > PaidToDate date NOT NULL , > InitialPremiumDate date NOT NULL , > StatusDate date , > BenefitAmount double , > ModalPremium double , > AnnualPremium double , > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > Waived char(1) , > IssueAgePI smallint(6) , > IssueStatePI char(2) , > ResidentStatePI char(2) , > ResidentZipPI varchar(9) , > GenderPI char(1) , > DateOfBirthPI date , > TaxIDPI varchar(9) , > RelationToEmp varchar(5) , > BillTypeID varchar(10) NOT NULL , > BillModeID varchar(10) NOT NULL , > DateBilled date , > Reinsured varchar(5) , > InsuredLives char(2) , > BenefitFrequency char(1) , > BenefitPercent varchar(9) , > SmokerPI char(1) , > PaidUpDate date , > SegId char(2) , > RegionCode varchar(5) , > TerminationDate date , > KEY CoverageID (CoverageID), > KEY CoverageIdSbc (CoverageIdSbc), > KEY GroupId (GroupID), > KEY ProductID (ProductId), > KEY StatusID (StatusID), > KEY ParentCoverageIdIndex (ParentCoverageID), > KEY EffectiveDateIndex (effectivedate), > KEY PaidToDateIndex (PaidToDate), > KEY InitPremDateIndex (InitialPremiumDate), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc), > KEY CovIdCovIdSbcInitPremIndex (CoverageID, CoverageIdSbc, > InitialPremiumDate) > ); > > CREATE TABLE adminproducer ( > Source char(2) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerNbr varchar(15) NOT NULL , > IsAgency tinyint(4) , > ProducerName varchar(50) , > StatusID char(1) , > EffectiveDate date , > TerminationDate date , > TaxID varchar(9) NOT NULL , > Address1 varchar(50) , > Address2 varchar(50) , > City varchar(30) , > State char(2) , > Zip varchar(9) , > MailingAddress1 varchar(50) , > MailingAddress2 varchar(50) , > MailingCity varchar(30) , > MailingState char(2) , > MailingZip varchar(9) , > AdvanceBalance double , > DistributionCode varchar(5) , > KEY ProducerIDIndex (ProducerID), > KEY ProducerNbrIndex (ProducerNbr), > KEY TaxIDIndex (TaxID) > ); > > CREATE TABLE adminproduct ( > Source char(2) , > IsRider char(1) , > ProductID varchar(15) NOT NULL , > ProductNbr varchar(15) , > ProductDescription varchar(50) , > UnderwriterID varchar(15) , > LOBID varchar(15) NOT NULL , > BusinessType varchar(5) , > StatutoryLinesCode varchar(5) , > WaiverAvailable tinyint(4) , > AccidentElim mediumint(9) , > AccidentBen1 double , > AccidentBen2 double , > SickElim mediumint(9) , > SickBen1 double , > SickBen2 double , > KEY ProductIdIndex (ProductID), > KEY LobIdIndex (LOBID) > ); > > > -----Original Message----- > From: Mark Matthews [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:15 AM > To: Mary Stickney; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Mary Stickney wrote: > > I have been doing speed tests.... the same query ran on MYSQL took 45 > > minutes > > on MS-SQL it took 11 minutes...... > > > > yes you do get what you pay for.... > > Why not post the queries and the schemas here? My guess is you don't > have something indexed correctly, or are using a query that gets > optimized well by MS-SQL Server, but not MySQL. > > We all know that SQL is not absolutely portable, and that when you move > queries from database to database, that there is some work to > re-optimize them. > > There are some queries that just work better on databases other than > MySQL, but they are very few and far-between. > > Without any way to backup your claim, it is hard for anyone here to > believe that you have done everything possible to make a fair > comparison. Given your previous comments in this forum, it appears that > you must be trolling. > > -Mark > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
