Create new index in a table

Elessar · 2012-08-01

Hey,

I analyzed a job which has a very long runtime. In the client monitor I saw that one SQL-Statement has a runtime around 900ms. This statement uses no index key, so in order to improve it I want to set an index. The sql-statement looks like this: SELECT SUM("Amount") FROM "XXXX" WITH (UPDLOCK, ROWLOCK) WHERE (("Ledger Entry No_"=XXX)) AND (("Entry Type"=X OR "Entry Type"=X)). My question is, if I could create the index in the table in Navision with the following parameters:

Enabled: yes
Key: Ledger Entry No_, Entry Type
SumIndexFields: Amount
Key Groups: <>
MaintainSQLIndex: <Yes>
MaintainSiftIndex: <Yes>
Clustered: <No>
SQL-Index: <Undefined>

Are these settings ok or could there be problem, if I create a new index in a table? I would do this first on a test-system, but I would be more confident, if you have a look at this and give me some advice.

Best regards!

David_Singleton · 2012-08-01

The client monitor is just one of many tools you use to analyze database performance. Don't simply implement any thing you see as an issue with out actually finding out the source of the issue as there may be a better way to solve it. Obviously since you have only posted less than 10% of the needed information ALL replies you get to this are people making random guesses. My random guess is that you might look at the SIFTs, see if someone tried tuning before and incorrectly removed one, or if the incorrect one had been created. Another guess might be to tell you that there is a chance the new index will help but who knows how much it will help, and also since you are adding unnecessary stuff, you may decrease the time required for this one query and then increase the time on 5 others, thus nullifying your work. The problem being that right now you will not see the minuses, and then in a months time when you do you wont connect that issue to this one.

David_Singleton · 2012-08-01

PS Rule #1 in tuning - "There is no silver bullet"

kriki · 2012-08-01

David Singleton wrote:

PS Rule #1 in tuning - "There is no silver bullet"

Not completely true. I have found once or twice a silver bullet!
But baseline is don't count to find a silver bullet. You're just plane lucky if you find one.

davmac1 · 2012-08-01

What is the name of the table?
How many rows?

macuxpc · 2012-08-01

FlSch wrote:

The sql-statement looks like this: SELECT SUM("Amount") FROM "XXXX" WITH (UPDLOCK, ROWLOCK) WHERE (("Ledger Entry No_"=XXX)) AND (("Entry Type"=X OR "Entry Type"=X)).

Could you post the actual query? Also, what was the time elapsed for this statement?

If the XXXX is a base table (not a VSIFT), you do indeed have a missing SIFT definition, which could speed things up considerably.

Did you filter your Client Monitor output on "Time Elapsed" to see the long running stuff?

bbrown · 2012-08-01

SIFT won't always speed something up and the cost of maintaining it may offset the advantage. As the sayin goes "it depends".

As David said, without more details we're all just throwing out guesses.

Elessar · 2012-08-01

Hey,

thanks for all your answers.

@ Davmac: the table is a custom table which has around 700000 voucher entries.

@macuxpc: the query is SELECT SUM("Amount") FROM "XXX$Voucher Entry" WITH (UPDLOCK, ROWLOCK) WHERE (("Voucher Entry No_"=98786)) AND (("Entry Type"=2 OR "Entry Type"=6)) and the elapsed time is 890 ms. Yes I have filtered the output for the long running stuff and this query is the slowest of all.

What other informations do you need? The used SQL-index for this quere is No_. SumIndexField is Amount. Order is Voucher Entry No_, Entry Type, Date, No_. The filter is Voucher Entry No_: 98786, EntryType:resEntry|resEntry (1x per Person). The sql-plan is: Compute Scalar[2,1];Stream Aggregate[3,2];Clustered Index Scan(XXX$Voucher Entry$0)[4,3]

Best regards!

macuxpc · 2012-08-01

FlSch wrote:

@macuxpc: the query is SELECT SUM("Amount") FROM "XXX$Voucher Entry" WITH (UPDLOCK, ROWLOCK) WHERE (("Voucher Entry No_"=98786)) AND (("Entry Type"=2 OR "Entry Type"=6)) and the elapsed time is 890 ms. Yes I have filtered the output for the long running stuff and this query is the slowest of all.

Create the SIFT definition as you had described it in your OP and run the process again. I'm pretty sure it will resolve this long-running query. It should now be a SELECT to a VSIFT instead of the "Voucher Entry".

If you already have an existing index "Voucher Entry No.", "Entry Type", just add "Amount" to the SumIndexFields.

stryk · 2012-08-01

The VSIFT (SumIndex of the Key you mentioned) might do the job:

Enabled: yes
Key: Ledger Entry No_, Entry Type
SumIndexFields: Amount
Key Groups: <>
MaintainSQLIndex: <Yes>
MaintainSiftIndex: <Yes>
Clustered: <No>
SQL-Index: <Undefined>

Alternatively - or if the VSIFT does not work - you could try to improve this query by adding a SQL Server site index:

