0

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][1]][1]

products16 05:46 [![enter image description here][2]][2]

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?

2
  • Did you check SQL query stress tool written by Adam Machanic? You can create your own workload task and run it repeatedly against your DB.
    – Sranda
    Commented Aug 31, 2022 at 9:47
  • @Sranda I used the SqlQueryStress tool on your advice, but I couldn't interpret it.
    – omerix
    Commented Aug 31, 2022 at 12:10

1 Answer 1

0

I'm having a hard time following your SQLQueryStress results, but putting that aside, I highly doubt you'll have any noticeable difference between VARCHAR(32) and VARCHAR(16) in your index for a small dataset of 1,000,000 rows.

If you want to granularly test the difference between the two, just run your example query in SSMS after enabling TIME Statistics by running SET STATISTICS TIME ON;. The CPU Time and Elapsed Time will now show in the Messages tab every time you run your query. I would run the query 10 times for each field size you're comparison, then average the Elapsed Time of each set of trials. You should also consider discarding results during the tests, to minimize irrelevant variables such as network and render time.

Alternatively, if you didn't want to limit yourself, you can (and probably should from a normalization perspective) create a separate ProductCodes table which stores the distinct list of codes (with any reasonable size VARCHAR description field), and has a CodeId field as the primary key, that is an INT and then store the CodeId field in your Products table instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.