Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow performance Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 1:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 8, 2013 12:33 AM
Points: 2, Visits: 5
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.


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



  Post Attachments 
plan2.png (3 views, 18.63 KB)
Post #1512130
Posted Thursday, November 7, 2013 1:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
Not without a lot more information. Need table definitions, index definitions, queries, execution plans.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1512134
Posted Thursday, November 7, 2013 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 8, 2013 12:33 AM
Points: 2, Visits: 5
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
Post #1512135
Posted Thursday, November 7, 2013 2:09 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 5:55 AM
Points: 1,483, Visits: 8,545
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.


BrainDonor
Linkedin
Blog Site
Post #1512145
Posted Monday, November 11, 2013 9:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
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?


Post #1513181
Posted Monday, November 11, 2013 2:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse