Query running at startup for nearly a full minute [ Edited ]

[cdavis]

Hey everyone,

We're experiencing a strange issue where all users are getting stuck waiting for a query to run. As we don't use SecCodeID's on tickets there is absolutely no results for this query to return (or reason to run), but it sucks up 90% of the startup time. I tried knocking it out or modifying it with the query optimizer but it still just continues to run every startup. If anybody has any ideas how to eliminate or fix this query it would be very appreciated:

Time Stamp 2010/04/13 09:44:35.453
SQL Type USER
Parse(ms) 0.0997
Prepare(ms) 0.0274
Secure(ms) 0.0074
Execute(ms) 58956.0399
GetRows(ms) 0.0076
Log(ms) 0.0000
Rows 0
UserID testUser

---------- Client SQL ---------
Select Distinct(seccode.SeccodeID) from Seccode join ticket on (ticket.assignedtoid=seccode.seccodeid) 
---------- Executed SQL ----------
Select Distinct(seccode.SeccodeID) from Seccode join ticket on (ticket.assignedtoid=seccode.seccodeid) INNER JOIN SECRIGHTS S_AA ON (S_AA.ACCESSID = 'U6UJ9A0000ZQ' AND TICKET.SECCODEID = S_AA.SECCODEID ) 

Message Edited by cdavis on 04-13-2010 07:09 AM

[RJSamp]

Welcome to SLX OLE DB Provider He double hockey sticks.

We long ago abandoned using the provider for most data retrievals/queries that don't involve SLX' connection for Add, Edit, Delete OOTB menu options on a grid or require a SECCODEID or Field Level Security 'test'. Since we don't worry about either SECCODEID or FLS, and almost always write our own Popupmenu options this means that for 99% of Our queries we use a SQL Server Native connection.

Grids involving thousands of rows now take seconds instead of 10's of minutes.......

In your specific example, isn't this a known issue where SECCODEID's are Null, or the lack of an Index on the field slows things down?

set the Ticket.AssignedToID field to  a valid SeccodeID, and index that field as well.

SLX is running this at startup for a Ticket 'Group'......take the AssignedToID column out of any groups you can....

[rchavez]

Not sure about the purpose of the Query (although it appears to be trying to determine all existing Owners of Tickets), but if this query is indeed taking that long, my advise is to review your Indexes and see how you could Improve it.

I am not sure that you could get rid of the query, instead analyze the cause for it to take that long to execute. 

On its face, it appears that it is doing some Table Scans, and you may be able to tweak your Indexes and Statistics to Improve its response Time.