Styjun

What happens when redirecting with 3>&1 1>/dev/null?

Is it OK to look at the list of played moves during the game to determine the status of the 50 move rule?

Why is this integration method not valid?

If a character has cast the Fly spell on themselves, can they "hand off" to the Levitate spell without interruption?

why "American-born", not "America-born"?

Ribbon Cable Cross Talk - Is there a fix after the fact?

Can someone get a spouse off a deed that never lived together and was incarcerated?

Anatomically correct Guivre

How do I write real-world stories separate from my country of origin?

What defines a person who is circumcised "of the heart"?

Team member is vehemently against code formatting

Is ideal gas incompressible?

How to make Flex Markers appear in Logic Pro X?

Shell builtin `printf` line limit?

Nunc est bibendum: gerund or gerundive?

Why is unzipped file smaller than zipped file

Why the work done is positive when bringing 2 opposite charges together?

"Official wife" or "Formal wife"?

Why is Ni[(PPh₃)₂Cl₂] tetrahedral?

Is being an extrovert a necessary condition to be a manager?

How could the B-29 bomber back up under its own power?

Efficient Algorithms for Destroyed Document Reconstruction

Managing heat dissipation in a magic wand

How to test if argument is a single space?

Adding an Index to my table doesn't seem to be improving search performance - why is this?

Does Foreign Key improve query performance?Why use the INCLUDE clause when creating an index?Improve INSERT-per-second performance of SQLite?How to improve performance of non-scalar aggregations on denormalized tablesIs it a good idea to always define a clustered index on a database table?Improve SQL CTE query performanceWhy does changing 0.1f to 0 slow down performance by 10x?Join table index performance improvements concernsStored Proc /Table Index ImprovementWhy can't see enum-type index in explain query?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have the following on my Property class:

 [Index]
 public Status PropertyStatus get; set; 

Where Status is an enum containing one of five integer values, ranging from 0-4 inclusive.

When I run the following query:

SELECT TOP 100 * FROM [PROPERTY] WHERE PROPERTYSTATUS = 1

The query takes up to five minutes with only 30,000 rows.

I can't profile the query because the database is in Azure SQL. I do see the index:

What else can I do to troubleshoot / optimize this query?

UPDATE:

My schema is managed by EF Code-First, with the Property class looking like this:

public class Property
 [Key]
 public int PropertyId get; set;
 [Index(IsClustered = true)]
 public Status PropertyStatus get; set; 
 [Index]
 public bool InitiatedByUser get; set; 

With the clustered index I do see improved performance.

sql sql-server performance indexing azure-sql-database

share|improve this question

edited Mar 24 at 2:48

user666

asked Mar 23 at 20:55

user666user666

5901716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Try optimizing with a Clustered index..CREATE CLUSTERED INDEX IX_status ON PROPERTY(status) instead.
  
  – Raymond Nijland
  Mar 23 at 21:02
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RaymondNijland would you mind explaining why you're making this recommendation? I've read about this alternative but I'm not sure why this would improve SELECT statement performance.
  
  – user666
  Mar 23 at 21:06
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Post your CREATE TABLE and CREATE INDEX DDL as code instead of a screen image. The optimal index for this query would be either a clustered one on PROPERTYSTATUS or a non-clustered index on PROPERTYSTATUS with all other columns included. Be aware that the behavior of TOP without ORDER BY is not defined; you might get any 100 rows that satisfy the criterial.
  
  – Dan Guzman
  Mar 23 at 21:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please also post your reservation size in SQL Azure (Basic, S1, S2, etc). That limits your memory, CPU, and IO. If possible, post the query plan for your query since that will give much more insight into why it is taking time to return results. The short answer is that doing single column secondary indexes forces bookmark lookups into the heap/clustered index and these are generally more expensive than having a covering index to retrieve all columns (select top 100 * …). If you just retrieve the column of interest with current schema, it should be faster.
  
  – Conor Cunningham MSFT
  Mar 24 at 0:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ConorCunninghamMSFT How can I provide you with my query plan when running from SSMS?
  
  – user666
  Mar 24 at 2:43
  

  
  
  
  

 | 
show 5 more comments

0

