Webcast on SQL Optimization for Ms Dynamics NAV

Waldo

Ok, Mark, looking forward to it ... meanwhile ... see you in Plopsa

Marije_Brummel

Yes! Everybody, next sunday will be MiBuSo Plopsa day!

See you in Belgium \:D/

(PS: This is not a SQL Workshop location)

Luc_VanDyck

Hynek Muhlbacher wrote:

N.B. I think that I have done two posts so far breaking all rules not to advertise business here... Sorry, perhaps somebody ban me from here please?

So you are asking for an invoice, right ;-)

NavStudent

Ok ran it with closing date.

setrange(Date,010107D,CLOSINGDATE(123107D));

SELECT SUM("s2") FROM "NavisionSP2$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=? AND ((f3>=? AND f3<?)))

setrange(Date,010107D,123107D);

SELECT SUM("sum1") FROM (SELECT  SUM("s2") AS "sum1" FROM "NavisionSP2$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=? AND ((f3>=? AND f3<?))) UNION ALL SELECT  SUM("s2") AS "sum1" FROM "NavisionSP2"."dbo"."NavisionSP2$50000$0" WITH (READUNCOMMITTED

It looks like you should set closing date in your filter for dates?
Will this have perfomrance Improvement? Looks like the second statement does a Union whereas the first doesn't.

Marije_Brummel

What is this 50000 table you have created? In your first example you used the item ledgers.

NavStudent

Yes I created a 50000 table to play and learn. With Line No. as PK and Date and amount field.

Something else I just saw that was mentioned in webcast was example of table that can gain performance. "Warehouse Activity Line" was one of them.

Anyways. Another tip I want to add is that. Since 5.0 has sql performance changes already added to the object, if you want to improve performance, Just compare the 5.0 table keys to 4.0. They've added different SQL Index to the keys and have disabled MAINSQL Index on several keys.
Make sure that you don't change Navision keys, just the sqlindex.

This can be very helpfull.

Marije_Brummel

And who do you think has done that.

NavStudent

I would have assumed somebody from Navision, but based on your question, it looks like the person is not from Navision.

So my final answer as they would say in jeopardy. Who is Hynek?

Marije_Brummel

To completely finalise this advertisement.

Hynek is manager of SQL Perform Ltd, a company specialised in SQL tuning for NAV.

The answer to your question is yes, even MS is hiring him for his knowledge.

Please look at www.sqlperform.com

NavStudent

two more question. In his demo he did something to sql profiler that made it stop going to the last line. what was that?


Also how much performance degradation is there when running sql profiler on a database? Is it noticeable. If lets say there are 20 people loged in?

NavStudent

ok I've now started query analyzer and looked at the following sql statement

SELECT  *,DATALENGTH("Picture") FROM "NAVPROD"."dbo"."Jason Pharm$Contact" WHERE (("Name" LIKE 'test')) AND  "No_">'C100020' ORDER BY "No_" OPTION (FAST 10)

There are two keys on Contact table
"No."
"Name","No." <---- new key added by me.

Based on execution plan both keys have 50% percent chance for the above.


I added the key because users constantly running this query on production.



In SQL the query analyizer shows the following code

SELECT  *,DATALENGTH("Picture") FROM "NAVPROD"."dbo"."Jason Pharm$Contact"  WHERE (("Name" LIKE @P1)) ORDER BY "No_" OPTION (FAST 10)

I can't tell what P1 is and P2. Is there a way I can find out?

If I change the value of P1 and P2 I get different percange 68% - 32%.
The Primary clustered key wins most of the time.

In query analyzer this shows a duration of 700 ms.
There are 150K Contacts records in the table.

is 700 ms too high? Will the new key bring any benefits if I add it to production?

Users are constantly searching for contacts on contact card.

ara3n

NavStudent wrote:

two more question. In his demo he did something to sql profiler that made it stop going to the last line. what was that?

Click on window, uncheck autscroll.

kriki

[Topic moved from Navision forum to SQL Performance forum]