IIS - PHP - ODBC - 1gb temporary files.

[drjay]

I'm hoping someone here has run into this oddball. I'm accessing a Filemaker DB through php/odbc and running even small queries creates giant temporary files that are mostly blank spaces. For example, one query returns about 113 rows but creates a 987meg temporary file. Here's the kicker: I grabbed one of the temporary files and compressed it via 7z at default settings and it's 148k! That's slightly less than the amount of data the query is actually displaying.

 

Why does it need a gig of nearly blank space in a temporary file to display about 1 page of actual text? No images are involved.

 

Here's the code. Don't get me started on the quotes thing, the driver wouldn't have it any other way.

 

 

$sql='select "Database"."a","Database"."b","Database"."c","Database"."d","Database"."e" from "Database" where "Database"."car" like \'%ACC%\' and "Database"."SubSection" = \''.$sec.'\'';


$rs=odbc_exec($conn,$sql);

 

[drjay]

Ok, just found a clue: the size of the temporary file it creates is relative to the field (column) length of the result. For example, I created a db with two fields, decimal(15) and text. In Filemaker text comes back as varchar(10000000)....yeah.

 

So I filled 1514 records with "1.1" in both the text and decimal fields. When I select just the decimal field the temporary file is a couple meg. When I select the same data from the text field it gets up to 4gb before I stop it.

 

This explains why there's so much blank space in the temporary file. It's padding every result to its maximum size. So if I have varchar(100) and a single character, I'll have 99 spaces in it. The big issue is in this case it's apparently varchar a damn million.

 

I've only ever seen this kind of thing when converting a mysql table to heap. Anyone know how to stop it in php?

[Mchl]

If it's DBMS that does it you can hardly do anything from PHP (apart from deleting these temporary files).

[drjay]

It's almost certainly PHP doing it, and the temp files are automatically removed once the page has finished loading. It seems clear to me that PHP is putting everything into a temporary file, padding it to the column length (varchar(1000000)), and pulling out the results afterward. Using anything other than PHP with the same DSN does not create these files, only PHP.

 

Even if it left the files it wouldn't be the main problem. This happens with dozens of pages and every time it's loaded. 10 people hit the page around the same time and it's creating 10 to 40gb of temp files. That takes a good long while. I'm using page caching now but it's still a problem when I need to refresh.