性能问题 - 从Navision获取数据到PHP API

Question

I'm currently creating an API that has access to our Navision system. I'm pretty new to this, but I only added some functionality to an already existing system. The thing is:

UPDATE 2:

I think the version of Navision is irrelevant, since I'm querying the MSSQL database attached directly. Maybe the version of the MSSQL DB could be relevant:

Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

UPDATE:

I have already iterated over the CSV file and also the the search query is done every time a get a new email, since it cannot call the MSSQL server with an IN() statement with 70.000 emails in it.

I have also only opened the csv file for writing once instead of on every request.

Completed:

• I have a csv file with a lot of emails (done)
• This should be uploaded (done)
• The uploaded file should then be parsed for emails (done)
• The emails should then be matched up with the ones in Navision (done)

Problem:

• It takes a lot of time doing this
• Navision turns slow and there is a lot of people depending on that system on a daily basis.

Info:

• There are approx. 70.000 emails in the CSV file (and counting)
• We have approx. 1 million customers in Navision
• This script is supposed to run daily.
• Currently there is no joins in the SQL statement (this will come)

Do any of you have any suggestions on how to optimize the process?

Any help will be appreciated.

// Ulrik McArdle

Answer 1

Without knowing the details of how you've implemented the process it is impossible to talk specifics. But, here are a couple of common pitfalls to avoid:

1) When writing a script to parse a large text CSV text file, do so line by line. Do not try to load the file into memory - into a gargantuan array or some such. This is hugely inefficient. Rather, open the file and iterate over it line by line using something like a while loop and fgetcsv(), process that line, throw it away and load another.

2) Make the RDBMS - MySQL, Oracle, Postgres, whatever - handle the heavy work. PHP is not the place to process the data. Optimise the SQL statements, in other words. I would assume that the "Navision turns slow" bit would best attended by attention to the SQL.