Query won't run, is this the optimal syntax?

[Lurk]

SELECT     Account.Acc_Reference, Account.Acc_Description, Acc_Template.AT_Description, Account_Balances.AB_Actual, Account_Balances.AB_Budget,
                      Account_Balances.AB_On_Order, Journal_Item.JI_Amount
FROM         Account INNER JOIN
                      Account_Balances ON Account.Acc_Id = Account_Balances.Acc_Id INNER JOIN
                      Acc_Template ON Account.AT_Id = Acc_Template.AT_Id INNER JOIN
                      Journal_Item ON Account.Acc_Id = Journal_Item.Acc_Id
WHERE     (Account_Balances.FY_Id = '7') AND (Acc_Template.AT_Description IN ('Expenses', 'Capital works in progress', 'Assets'))

Why won't this query run in PHP? it works in Enterprise Manager(SQL2000), but times out in a PHP script... there's no problem running other queries on the DB, just this one... thanks.

[Lurk]

bump

Can I structure this query any better? Anyone?

[tomhath]

Query looks okay to me. I'd check what indexes are on the tables in SQLServer and see if the same ones are there in mysql.

[Lurk]

Originally posted by tomhath
Query looks okay to me. I'd check what indexes are on the tables in SQLServer and see if the same ones are there in mysql.

Sorry, I don't follow. How does MySQL come into it? 😕

[tomhath]

Opps, misread the original post (somehow I saw mysql where you said php).

What do you see with Profiler turned on in SQLServer? Is it running the query with the parameters you expected?

[Lurk]

I get:

Audit login
Existing connection(15 reads, 0 writes)
exec sp_server_info 18(6 reads, 0 writes)
use <database name>(10 reads, 0 writes)

That mean anything? Haven't used the profiler before. 🙂

[Lurk]

bump

[Lurk]

Can anyone shed some light on this?

[[deleted]]

If you JOIN to get just one value, you can also use a subquery, that will be a lot faster.

[Lurk]

Originally posted by vincente
If you JOIN to get just one value, you can also use a subquery, that will be a lot faster.

Ok thanks. Since there are two tables in the select statement that are only being used to get one column(acc_template and journal_item), how would the query look with subqueries?

The main question is: would this solve the problem on the sql server not running the query? What does the result from Profiler mean?

[[deleted]]

Well, you're speaking of a 'timeout', so I'm guessing that the query takes too long to execute and PHP gives up waiting for it to complete.

it should look somehting like this if you change the AB_Actual to a subquery:

SELECT Account.Acc_Reference,
Account.Acc_Description,
Acc_Template.AT_Description,
(SELECT Account_Balances.AB_Actual FROM Account_Balances WHERE Account.Acc_Id = Account_Balances.Acc_Id) AS AB_Actual,
Account_Balances.AB_Budget,
Account_Balances.AB_On_Order,
Journal_Item.JI_Amount
FROM Account
Acc_Template ON Account.AT_Id = Acc_Template.AT_Id INNER JOIN
Journal_Item ON Account.Acc_Id = Journal_Item.Acc_Id
WHERE (Account_Balances.FY_Id = '7') AND (Acc_Template.AT_Description IN ('Expenses', 'Capital works in progress', 'Assets'))