Thank you everyone for all your suggestions. Gonna start testing them out.
Byron: I have this issue when running the query via management studio and via cfquery so not sure if that is relevant.. Jon: I'm still running 2005 (if it ain't broke...), but was also thinking about trying READ UNCOMMITTED (maybe that would have the same effect as your suggestion?) Mark: The select is only returning a couple of columns (date/int) and no text.. I'll check the activity monitor.. the execution plan shows the index usage and doesn't appear to account for the delays... Jeff: Thanks for the Queries, I'll try them! Whohoo! Cftalk is alive!! Brook -----Original Message----- From: Byron Mann [mailto:byronos...@gmail.com] Sent: December-05-13 10:22 AM To: cf-talk Subject: Re: Simple SQL Query sometimes really Slow? Could never figure this out, but we had a similar issue on 2005 with a date time column. I remember we changed from a cfquery to a stored procedure and it was resolved. Byron Mann Lead Engineer & Architect HostMySite.com On Dec 5, 2013 12:27 PM, "Brook Davies" <cft...@logiforms.com> wrote: > > This may not be the right place to post this (man, CF-TALK has changed > a lot in the last 5 or so years ;)). > > > > I have a simple SQL query that is showing up as running slow. When I > run it via the Management Studio it is sometimes fast 0.1 seconds and > sometimes, seemingly randomly slow 1.5 minutes!). Other queries on > other tables are executing normally. This table only has 50k records > and even a simple query is sometimes really slow. > > > > The query that runs slow is as simple as > > > > select commitDate,id from databaseChangeLog > > where usr_id = 62622 and form_id = 312468 > > and commitDate > '2013-12-04 11:00:05.0' > > > > But is just as slow without the date part. The table has a clustered > index on the primary key (id) and a non-clustered index on > usr_id,form_id and commitDate. The index doesn't seem to make any difference. > > > > My guess is the table is locked. My question is: > > > > How can I determine if it is locked? What would be locking it. I > checked all my code and there are no CFTRANSACTIONS or ISOLATED READS > or anything like that. There are some inserts and the table has 2 TEXT > columns which are being updated at times with fairly large values. But > the only queries reported as slow are these simple SELECTS. The query > execution plan uses the non-clustered index on (usr_id,form_id and > commitDate). > > > > I'm just at a loss as to why this specific query is sometimes so slow.. > where to look? > > > > Brook > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
