This topic is locked

Window Opens really Slow

2/13/2018 8:40:17 AM
PHPRunner General questions
S
swanside author

I have a dilemma, where I have a table called job with the current SQL fields

SELECT

Order_Date,

AttendBy,

End_Date,

File_No,

Job_No,

CustomerId,

CustomerId AS customerPK,

CustomerEmail,

Contract,

Job_Description,

VAT,

Invoice_Printed,

Invoice_Printing_Date,

Invoice_Tax_Date,

Payment_Received,

Job_Finished,

CustomerRef,

Payment_Due_date,

Payment_Date,

Note,

EngineerId,

EngineerId AS PDAAllocation,

EngineerId AS GasId,

LocationId,

LocationId AS locationPK,

LocationId AS SiteEmailAddress,

LocationId AS SitePhoneNumber,

JobTypeId,

Exported,

Logged_By,

ContactEmail,

PhoneNumber,

ResponseCatagories,

CallerName,

CustomerName,

JobSheet,

OrderSiteAddress,

Engineer_Note,

AutoExport,

Line1,

Line2,

Line3,

PostCode,

SLine1,

SLine2,

SLine3,

SPostCode,

Line4,

Line5,

SLine4,

SLine5

FROM job

ORDER BY Job_No DESC


After this I have a Custom View called Invoice Preview whos SQL is this.

SELECT

Job_No,

Job_No AS Invoice,

Job_No AS BasicInvoice,

Job_No AS FullInvoice,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(TotalLineValue),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

IFNULL((SELECT Labsubtotal+Matsubtotal), 0) AS Subtotal,

IFNULL((SELECT Subtotal*VAT/100), 0) AS VATAmount,

IFNULL((SELECT Subtotal+VATAmount), 0) AS Total

FROM job

ORDER BY Job_No DESC



So on the job list clicking on Invoice Preview will show 3 fields Invoice, Basic Invoice and Full Invoice which are set as custom and have this code

if ($value==$data["Job_No"]) $value = "<a target=_blank href=\"Basic_Invoice_view.php?editid1=".$data["Job_No"]."\">".$value."</a>";



These are all custom views again,

Basic Invoice and Invoice display -

SELECT

Invoice_Tax_Date,

Job_No,

Contract,

CustomerRef,

Order_Date,

File_No,

VAT,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(TotalLineValue),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

IFNULL((SELECT Labsubtotal+Matsubtotal), 0) AS Subtotal,

IFNULL((SELECT Subtotal*VAT/100), 0) AS VATAmount,

IFNULL((SELECT Subtotal+VATAmount), 0) AS Total,

CustomerName,

Line1,

Line2,

Line3,

PostCode,

OrderSiteAddress,

SLine1,

SLine2,

SLine3,

SPostCode

FROM job


And full Invoice show -

SELECT

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(TotalLineValue),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

IFNULL((SELECT Labsubtotal+Matsubtotal), 0) AS Subtotal,

IFNULL((SELECT Subtotal*VAT/100), 0) AS VATAmount,

IFNULL((SELECT Subtotal+VATAmount), 0) AS Total,

IFNULL((select sum(Working_Hrs) from labour where Job_No = job.Job_No), 0) AS JSH,

Invoice_Tax_Date,

Job_No,

Job_No AS Materials,

Contract,

CustomerRef,

Order_Date,

File_No,

VAT,

CustomerName,

Line1,

Line2,

Line3,

PostCode,

OrderSiteAddress,

SLine1,

SLine2,

SLine3,

SPostCode

FROM job


Now when you click on the Job Number in the Invoice field, a new window opens but it takes about 20 seconds to open the page. Once the page is open if you copy and paste the address URL into a new window, it will open right away.

If I run the same query in phpMyAdmin again it opens within a second, so why does it take so long on a PHP page?
It is running on a Windows SBS2008 under IIS7 and php5

The same happens if I run it on WAMP with php5 and also on a ubuntu machine with php5.

I first thought it was the php version, but I dont have the same problem when running phpMyAdmin, so any pointers on what to look for will be great.

Thanks

Paul.

admin 2/13/2018

This kind of question is impossible to answer by just looking at your SQL code.

S
swanside author 2/13/2018



This kind of question is impossible to answer by just looking at your SQL code.


<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=84350&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' /> OK But it should not make any difference if I have a table and 4 custom views all linked to the one table speed wise would it?

If not I might have to send you a project file and see if you can debug it for me and let me know a cost.
Cheers

Paul.

admin 2/13/2018

Posting to Demo Account and seeing if this error can be reproduced there should be your first step of troubleshooting no matter what.

S
swanside author 2/18/2018

Cheers

I will post a light version today.

Thanks

A
asawyer13DevClub member 2/20/2018

If you are using MySQL you might want to make sure slow query logging is turned on and see if the query is not using an index that you are expecting it to.

S
swanside author 2/20/2018



If you are using MySQL you might want to make sure slow query logging is turned on and see if the query is not using an index that you are expecting it to.



Cheers Alan.

Will give that a try also.

Thanks