November 7, 2013 at 1:23 am
I have face slow to retrieve the data from particular table.
This table frequently data inserted and same time 50 user will access this table. can you please provide the any suggestions.
SelectTop 1 Q.UniqueId FromDataQueue Q Where(((Q.field1= 1) And (Q.Status = 'ND')))
And Q.Status Not In ('A', 'B')
And 0 = (Select Count(*) From DataQueue Where ImageId = Q.ImageId And FieldId = Q.FieldId And field7= 2)
And Q.BatchPriority <> 0 And Q.field4<> 0 And Q.KeyingLocation = 'E'
Order ByQ.field2, Q.field1 Asc, Q.field3, Q.field5, Q.field4, Q.field6
November 7, 2013 at 1:29 am
Not without a lot more information. Need table definitions, index definitions, queries, execution plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2013 at 1:31 am
Select Top 1 Q.UniqueId From DataQueue Q Where (((Q.field1= 1) And (Q.Status = 'ND')))
And Q.Status Not In ('A', 'B')
And 0 = (Select Count(*) From DataQueue Where ImageId = Q.ImageId And FieldId = Q.FieldId And field7= 2)
And Q.BatchPriority <> 0 And Q.field4<> 0 And Q.KeyingLocation = 'E'
Order By Q.field2, Q.field1 Asc, Q.field3, Q.field5, Q.field4, Q.field6
November 7, 2013 at 2:09 am
This article shows you how to post enough information to be helpful to everyone that is willing to assist you:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Once enough information is supplied there will be no end of (generally) helpful advice but we do need to know more than just the text of the query.
November 11, 2013 at 9:39 am
infexthenna (11/7/2013)
Select Top 1 Q.UniqueId From DataQueue Q Where (((Q.field1= 1) And (Q.Status = 'ND')))And Q.Status Not In ('A', 'B')
And 0 = (Select Count(*) From DataQueue Where ImageId = Q.ImageId And FieldId = Q.FieldId And field7= 2)
And Q.BatchPriority <> 0 And Q.field4<> 0 And Q.KeyingLocation = 'E'
Order By Q.field2, Q.field1 Asc, Q.field3, Q.field5, Q.field4, Q.field6
Do you have Q.BatchPriority and And Q.field4 less then 0 also? And why Q.field1 Asc is written in order by?
November 11, 2013 at 2:29 pm
I believe that I'd change the following code to a NOT EXISTS instead of doing a count.
And 0 = (Select Count(*) From DataQueue Where ImageId = Q.ImageId And FieldId = Q.FieldId And field7= 2)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply