Sample Header Ad - 728x90

Working with varchar field in Clustered Index. (Performance Test)

0 votes
1 answer
145 views
I am using Microsoft SQL Server 2016. For my products table; I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)). I know that ProductId is the right option for this, but it affects my software development speed. But in terms of speed, I want my Product table to run fast. Estimately there will be 1,000,000 records in the table. What I want to ask is if using varchar(16) instead of varchar(32) in ProductCode, how much will it affect performance in queries? Is there a software that I can test as ProductCode varchar(32) and varchar(16) in 1,000,000 lines? Can I do this on sql Management studio? 16 characters is enough right now. However, the user may want the product code to be given by the system itself in this case I want to automatically set a product code with newid. Product Id(int) is not the right solution for me. Because many transactions (orders, warehouses, sales) that have their own product codes are executed through this code. Edit: I tested with SqQueryStress based on the comment. I am sharing the results. I created 2 tables as products and sales. productsID,salesID; - productsID.id clustered index - salesID.productId non clustered index products16,sales16 ; - products16.productCode clustered index - sales16.productCode non clustered index products36,sales36; - products36.productCode clustered index - sales36.productCode non clustered index (https://www.db-fiddle.com/f/3qJM9uupQoXAtLgL7u8YaE/0) I entered 100,000 products, random results productsId 06:03 [![enter image description here]] products16 05:46 [![enter image description here]] products36 05:42 enter image description here I entered a sales record from 10,000 random products tables. salesID 01:42 enter image description here sales16 02:34 enter image description here sales36 02:01 enter image description here I listed the sales reports according to the data I entered. (1000 times) Does that mean reporting 1000 * 10,000 (sales item) 10,000,000 rows? salesId sales report (join) 01:07 enter image description here sales16 sales report (join) 02:05 enter image description here sales36 sales report (join) 02:55 enter image description here I used SqlQueryStress for the first time, I don't know much about interpretation. **When I pull 10,000,000 sales data. Reporting sales36 and sales16 as well 02:55 (175 seconds) 02:05 (125 seconds) Is there a 40% performance difference?**
Asked by omerix (101 rep)
Aug 31, 2022, 09:42 AM
Last activity: Jul 21, 2025, 05:01 AM