[creativecoding]

Well this will be my first (and hopefully last) time posting here. I'm working with a friend on a CRM program (for managing small-medium contractor businesses). We noticed that the load time was taking painfully long. Like 5 seconds long. Unacceptable.

I do some scouting around and find the cause of the problem... One of our quick-search form for customers wasn't so quick. It was loading the suggestions box with every customer in the database (so you can go straight from the search box -> customer). We're testing on a database with just under 900 customers. Obviously that's pretty bad to start with.... but it gets worse.

Here's the code I used to get the rows. It's obviously 2am code and as soon as I found it I gasped with horror, and took a shower in effort to clean myself of my sins.

        /*
	 * getBulk(int $count, int $page)
	 * Gets bulk information of customers
	 * Returns their information in an array
	 * Limited to $count, 0 for unlimited
	 * Use $page if you have $count set for multiple pages
	*/
	public function getBulk($count, $page){
		$result = array();
		$offset = $page * $count;

		$sql = "SELECT id FROM customers LIMIT ? OFFSET ?;";
		if($stmt = parent::get("db")->mysqli()->prepare($sql)){
			$stmt->bind_param('ii', $count, $offset);
			$stmt->execute();
			$stmt->bind_result($id);
			while($stmt->fetch()){
				$result[] = $id;
			}
			$stmt->close();
			foreach($result as $key => $id){
				$result[$key] = $this->getInfoById($id);
			}
			return $result;
		}
		return false;
	}

	/*
	 * getAll()
	 * Returns all customers
	*/
	public function getAll(){
		$stillmore = true;
		$result = array();
		$page = 0;
		while($stillmore){
			$tmp = $this->getBulk(1, $page);
			if(empty($tmp)){
				return $result;
			}
			$result[] = $tmp;
			$page++;
		}
	}

I was fetching 900 rows, one query at a time

Totally horrible code, I don't even know what I was thinking. I fixed it up and now it uses a single query and the page load has been reduced to 1-2 seconds. Furthermore it's my hopes to take out retrieving all users at once, and developing something like what Google has.

This post has been edited by creativecoding: 07 January 2013 - 04:01 PM

[Martyr2]

What I like most about 2am code is that at that time you are pretty much so fried that you are just whipping out massive amounts of code, thinking you are all elite and stuff. Then when you finally come to the next day after a good sleep, you may find it horrible, but at least you don't remember half of it and then you can simply debug it as if it was someone else's code.

So in short you get the massive amounts of code from the 2am session that pretty much pushes you to 99% completion and then you get the next day to spend some time correcting it and only having to do 1% left of the project.

It is fantastic if you don't screw things up too bad during 2am.

[BobRodes]

One of the marks of an expert is knowing when to quit for the day.