Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935

    Why does this simple update take so long!!

    This is related to my other thread but I didn't want to post for confusions sake.

    I've got a table that is populated each night by a query that unzips a file sent to us and unloads it into the database. We have no control over the text file that comes in.

    The database structure (which I cannot control the fields of but can control their type) is:

    Code:
      `timeStamp` text NOT NULL,
      `listingNumber` varchar(255) NOT NULL default '',
      `listingProofed` text NOT NULL,
      `mlsPhotoLink` text NOT NULL,
      `currentPrice` varchar(255) NOT NULL default '0',
      `primaryListingClass` text NOT NULL,
      `secondaryListingClass` text NOT NULL,
      `NoBedsTotal` text NOT NULL,
      `NoBathsTotal` text NOT NULL,
      `streetNumber` text NOT NULL,
      `streetName` text NOT NULL,
      `governingAgency` text NOT NULL,
      `county` text NOT NULL,
      `state` text NOT NULL,
      `zipCode` text NOT NULL,
      `schoolDistrict` text NOT NULL,
      `elemSchool` text NOT NULL,
      `PPN` text NOT NULL,
      `legalDescription` longtext NOT NULL,
      `lotSize` text NOT NULL,
      `acreage` text NOT NULL,
      `lotWooded` text NOT NULL,
      `ripRights` text NOT NULL,
      `lakeName` text NOT NULL,
      `frontage` text NOT NULL,
      `zoning` text NOT NULL,
      `directions` longtext NOT NULL,
      `presentUsage` text NOT NULL,
      `yearBuilt` text NOT NULL,
      `age` text NOT NULL,
      `airType` text NOT NULL,
      `archStyle` text NOT NULL,
      `heatType` text NOT NULL,
      `ANSISource` longtext NOT NULL,
      `ANSISquareFootage` longtext NOT NULL,
      `TFLASquareFootage` longtext NOT NULL,
      `pool` text NOT NULL,
      `fireplace` text NOT NULL,
      `numberFireplace` text NOT NULL,
      `garageType` text NOT NULL,
      `garageAttached` text NOT NULL,
      `noStalls` text NOT NULL,
      `exterialMaterials` text NOT NULL,
      `basementType` text NOT NULL,
      `driveway` text NOT NULL,
      `water` text NOT NULL,
      `sewer` text NOT NULL,
      `roomsDescription` longtext NOT NULL,
      `financingOptions` longtext NOT NULL,
      `financialComments` longtext NOT NULL,
      `possession` text NOT NULL,
      `taxableValue` text NOT NULL,
      `taxYear` text NOT NULL,
      `assessValue` text NOT NULL,
      `assessments` text NOT NULL,
      `assessFor` text NOT NULL,
      `interiorFeatures` text NOT NULL,
      `ExteriorOtherFeatures` text NOT NULL,
      `outBuildings` text NOT NULL,
      `poolFeatures` text NOT NULL,
      `landscapeFeatures` text NOT NULL,
      `projectName` text NOT NULL,
      `buildingNumber` text NOT NULL,
      `unitNumber` text NOT NULL,
      `associationFees` text NOT NULL,
      `entry` text NOT NULL,
      `condoIncludes` text NOT NULL,
      `condoAmmenities` text NOT NULL,
      `utilitiesAvailable` text NOT NULL,
      `utilitiesAttached` text NOT NULL,
      `roadSurface` text NOT NULL,
      `kitchenLevel` text NOT NULL,
      `formalDiningLevel` text NOT NULL,
      `familyRoomLevel` text NOT NULL,
      `livingRoomLevel` text NOT NULL,
      `RecRoomLevel` text NOT NULL,
      `denStudyLevel` text NOT NULL,
      `mainFloorLaundryLevel` text NOT NULL,
      `masterBedroomLevel` text NOT NULL,
      `bedroom2Level` text NOT NULL,
      `bedroom3Level` text NOT NULL,
      `bedroom4Level` text NOT NULL,
      `masterBathLevel` text NOT NULL,
      `eatingAreaLevel` text NOT NULL,
      `diningRoomLevel` text NOT NULL,
      `greatRoomLevel` text NOT NULL,
      `noBedsBasement` text NOT NULL,
      `noBathsBasement` text NOT NULL,
      `noBedsFirstFloor` text NOT NULL,
      `noBathsFirstFloor` text NOT NULL,
      `noBedsSecondFloor` text NOT NULL,
      `noBathsSecondFloor` text NOT NULL,
      `noBedsThirdFloor` text NOT NULL,
      `noBathsThirdFloor` text NOT NULL,
      `restroomEasements` text NOT NULL,
      `certificationExceptions` text NOT NULL,
      `tillableAcres` text NOT NULL,
      `woodedAcres` text NOT NULL,
      `pastureAcres` text NOT NULL,
      `otherAcres` text NOT NULL,
      `keybox` text NOT NULL,
      `showInstructions` longtext NOT NULL,
      `shows` longtext NOT NULL,
      `listingComments` longtext NOT NULL,
      `shortComments` longtext NOT NULL,
      `1stListingAgentID` text NOT NULL,
      `1stListingAgentName` text NOT NULL,
      `1stListingOfficeCode` text NOT NULL,
      `1stListingOfficeName` text NOT NULL,
      `1stListingOfficeAddress` text NOT NULL,
      `1stListingOfficePhone` text NOT NULL,
      `2ndListingAgentName` text NOT NULL,
      `2ndListingOfficeCode` text NOT NULL,
      `2ndListingOfficeName` text NOT NULL,
      `2ndListingOfficeAddress` text NOT NULL,
      `2ndListingOfficePhone` text NOT NULL,
      `3rdListingAgentName` text NOT NULL,
      `AddMediaLink1` text NOT NULL,
      `AddMediaText1` text NOT NULL,
      `AddMediaLink2` text NOT NULL,
      `AddMediaText2` text NOT NULL,
      `AddMediaLink3` text NOT NULL,
      `AddMediaText3` text NOT NULL,
      `AddMediaLink4` text NOT NULL,
      `AddMediaText4` text NOT NULL,
      `AddMediaLink5` text NOT NULL,
      `AddMediaText5` text NOT NULL,
      `AddMediaLink6` text NOT NULL,
      `AddMediaText6` text NOT NULL,
      `AddMediaLink7` text NOT NULL,
      `AddMediaText7` text NOT NULL,
      `AddMediaLink8` text NOT NULL,
      `AddMediaText8` text NOT NULL,
      `AddMediaLink9` text NOT NULL,
      `AddMediaText9` text NOT NULL,
      `AddMediaLink10` text NOT NULL,
      `AddMediaText10` text NOT NULL,
      `AddMediaLink11` text NOT NULL,
      `AddMediaText11` text NOT NULL,
      `AddMediaLink12` text NOT NULL,
      `AddMediaText12` text NOT NULL,
      `AddMediaLink13` text NOT NULL,
      `AddMediaText13` text NOT NULL,
      `AddMediaLink14` text NOT NULL,
      `AddMediaText14` text NOT NULL,
      `AddMediaLink15` text NOT NULL,
      `AddMediaText15` text NOT NULL,
      `AddMediaLink16` text NOT NULL,
      `AddMediaText16` text NOT NULL,
      `AddMediaLink17` text NOT NULL,
      `AddMediaText17` text NOT NULL,
      `AddMediaLink18` text NOT NULL,
      `AddMediaText18` text NOT NULL,
      `AddMediaLink19` text NOT NULL,
      `AddMediaText19` text NOT NULL,
      `AddMediaLink20` text NOT NULL,
      `AddMediaText20` text NOT NULL,
      `AddMediaLink21` text NOT NULL,
      `AddMediaText21` text NOT NULL,
      `AddMediaLink22` text NOT NULL,
      `AddMediaText22` text NOT NULL,
      `AddMediaLink23` text NOT NULL,
      `AddMediaText23` text NOT NULL,
      `AddMediaLink24` text NOT NULL,
      `AddMediaText24` text NOT NULL,
      `floorPlanLink` text NOT NULL,
      `virtualTourLink` text NOT NULL,
      `disclaimer` longtext NOT NULL,
      `brokerReciprocityText` longtext NOT NULL,
      `copyrightNotice` longtext NOT NULL,
      `brokerReciprocityLogoLink` longtext NOT NULL,
      `assocNameText` longtext NOT NULL,
      `MLSExchangeText` longtext NOT NULL,
      `brokerOfficeLogoLink` longtext NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    and has about 13000 rows.

    If I try to do

    Code:
    $allListings = query('SELECT `listingNumber`,`currentPrice` FROM `grarmls` LIMIT 200');
    
    foreach($allListings as $tmp) {
    	$newPrice = str_replace(',', '', $tmp['currentPrice']);
    	query('UPDATE `grarmls` SET `currentPrice` = "'.$newPrice.'" WHERE `listingNumber` = "'.$tmp['listingNumber'].'"');
    }
    The select query takes .01 seconds or less, and the loop itself with the str_replace adds almost no time if the update query is copmmented out. However, if I leave the update query in and it loops the 200 the server will bomb from the load. Really, above a limit of 25 the server is not happy (25 takes almost 1 second).

    It's not the server, I have tried it on two of them. I am only working with 2 columns of 25 rows, what gives?

  2. #2
    Wow!

    I assume you don't have an index for listingNumber and the database server will scan each record in that table.

    The table structure is so WRONG that you cannot do much to speed things.

    Try to do everything in a single pass with something like:
    'UPDATE `grarmls` SET `currentPrice` = REPLACE(currentPrice,',','')

  3. #3
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Quote Originally Posted by FW-Mike
    This is related to my other thread but I didn't want to post for confusions sake.

    I've got a table that is populated each night by a query that unzips a file sent to us and unloads it into the database. We have no control over the text file that comes in.

    The database structure (which I cannot control the fields of but can control their type) is:

    Code:
      `timeStamp` text NOT NULL,
      `listingNumber` varchar(255) NOT NULL default '',
    [..]
      `currentPrice` varchar(255) NOT NULL default '0',
    [..]
      `zipCode` text NOT NULL,
    [..]
      `yearBuilt` text NOT NULL,
      `age` text NOT NULL,
    [..]
      `taxableValue` text NOT NULL,
      `taxYear` text NOT NULL,
      `assessValue` text NOT NULL,
    [..]
      `noBedsBasement` text NOT NULL,
      `noBathsBasement` text NOT NULL,
      `noBedsFirstFloor` text NOT NULL,
      `noBathsFirstFloor` text NOT NULL,
      `noBedsSecondFloor` text NOT NULL,
      `noBathsSecondFloor` text NOT NULL,
      `noBedsThirdFloor` text NOT NULL,
      `noBathsThirdFloor` text NOT NULL,
    [..]
      `1stListingOfficePhone` text NOT NULL,
    [..]
      `2ndListingOfficePhone` text NOT NULL,
    [..]
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    and has about 13000 rows.
    First, the fields that I have highlighted -- you need to change their types. No need for currentPrice to be text, or noBathsThirdFloor to be text (can just be boolean).

    Also, there are NO indexes on your table, which is probably why its so slow. You also could do with some normalization -- that would probably speed things up. As you said, you don't have control over the structure; but whoever came up with this table needs to stop designing database tables; and read a book on database design.

    You might as well put all this information in a flat file for you are getting absolutely NO benefit from using a database server the way your table is designed.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •