December 7, 2016 at 3:51 am
Hello,
Need your advise on the above topic, please. Let assume I have an Invoice Lines Table invoice_lines as below, with a unique index on its most common columns:
invoice_number and invoice_line. There are also non-unique indices as below I01 and I02. In most of the cases (over 80%) those other indices used also
to get a value of required_info column, which is not a part of either index. Would you advise to have instead of indices I01 and I02 only one idex I03 as below, please?
Each invoice normally has between 5 to 10 lines.
CREATE TABLE invoice_lines(
invoice_id INT DEFAULT 0 NOT NULL,
invoice_line INT DEFAULT 0 NOT NULL,
supplier_id INT DEFAULT 0 NOT NULL,
product_code VARCHAR(10) DEFAULT '' NOT NULL,
other_column1 VARCHAR(10) DEFAULT '' NOT NULL,
other_column2 VARCHAR(10) DEFAULT '' NOT NULL,
other_column3 VARCHAR(10) DEFAULT '' NOT NULL,
required_info VARCHAR(100));
CREATE UNIQUE NONCLUSTERED INDEX invoice_lines_U01 ON invoice_lines (invoice_id,invoice_line);
CREATE NONCLUSTERED INDEX invoice_lines_I01 ON invoice_lines (invoice_id,supplier_id,product_code);
CREATE NONCLUSTERED INDEX invoice_lines_I02 ON invoice_lines (invoice_id,other_column1,other_column2);
CREATE NONCLUSTERED INDEX invoice_lines_I03 ON invoice_lines (invoice_id);
December 7, 2016 at 4:00 am
That depends on number of rows that belong to an invoice. If they have few lines, most chances are that I'll have only the unique index. On the other hand if Invoice_id column is not very selective, then depending on my queries I'll might have other indexes that combine Invoice_id with another column that is used as a search argument. I also wouldn't have another index that is based only on Invoice_id.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 7, 2016 at 4:29 am
What CLUSTERED index does the table have?
December 7, 2016 at 4:36 am
DesNorton (12/7/2016)
What CLUSTERED index does the table have?
Currently none. The table has lots of records periodically loaded at once (every other hour or so).
December 7, 2016 at 4:57 am
BOR15K (12/7/2016)
DesNorton (12/7/2016)
What CLUSTERED index does the table have?Currently none. The table has lots of records periodically loaded at once (every other hour or so).
If you use invoice_lines_I03 to replace invoice_lines_I01 and invoice_lines_I02, then you might just as well delete them and not not add the new index. This is because invoice_lines_U01 already covers invoice_lines_I01.
I would consider creating a single clustered primary key to replace all 3 of these indexes. Since the existing indexes all have invoice_id as their leading key, the PK should be sufficient, and you can then remove the other indexes.
ALTER TABLE dbo.invoice_lines
ADD CONSTRAINT pk_invoice_lines
PRIMARY KEY CLUSTERED (invoice_id,invoice_line);
December 7, 2016 at 5:17 am
DesNorton (12/7/2016)
BOR15K (12/7/2016)
DesNorton (12/7/2016)
What CLUSTERED index does the table have?Currently none. The table has lots of records periodically loaded at once (every other hour or so).
If you use invoice_lines_I03 to replace invoice_lines_I01 and invoice_lines_I02, then you might just as well delete them and not not add the new index. This is because invoice_lines_U01 already covers invoice_lines_I01.
I would consider creating a single clustered primary key to replace all 3 of these indexes. Since the existing indexes all have invoice_id as their leading key, the PK should be sufficient, and you can then remove the other indexes.
ALTER TABLE dbo.invoice_linesADD CONSTRAINT pk_invoice_lines
PRIMARY KEY CLUSTERED (invoice_id,invoice_line);
I do not think CLUSTERED is a solution, as there are lots of data (tens of thousands) can be uploaded in one go almost every hour and last thing I want is to have it clustered. With regards to the indices - I don't currently have I03 index, but enquire if I should have it and dump the other two.
Thank you.
December 7, 2016 at 6:04 am
BOR15K (12/7/2016)
Hello,Need your advise on the above topic, please. Let assume I have an Invoice Lines Table invoice_lines as below, with a unique index on its most common columns:
invoice_number and invoice_line. There are also non-unique indices as below I01 and I02. In most of the cases (over 80%) those other indices used also
to get a value of required_info column, which is not a part of either index. Would you advise to have instead of indices I01 and I02 only one idex I03 as below, please?
Each invoice normally has between 5 to 10 lines.
CREATE TABLE invoice_lines(
invoice_id INT DEFAULT 0 NOT NULL,
invoice_line INT DEFAULT 0 NOT NULL,
supplier_id INT DEFAULT 0 NOT NULL,
product_code VARCHAR(10) DEFAULT '' NOT NULL,
other_column1 VARCHAR(10) DEFAULT '' NOT NULL,
other_column2 VARCHAR(10) DEFAULT '' NOT NULL,
other_column3 VARCHAR(10) DEFAULT '' NOT NULL,
required_info VARCHAR(100));
CREATE UNIQUE NONCLUSTERED INDEX invoice_lines_U01 ON invoice_lines (invoice_id,invoice_line);
CREATE NONCLUSTERED INDEX invoice_lines_I01 ON invoice_lines (invoice_id,supplier_id,product_code);
CREATE NONCLUSTERED INDEX invoice_lines_I02 ON invoice_lines (invoice_id,other_column1,other_column2);
CREATE NONCLUSTERED INDEX invoice_lines_I03 ON invoice_lines (invoice_id);
If 80% of the queries use I01 and I02 to get the data required, then the key values of the index are being used to find the rows in the leaf level. If an invoice only contains 5 to 10 lines, then my guess is that the optimizer is using an index seek on the NCI and then doing to lookup to get the required_info column from the CI. We would need the actual execution plan to really know.
If your query returns only the key columns of the NCI and also required_info and you want it to cover the query, you could include the required_info column in the NCI. Example:
CREATE NONCLUSTERED INDEX invoice_lines_I03 ON invoice_lines (invoice_id, supplier_id, product_code) INCLUDE(required_info);
The bytes of required_info would be included in the leaf level of the index only, not in the B-Tree. You could do the same for your I02 to make it covering as well. If you're returning more columns than just those, you'll still get a lookup in the query plan unless you INCLUDE the queried columns.
Oh, and I would create a clustered index on that table. If it were me, I'd use an IDENTITY column.
I hope this helps.
December 7, 2016 at 6:10 am
Ed Wagner (12/7/2016)
Oh, and I would create a clustered index on that table. If it were me, I'd use an IDENTITY column.
I hope this helps.
Thank you, Ed. Can you advise why would you use clustered index in this case, please? The majority of the data is loaded in big bulks automatically and not processed manually.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply