CODEWITHSUNDEEP
phpsqlsql-server-2008pdo
VSack
VSack

Reputation: 479

MSSQL Query Taking Expotentially Longer in PHP/PDO

I'm writing a small tool for my boss that requires me to extract records from a FedEx flat file and then grab order data from our ERP. I'm running into a serious bottleneck that is causing my queries to take longer than five seconds when run through PHP, and I'm turning to the collective for help.

App OS: LAMP Server w/ PHP 5.3.2 DB: Microsoft SQL Server 2008 R2

Once I grab an invoice, it gives me all our order numbers associated with the invoice. These are almost always non-sequential, and the bigger invoices can have up to 3,000 order numbers.

Example: SELECT o.ORDER_NO, o.TRANSACTION_ID, o.SHIP_DATE, o.SHIP_CHRG, s.TRANSACTION_ID, s.ACTUAL_WGHT, s.PACKER, s.SHIP_DEPARTMENT, s.TRACKER_ID FROM o INNER JOIN s ON o.TRANSACTION_ID = s.TRANSACTION_ID AND o.ORDER_NO IN (86965,93616,93357,89475,90252,90249,93674,94029,88340,93044,89267,87340...)

The above example has 2,744 ORDER_NOs it needs to grab. If I jump on my Windows client and use management studio, it returns results in just over half a second (274995 bytes). If I do it in PHP, its taking 5.5 to 6 seconds.

I normally use PDO dblib to access my alien databases and I've tried direct query and prepared executes with no change. I even tried the code using built in mssql functions in PHP to no effect.

Is there anything I am missing that would allow me to get this time down? My execution plans look good on the database server, so I'm leaning strongly to it being an issue with PHP.

Thanks in advance for any help you all may be able to provide!

Upvotes: 2

Views: 670

Answers (2)

VSack
VSack

Reputation: 479

Well, first off I just realized that I wrote a terrible query.

I'm doing an INNER JOIN on multiple conditionals one of which is this lousy list. Swapping the AND for a WHERE shaved the query in half.

A buddy recommended that perhaps an IN statement was being treated poorly by the dblib driver and to swap it out for a very, very long OR qualification. While it feels horrible to my senses, the query results return in under a second!

So I guess that solves this issue...for now?

Upvotes: 0

sam yi
sam yi

Reputation: 4934

i have never worked with pdo but thought you can try this. if this is the only process running, before you run the search query, upload the order list to a table. then join to that table in the search. 

insert into **order_list**
select 86965
union select 93616
union select 93357
union select 89475

or

insert into **order_list**
select 86965

insert into **order_list**
select 93616

insert into **order_list**
select 93357

then

SELECT o.ORDER_NO, o.TRANSACTION_ID, o.SHIP_DATE, o.SHIP_CHRG, s.TRANSACTION_ID,       s.ACTUAL_WGHT, s.PACKER, s.SHIP_DEPARTMENT, s.TRACKER_ID 
FROM o INNER JOIN s ON o.TRANSACTION_ID = s.TRANSACTION_ID 
INNER JOIN order_list oo ON o.ORDER_NO = oo.ORDER_NO

Upvotes: 1

Related Questions