Access Performance

gabriel

Is using

"SELECT col1, col2, col3 FROM table"

faster in access performance then using

"SELECT * FROM table" when one of the fields contains large blocks of data and you only need to get data from the smaller fields?

discostudio

Absolutely...

You should only select the columns you actually need.

thorpe

in fact even when you want all fields it is still faster to specify each filed. otherwise, the database needs to perform an extra query to determine the field names.

as a rule, i never use SELECT *, not even in developement. as we all know... lots of code written in developement that we intend on fixing before production never gets fixed.

do it right the first time and sleep well.

edit: i think im going to make that last comment my motto.

gabriel

I have a Fanfiction archive and whenever I load the main page which only has about 4 small entriies it takes longer to load then the other pages I currently have it set on the main page to SELECT * and one of those fields contains rather lengthy stories.

Will loading the story descriptions specifing only the fields needed for the descriptions excluding the story field help with loading speed?

thorpe

i would think not. the kind of speed differences we are talking about here are would more than likely be measured in milliseconds, and i doubt you would notice the difference.

why dont you try it and see?

if you think your code is causing inificientcies, why not post it so we can see?

gabriel

Originally posted by thorpe
i would think not. the kind of speed differences we are talking about here are would more than likely be measured in milliseconds, and i doubt you would notice the difference.

why dont you try it and see?

if you think your code is causing inificientcies, why not post it so we can see?

Here is the code for the page that loads slower than all the others.

<?php
require "db.php";
require "config.inc.php";

// Load story options and story layout template

$key = "storyops";
$temp_array = file("templates/story_layout.html");


jsselect();

// if category.php GET var gets messed with boot the user back to homepage

if(!isset($_GET['req']))
{
	go(".");
}

// split the two numbers into category and subcategory
list($cat, $subcat) = explode(":", $_GET['req']);
require "head.php";

// If subcategory number equals 0 print Misc for subcat

if($subcat > 0){
$categoryloop = "SELECT * FROM $dbseries WHERE seid = $subcat";
$categoryloop1 = mysql_query($categoryloop); 
$fv = mysql_fetch_array($categoryloop1);
}



$catloop = mysql_query("SELECT * FROM $dbcategory WHERE catid = $cat");
$catloop1 = mysql_fetch_array($catloop);
if(!isset($fv['series_name']))

echo ("<p align = \"left\"><font size = \"2\">".$catloop1['category']." &gt; Misc.</font></p>");
else
	echo ("<p align = \"left\"><font size = \"2\">".$catloop1['category']." &gt; ".$fv['series_name']."</font></p>");

echo ("<form name=\"form\">");
paging($selectname = "page");

// Load the story data in a loop

$storyloop = "SELECT * FROM $dbstory WHERE catid = $cat AND series = $subcat AND cid = 0 order by updated desc LIMIT $pgs,$u";
$storyloop1 = mysql_query($storyloop); 
while ($sl = mysql_fetch_array($storyloop1))


{
	$sid = $sl['sid'];
//Load and convert HTML tamplate tags into specified data from the DB
	require "story_layout.php";


}

paging($selectname = "page1");
echo "</form>";

?>