Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 1
Registered: ‎04-13-2010

Query running at startup for nearly a full minute

[ Edited ]

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
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Query running at startup for nearly a full minute

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....

RJ Samp
Bronze Super Contributor
Posts: 129
Registered: ‎06-09-2009

Re: Query running at startup for nearly a full minute

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.

 

 

Raul A. Chavez
http://raul.chavez.com
http://www.crmbi.com