300+ Inputs Frm File to Gen. List/Timing

[Anon]

Hi,

What is the best way to go about taking a list of product ID numbers, from a file: data.txt, which can have 300-1000 product IDs, and putting each ID number through a series of queries to generate a special price file.

Here is what I am using right now:

$FileName = "data.txt";

if (!file_exists($FileName))
echo "File Not Found??";

@ $dataFile = fopen($FileName, "r");
set_time_limit(1000);
while (!feof($dataFile)) {
$getData = fgets($dataFile, 8);

...Then there are about 6-sql statements and 10-echo statements...

The problem I have with this setup is - set_time_limit(1000);. With this coding setup I need more 1000 seconds to complete 300+. Maybe I am not doing something right? Or is this normal for what I am try to accomplish.

Thanks In Advance!

[[deleted]]

I can't tell wether it's normal,
you are trying to run 6 queries 300 times.
If those queries are against a database of two records, I'd be worried.
If they are against a database of 45 million records, I wouldn't be surprised.

What kind of queries do you run, and on what kind of tables? (size, content etc)

[Anon]

Well, the database has 5 different tables - three tables have about 110,000 records, and 2 have about 250.

The first query also gets some related fields that are in all of the tables. And that information is used in the other 5 queries. Here is some more of the coding:

$charge = (1 + ($percent/100));
$FileName = "data.txt";

if (!file_exists($FileName))
echo "File Not Found??";

@ $dataFile = fopen($FileName, "r");
set_time_limit(1000);
while (!feof($dataFile)) {
$getData = fgets($dataFile, 8);

$sql = "SELECT descr, cost*$charge as newcost, part_num, vend_code, manu_part, qty, grp, cat, sub FROM products WHERE part_num LIKE $getData";

$result = mysql_query("$sql");
while ($row = mysql_fetch_array($result)) {

$sql2 = "SELECT descr as descr2 FROM comcat WHERE grp LIKE '$row[grp]' and cat LIKE '$row[cat]'";

...

$sql4 = "SELECT vend_name FROM manuf WHERE vend_code LIKE '$row[vend_code]'";

....

Just to let you know, I am only a rookie at php and at computer science. In this coding I used an Array, but it looks like to me I don't need to use one. What do you think? I don't think that would have an affect on the execution time of these queries.

Also, I am running these queries on an AMD-400mhz system with 192mb of memory on Windows 98se & Apache for windows. I know a faster processor would help out a little, what about using Mandrake linux operating system?

Thanks!

[[deleted]]

First, using Linux is nearly allways faster than windows. But that's probably not the problem here.

In your queries you are using the LIKE operator a lot, but without a real search term.

Normally a LIKE query would look like this:
SELECT *
FROM table
WHERE col1 LIKE '%find me%'

This will return all rows where col1 contains 'find me' anywhere in the data.
so it will find 'hello, I dare you to find me' and 'find me if you can'

In your case you do:
SELECT *
FROM table
WHERE col1 LIKE 'find me'
which will only find records where col1 contains exactly 'find me'
In that case, it's much faster to use the '=' operator:

SELECT *
FROM table
WHERE col1 = 'find me'

Also, you don't have to put quotes around numerical values:

SELECT *
FROM table
WHERE col1 = 2;

not even if the 2 is put in there using a variable:

SELECT *
FROM table
WHERE col1 =$iMyValue

Third, making things a bit more complex to newbies:
Indexes.
Your database can use indexes just like you use the index of a book to quickly find which page some information is on.
Grab the mysql manual from www.mysql.com and read the section on indexes to get more information.

Indexes are_great speed for performance.
if you don't use indexes, then every query will force a 'sequencial scan'.
That means that for every query, the DBMS is forces to read all_ rows in the table, even if it only needs to find one row.
As you can imagine, that slows you down drastically.

Try to create indexes on all columns that you query, preferably on combinations of columns that are also combined in the query:

SELECT *
FROM table
WHERE col1 = 2 AND col2=65;

Here you'd put an index on col1 and col2 together.

[Anon]

Vincent, Thanks for the all input.

I'll make the changes as you suggested, and read-up on Indexes. I'll let you know how much of an improvement this makes. I'm sure using Indexes will greatly cut down on the time it takes to generate the queries.

Thanks Again!!