Azure DB - Mssql - PHP mssql_fetch_assoc timing out question

Question

Did a database migration to Azure from 2008 dedicated server. It was working before and was slow but now this bit of code seems to be timing out and not sure why. If i manually run the query on Sqlserver returns about 5200 results, runtime about 1 seconds. No query errors - overview on azure dashboard doesn't seem to be going over DTU rate. If i narrow down to just a couple results that bit of code below works. Any suggestion or more information I can add here to help get this problem solved? I need need all the information from the Select *.

Edit: added more information - working on getting the plans. not sure why table formatting isn't showing right. Shows correctly in the editing view. Added photos.

query 

SELECT
    at.*, u.username as activity_username
FROM
    activity_track as at
LEFT JOIN
        ccusers u
        ON
            at.activity_userid = u.ccusersid
WHERE
    (activity_action='Edit Attorney Sales Rep' OR activity_action='Edit Attorney Referral Source')
ORDER BY
    at.activity_date DESC

activity_track table def -- row count 514347

• activity_id - int - PK
• activity_server_info - text
• activity_userid - big int
• activity_date - datetime
• activity_action_type - varchar(25)
• activity_action - varchar(50)
• activity_notes - text

ccusers -- def row count 1281807

• username - varchar(50)
• password - varchar(50)
• ccUsersid - bigint - PK
• user_type - varchar(50)
• agencies - varchar (50)
• c_date - varchar(50)
• isEmp - tinyint

ccusers

• IX_c_date nonclustered c_date

• IX_counselingagencies nonclustered counselingAgencies

• IX_isDecafemployee nonclustered isDecafEmployee

• IX_password nonclustered password

• IX_user_type nonclustered user_type

• IX_username nonclustered username

• PK_ccUsers clustered, unique, primary key ccUsersId

activity_track

• IX_activity_track nonclustered activity_date
• IX_activity_track_1 nonclustered activity_userid
• IX_activity_track_2 nonclustered activity_action_type
• IX_activity_track_3 nonclustered activity_action
• PK_activity_track clustered, unique, primary key activity_id

$result = mssql_query ( $query, $website_database_connection );
while ( $row = mssql_fetch_assoc ( $result ) )
    {
        $Activities [ ] = $row;
    }

Answer 1

Try refactoring your php program to use the sqlsrv_ API; it's the "official" way to get from php to SQL Server.

Then, let's take this optimization step by step.

First: try this simplified query.

SELECT at.activity_id
  FROM activity_track as at
 WHERE (    at.activity_action='Edit Attorney Sales Rep'
         OR at.activity_action='Edit Attorney Referral Source')
 ORDER BY at.activity_date DESC

That should be made reasonably fast by your index on activity_track (activity_action). The plan should show a nonclustered index seek with two iterations, one for each value. It looks like you're filtering about 10% of the rows in that table. If it still shows nonclustered index scan, it's possible there are so few different values in activity_action that the optimizer concludes a full scan is faster. It seems unlikely given the ~10% filtering.

Then try SELECT at.* instead of SELECT at.activity_id. You should keep most of your performance.

Finally, add back the left join to ccusers.

Pro tip: avoid SELECT * especially when you're having performance trouble. Instead, enumerate the columns you need from the table.

If you use SSMS -- Microsoft's SQL Server Management Studio -- this tip is for you: Right-click in a query window, then select Show Actual Execution Plan, then run the query. The execution plan display sometimes recommends a new index to create.