/* SELECT SUM("Amount") FROM "XXXX" WITH (UPDLOCK, ROWLOCK) WHERE (("Ledger Entry No_"=XXX)) AND (("Entry Type"=X OR "Entry Type"=X)) */

CREATE INDEX idx01_20120801 ON "XXXX"
("Ledger Entry No_", "Entry Type")
INCLUDE
("Amount")

But: adding a new index (directly via SQL or via NAV "Key") eans to increase the "costs" when writing to that table. Thus, you should only do this if you gain a real benefit from this, e.g. if the SELECT SUM query is executed quite often!

David_Singleton · 2012-08-01

FlSch wrote:

What other informations do you need?

What version of Navision
Which build
SQL Version
Selectivity of the table on entry number
what is the code that calls this (guessing it is from a NAV calcfields or calcsums)
How often does this query execute per day
Is entry number the primary (clustered) index? If not what is the clustered index.
700,000 is not a lot of entries, so
How many spindles is the database on
how much cache on the server
how is your temp db configured (this will depend on the Nav build and SQL versions)
What is the insert to read ratio on the table?
Is this a hot table
Are the writes sequential

Those are just a FEW of the first questions I would investigate if tuning a database. Start with this and we can go from there. Though I am sure that other posters will add plenty more questions.

David_Singleton · 2012-08-01

Oh and why doesn't

Some text here work

David_Singleton · 2012-08-01

macuxpc wrote:

FlSch wrote:

@macuxpc: the query is SELECT SUM("Amount") FROM "XXX$Voucher Entry" WITH (UPDLOCK, ROWLOCK) WHERE (("Voucher Entry No_"=98786)) AND (("Entry Type"=2 OR "Entry Type"=6)) and the elapsed time is 890 ms. Yes I have filtered the output for the long running stuff and this query is the slowest of all.

Create the SIFT definition as you had described it in your OP and run the process again. I'm pretty sure it will resolve this long-running query. It should now be a SELECT to a VSIFT instead of the "Voucher Entry".

If you already have an existing index "Voucher Entry No.", "Entry Type", just add "Amount" to the SumIndexFields.

Sorry but I have to disagree with this. You have no idea of the selectivity on this table, so adding this sift may actually slow down the query. Worse we don't know the version of NAV or SQL, so you don't know if its SIFT or VSIFT so it could also have a big effect on write performance.

Trust me searching for the Silver Bullet never works.

David_Singleton · 2012-08-01

stryk wrote:

But: adding a new index (directly via SQL or via NAV "Key") eans to increase the "costs" when writing to that table. Thus, you should only do this if you gain a real benefit from this, e.g. if the SELECT SUM query is executed quite often!

:thumbsup:

macuxpc · 2012-08-01

David Singleton wrote:

Sorry but I have to disagree with this. You have no idea of the selectivity on this table, so adding this sift may actually slow down the query. Worse we don't know the version of NAV or SQL, so you don't know if its SIFT or VSIFT so it could also have a big effect on write performance.

Trust me searching for the Silver Bullet never works.

Never said this was a silver bullet ;-) However, it is obviously a bad query and needs to be resolved, either via SIFT or via the SQL-side index with included column Joerg brought up - not only is it a locking SELECT SUM to the base table, it's also a table scan, and it takes almost a full second, every time it is used.

Well, let's wait for feedback from the OP

Elessar · 2012-08-02

Hey,

I tried to change the table, but after I created the new index and tried to compile it I noticed, that I have no rights to change the table. So I have to ask our service provider to compile these changes, but this can sadly take some time. Thanks for all your help and very good feedback!

Best regards!

stryk · 2012-08-07

stryk wrote:
The VSIFT (SumIndex of the Key you mentioned) might do the job:

Enabled: yes
Key: Ledger Entry No_, Entry Type
SumIndexFields: Amount
Key Groups: <>
MaintainSQLIndex: <Yes>
MaintainSiftIndex: <Yes>
Clustered: <No>
SQL-Index: <Undefined>

Alternatively - or if the VSIFT does not work - you could try to improve this query by adding a SQL Server site index:
/* SELECT SUM("Amount") FROM "XXXX" WITH (UPDLOCK, ROWLOCK) WHERE (("Ledger Entry No_"=XXX)) AND (("Entry Type"=X OR "Entry Type"=X)) */

CREATE INDEX idx01_20120801 ON "XXXX"
("Ledger Entry No_", "Entry Type")
INCLUDE
("Amount")
But: adding a new index (directly via SQL or via NAV "Key") eans to increase the "costs" when writing to that table. Thus, you should only do this if you gain a real benefit from this, e.g. if the SELECT SUM query is executed quite often!

In the meantime you could try to fix the problem with that index, just to see if that really solves the issue. Once your NAV partner has encoded the Key/VSIFT you should DROP this SQL index.

Elessar · 2012-08-07

Good idea, I'll try it tomorrow and let you know.

Best regards!

Create new index in a table

Comments

Categories