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" <[email protected]> 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:357294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