I have the following on my Property class:

 [Index]
 public Status PropertyStatus get; set; 

Where Status is an enum containing one of five integer values, ranging from 0-4 inclusive.

When I run the following query:

SELECT TOP 100 * FROM [PROPERTY] WHERE PROPERTYSTATUS = 1

The query takes up to five minutes with only 30,000 rows.

I can't profile the query because the database is in Azure SQL. I do see the index:

What else can I do to troubleshoot / optimize this query?

UPDATE:

My schema is managed by EF Code-First, with the Property class looking like this:

public class Property
 [Key]
 public int PropertyId get; set;
 [Index(IsClustered = true)]
 public Status PropertyStatus get; set; 
 [Index]
 public bool InitiatedByUser get; set; 

With the clustered index I do see improved performance.

sql sql-server performance indexing azure-sql-database

share|improve this question

edited Mar 24 at 2:48

user666

asked Mar 23 at 20:55

user666user666

5901716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Try optimizing with a Clustered index..CREATE CLUSTERED INDEX IX_status ON PROPERTY(status) instead.
  
  – Raymond Nijland
  Mar 23 at 21:02
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RaymondNijland would you mind explaining why you're making this recommendation? I've read about this alternative but I'm not sure why this would improve SELECT statement performance.
  
  – user666
  Mar 23 at 21:06
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Post your CREATE TABLE and CREATE INDEX DDL as code instead of a screen image. The optimal index for this query would be either a clustered one on PROPERTYSTATUS or a non-clustered index on PROPERTYSTATUS with all other columns included. Be aware that the behavior of TOP without ORDER BY is not defined; you might get any 100 rows that satisfy the criterial.
  
  – Dan Guzman
  Mar 23 at 21:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please also post your reservation size in SQL Azure (Basic, S1, S2, etc). That limits your memory, CPU, and IO. If possible, post the query plan for your query since that will give much more insight into why it is taking time to return results. The short answer is that doing single column secondary indexes forces bookmark lookups into the heap/clustered index and these are generally more expensive than having a covering index to retrieve all columns (select top 100 * …). If you just retrieve the column of interest with current schema, it should be faster.
  
  – Conor Cunningham MSFT
  Mar 24 at 0:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ConorCunninghamMSFT How can I provide you with my query plan when running from SSMS?
  
  – user666
  Mar 24 at 2:43
  

  
  
  
  

 | 
show 5 more comments

I have the following on my Property class:

 [Index]
 public Status PropertyStatus get; set; 

Where Status is an enum containing one of five integer values, ranging from 0-4 inclusive.

When I run the following query:

SELECT TOP 100 * FROM [PROPERTY] WHERE PROPERTYSTATUS = 1

The query takes up to five minutes with only 30,000 rows.

I can't profile the query because the database is in Azure SQL. I do see the index:

What else can I do to troubleshoot / optimize this query?

UPDATE:

My schema is managed by EF Code-First, with the Property class looking like this:

public class Property
 [Key]
 public int PropertyId get; set;
 [Index(IsClustered = true)]
 public Status PropertyStatus get; set; 
 [Index]
 public bool InitiatedByUser get; set; 

With the clustered index I do see improved performance.

sql sql-server performance indexing azure-sql-database

share|improve this question

edited Mar 24 at 2:48

user666

asked Mar 23 at 20:55

user666user666

5901716

 [Index]
 public Status PropertyStatus get; set; 

SELECT TOP 100 * FROM [PROPERTY] WHERE PROPERTYSTATUS = 1

public class Property
 [Key]
 public int PropertyId get; set;
 [Index(IsClustered = true)]
 public Status PropertyStatus get; set; 
 [Index]
 public bool InitiatedByUser get; set; 

sql sql-server performance indexing azure-sql-database

sql sql-server performance indexing azure-sql-database

share|improve this question

edited Mar 24 at 2:48

user666

asked Mar 23 at 20:55

user666user666

5901716

share|improve this question

edited Mar 24 at 2:48

user666

asked Mar 23 at 20:55

user666user666

5901716

asked Mar 23 at 20:55

user666user666

5901716

asked Mar 23 at 20:55

user666user666

5901716