Indexing question

%3CLINGO-SUB%20id%3D%22lingo-sub-2928779%22%20slang%3D%22en-US%22%3EIndexing%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2928779%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20about%20700%2C000%2B%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20quite%20wide%20with%20some%20large%20columns.%20It%20contains%20product%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20has%20an%20auto-incrementing%20id%20column%20which%20is%20the%20PK%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20also%20has%20a%20column%20called%20ManufacturerMarking%20which%20is%20nvarchar(100)%2C%20not%20unique%20(but%20almost)%20and%20a%20column%20called%20%5BSupplier%20Code%5D%20which%20is%20integer%2C%20contains%20a%204%20digit%20number%20also%20not%20unique%20but%20the%20combination%20of%20those%20two%20columns%20is%20definitely%20unique.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20view%20based%20on%20this%20table%20that%20calculates%20some%20stuff%20and%20joins%20with%20a%20suppliers%20table%20on%20the%20supplier%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20select%20the%20top%20100%20rows%20of%20this%20view%20the%20query%20is%20almost%20instant%2C%20which%20is%20great.%20However%2C%20the%20business%20wants%20to%20order%20by%20ManufacturerMarking.%20If%20I%20sort%20the%20table%20by%20that%20column%20the%20same%20query%20takes%20about%207%20seconds%20(running%20on%20an%20Azure%20S4).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20leaving%20it%20up%20to%20Azure%20SQL%20to%20manage%20the%20non-clustered%20indexes.%20It's%20created%20a%20couple%20automatically%20which%20I%20can%20show%20here%20if%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20is%20being%20surfaced%20in%20Power%20Apps%20and%20also%20via%20an%20API%20I%20have%20created%20in%20an%20Azure%20function.%20In%20Power%20Apps%20the%20users%20can%20filter%20by%20created%20date%20(datetime2)%2C%20supplier%20name%20or%20via%20a%20search%20across%20a%20bunch%20of%20columns.%20These%20get%20passed%20in%20via%20the%20Power%20Platform%20SQL%20connector%2C%20which%20is%20a%20REST%20API%20that%20converts%20the%20Odata%20queries%20into%20a%20SELECT%20statement%20which%20performs%20quite%20well%20unless%20I%20try%20to%20send%20the%20order%20by%20clause%20with%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20API%20doesn't%20allow%20sorting%20the%20data%2C%20but%20if%20I%20alter%20the%20view%20to%20sort%20the%20source%20table%20by%20ManufactureMarking%2C%20I%20see%20the%20same%20performance%20hit%20as%20if%20I%20send%20in%20the%20Order%20by%20clause%20via%20the%20Odata%20query%20from%20Power%20Apps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20I%20need%20to%20do%20with%20the%20indexes%20to%20improve%20the%20performance%20of%20this%20table%2Fview%20when%20ordered%20by%20the%20ManufactureMarking%3F%20I%20looked%20into%20changing%20the%20clustered%20index%20of%20the%20table%20to%20ManufactureMarking%20but%20I'd%20have%20to%20drop%20the%20clustered%20index%20off%20the%20PK%2C%20and%20create%20one%20for%20this%20non-unique%20column.%20It%20all%20seems%20a%20bit%20daunting%20and%20maybe%20not%20advisable%20to%20do%20that.%20I%20don't%20want%20to%20accidentally%20drop%20or%20truncate%20the%20table%20(yes%20I%20do%20have%20backups%20but%20the%20outage%20would%20be%20embarrassing)%20or%20impact%20the%20performance%20in%20unexpected%20ways.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2928779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBusiness%20Apps%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Warehouse%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2947546%22%20slang%3D%22en-US%22%3ERe%3A%20Indexing%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2947546%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628375%22%20target%3D%22_blank%22%3E%40williampage%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%20Will.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20I've%20interpreted%20everything%20correctly%2C%20but%20I've%20got%20to%20start%20somewhere%20so%20here%20goes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%2C%20if%20this%20table%20is%20part%20of%20a%20larger%20system%20then%20you%20most%20definitely%20don't%20want%20to%20mess%20around%20with%20the%20clustered%20index%20on%20the%20primary%20key.%20That%20will%20cause%20performance%20issues%20elsewhere%20-%20you%20can%20almost%20guarantee%20it%20since%20any%20foreign%20key%20references%20back%20to%20this%20table%20will%20be%20referring%20to%20the%20primary%20key.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20table%20is%20more%20isolated%2C%20meaning%20few%20foreign%20key%20dependencies%20on%20its%20primary%20key%20and%20infrequently%20featuring%20in%20JOIN-oriented%20queries%20that%20use%20the%20primary%20key%20in%20the%20%22ON%22%20clause%2C%20then%20you%20may%20get%20away%20with%20changing%20the%20clustered%20key%20from%20the%20current%20primary%20key%20to%20the%26nbsp%3BManufacturerMarking%2C%20but%20I'd%20be%20very%20surprised%20if%20this%20is%20the%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20be%20clear%2C%20dropping%20an%20index%20will%20not%20delete%20your%20data.%20It%20will%20probably%20kill%20performance%20until%20you%20re-create%20some%20meaningful%20replacement%20but%20you%20won't%20lose%20your%20data.%20I%20do%20prefer%20using%20T-SQL%20for%20DROP%20commands%20since%20you%20really%20have%20to%20not%20be%20paying%20attention%20to%20type%20something%20like%20%22DROP%20TABLE%22%20when%20you%20mean%20%22DROP%20INDEX%22.%20Even%20if%20you%20type%20%22DROP%20INDEX%20%3CYOURTABLENAME%3E%22%2C%20that's%20simply%20going%20to%20throw%20an%20error%20and%20fail.%20But%20if%20the%20GUI%20is%20your%20preferred%20approach%2C%20just%20be%20careful%20you're%20right-clicking%20the%20right%20thing!%3C%2FYOURTABLENAME%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20the%20%22easy%22%20advice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20respect%20to%20the%20rest%2C%20the%20first%20thing%20to%20check%20is%20that%20a%20non-clustered%20index%20exists%20for%20just%20the%20ManufacturerMarking%20column%20-%20no%20extra%20columns%20included%20(that's%20assuming%20you're%20not%20using%20a%20WHERE%20clause%20to%20filter%20the%20table's%20rows).%20I%20have%20to%20point%20out%20that%20I'm%20going%20with%20%22non-clustered%22%20as%20a%20best%20guess%20on%20how%20the%20wider%20system%20might%20hang%20together%2C%20as%20we%20only%20have%20a%20small%20window%20into%20the%20bigger%20picture%20here%20so%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20surface%2C%20what%20you've%20described%20does%20meet%20%3CEM%3Esome%3C%2FEM%3E%20of%20the%20criteria%20outlined%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsql-server-index-design-guide%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20for%20a%20clustered%20index%20but%20as%20I%20just%20mentioned%2C%20if%20it%20truly%20is%20the%20beating%20heart%20of%20many%20relationships%20then%20I'd%20be%20willing%20to%20bet%20the%20data%20should%20be%20kept%20in%20the%20ordering%20it%20currently%20is.%20And%20this%20speaks%20to%20one%20key%20difference%20between%20clustered%20and%20non-clustered%3A%20%3CEM%3Eclustered%20indexes%20result%20in%20the%20%3CSTRONG%3Erow%20data%3C%2FSTRONG%3E%20being%20shifted%20around%20so%20that%20each%20index%20key%20is%20stored%20sequentially%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHypothetically%2C%20if%20nothing%20else%20has%20formal%20relationships%20with%20this%20table%20and%20you%20can%20use%20a%20clustered%20index%2C%20then%20dropping%20the%20existing%20clustered%20index%20and%20creating%20a%20new%20one%20against%20the%26nbsp%3BManufacturerMarking%20would%20mean%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EThe%20data%2Frows%20wold%20be%20stored%20in%20alphabetical%20order%20based%20on%20the%20ManufacturerMarking%20column%3B%3C%2FLI%3E%3CLI%3EYou%20would%20no%20longer%20need%20an%20ORDER%20BY%20clause%20in%20your%20view%20definition%20since%20ordering%20would%20no%20longer%20be%20necessary.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EBut%20again%20-%20and%20I%20keep%20coming%20back%20to%20this%20-%20I'd%20be%20surprised%20if%20the%20table%20is%20really%20that%20isolated.%20Only%20you%20can%20figure%20that%20out%20through%20inspecting%20the%20dependencies%20(things%20like%20foreign%20keys%2C%20views%2C%20stored%20procedures%2C%20functions%2C%20etc.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGetting%20back%20on%20track%2C%20if%20you%20have%20got%20an%20existing%20non-clustered%20index%20targeting%20only%20the%26nbsp%3BManufacturerMarking%20column%2C%20then%20there%20may%20well%20be%20not%20much%20more%20you%20can%20do%20-%20particularly%20as%20you%20said%20it's%20a%20very%20wide%20table%20(makes%20me%20wonder%20if%20you%20can't%20use%20a%20view%20to%20cut%20down%20the%20columns%2C%20which%20will%20help).%20But%20you%20can%20run%20the%20following%20superficial%20T-SQL%20check%20just%20to%20get%20some%20certainty%20around%20whether%20the%20bottleneck%20is%20definitely%20in%20SQL%2C%20or%20external%20to%20SQL.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20these%20two%20queries%20complete%20in%20roughly%20the%20same%20time%2C%20then%20your%20issue%20isn't%20the%20SQL%20instance%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20the%20schema%20name%20be%20%22dbo%22%20and%20table%20name%20be%20%22Products%22%2C%20since%20I%20don't%20know%20either%20(and%20it's%20not%20important%20that%20I%20do)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--%20Query%201%0ASELECT%20COUNT(*)%20AS%20%5BCount%5D%20FROM%20dbo.Products%20AS%20%5Bp%5D%20WITH%20(nolock)%0AGO%0A%0A--%20Query%202%0ASELECT%20COUNT(*)%20AS%20%5BCount%5D%20FROM%20dbo.Products%20AS%20%5Bp%5D%20WITH%20(nolock)%20ORDER%20BY%20p.ManufacturerMarking%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20query%202%20still%20takes%20around%207%20second%20then%20the%20bottleneck%20is%20definitely%20within%20the%20SQL%20instance%2C%20and%20at%20this%20stage%20based%20on%20what%20we%20know%2C%20I%20don't%20have%20any%20better%20suggestions%20other%20than%20the%20generalised%20indexing%20overview%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello.

 

I have a table with about 700,000+ rows.

 

It's quite wide with some large columns. It contains product information.

 

The table has an auto-incrementing id column which is the PK

 

It also has a column called ManufacturerMarking which is nvarchar(100), not unique (but almost) and a column called [Supplier Code] which is integer, contains a 4 digit number also not unique but the combination of those two columns is definitely unique.

 

I have a view based on this table that calculates some stuff and joins with a suppliers table on the supplier code.

 

If I select the top 100 rows of this view the query is almost instant, which is great. However, the business wants to order by ManufacturerMarking. If I sort the table by that column the same query takes about 7 seconds (running on an Azure S4).

 

I'm currently leaving it up to Azure SQL to manage the non-clustered indexes. It's created a couple automatically which I can show here if needed.

 

The data is being surfaced in Power Apps and also via an API I have created in an Azure function. In Power Apps the users can filter by created date (datetime2), supplier name or via a search across a bunch of columns. These get passed in via the Power Platform SQL connector, which is a REST API that converts the Odata queries into a SELECT statement which performs quite well unless I try to send the order by clause with it.

The API doesn't allow sorting the data, but if I alter the view to sort the source table by ManufactureMarking, I see the same performance hit as if I send in the Order by clause via the Odata query from Power Apps.

 

What do I need to do with the indexes to improve the performance of this table/view when ordered by the ManufactureMarking? I looked into changing the clustered index of the table to ManufactureMarking but I'd have to drop the clustered index off the PK, and create one for this non-unique column. It all seems a bit daunting and maybe not advisable to do that. I don't want to accidentally drop or truncate the table (yes I do have backups but the outage would be embarrassing) or impact the performance in unexpected ways.

 

1 Reply

@williampage 

 

Hey, Will.

 

I'm not sure I've interpreted everything correctly, but I've got to start somewhere so here goes.

 

Firstly, if this table is part of a larger system then you most definitely don't want to mess around with the clustered index on the primary key. That will cause performance issues elsewhere - you can almost guarantee it since any foreign key references back to this table will be referring to the primary key.

 

If the table is more isolated, meaning few foreign key dependencies on its primary key and infrequently featuring in JOIN-oriented queries that use the primary key in the "ON" clause, then you may get away with changing the clustered key from the current primary key to the ManufacturerMarking, but I'd be very surprised if this is the case.

 

Just to be clear, dropping an index will not delete your data. It will probably kill performance until you re-create some meaningful replacement but you won't lose your data. I do prefer using T-SQL for DROP commands since you really have to not be paying attention to type something like "DROP TABLE" when you mean "DROP INDEX". Even if you type "DROP INDEX <yourTableName>", that's simply going to throw an error and fail. But if the GUI is your preferred approach, just be careful you're right-clicking the right thing!

 

That's the "easy" advice.

 

With respect to the rest, the first thing to check is that a non-clustered index exists for just the ManufacturerMarking column - no extra columns included (that's assuming you're not using a WHERE clause to filter the table's rows). I have to point out that I'm going with "non-clustered" as a best guess on how the wider system might hang together, as we only have a small window into the bigger picture here so far.

 

On the surface, what you've described does meet some of the criteria outlined here for a clustered index but as I just mentioned, if it truly is the beating heart of many relationships then I'd be willing to bet the data should be kept in the ordering it currently is. And this speaks to one key difference between clustered and non-clustered: clustered indexes result in the row data being shifted around so that each index key is stored sequentially.

 

Hypothetically, if nothing else has formal relationships with this table and you can use a clustered index, then dropping the existing clustered index and creating a new one against the ManufacturerMarking would mean:

 

  1. The data/rows wold be stored in alphabetical order based on the ManufacturerMarking column;
  2. You would no longer need an ORDER BY clause in your view definition since ordering would no longer be necessary.

But again - and I keep coming back to this - I'd be surprised if the table is really that isolated. Only you can figure that out through inspecting the dependencies (things like foreign keys, views, stored procedures, functions, etc.)

 

Getting back on track, if you have got an existing non-clustered index targeting only the ManufacturerMarking column, then there may well be not much more you can do - particularly as you said it's a very wide table (makes me wonder if you can't use a view to cut down the columns, which will help). But you can run the following superficial T-SQL check just to get some certainty around whether the bottleneck is definitely in SQL, or external to SQL.

 

If these two queries complete in roughly the same time, then your issue isn't the SQL instance at all.

 

Let the schema name be "dbo" and table name be "Products", since I don't know either (and it's not important that I do):

 

-- Query 1
SELECT COUNT(*) AS [Count] FROM dbo.Products AS [p] WITH (nolock)
GO

-- Query 2
SELECT COUNT(*) AS [Count] FROM dbo.Products AS [p] WITH (nolock) ORDER BY p.ManufacturerMarking

 

If query 2 still takes around 7 second then the bottleneck is definitely within the SQL instance, and at this stage based on what we know, I don't have any better suggestions other than the generalised indexing overview above.