Slow performance

Question

Slow performance

infexthenna

SSC Rookie

Points: 28
More actions
November 7, 2013 at 1:23 am

#300819

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

infexthenna SSC Rookie Points: 28 More actions · Answer 2

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

BrainDonor SSCoach Points: 19252 More actions · Answer 3

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.

Steve Hall
Linkedin
Blog Site

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 4

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?

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)