sql optimization

NavStudent

Hello I've been looking at how navision does findasyoutype on sql. And this is very costly. I was simply searching on name
On SQL Navision runs the following statement.

SELECT  * ,DATALENGTH("Picture") FROM "Test"."dbo"."Pharm$Customer" WHERE (("Name" LIKE '%p%')) ORDER BY 
"No_" OPTION (FAST 10)

This takes 1700 ms and the number does go down as you keep searching for the field but.

Most of the time whe you are searching (Ctrl +F), you are looking for one record. So why isn't Navision client sending

SELECT  top 100 * ,DATALENGTH("Picture") FROM "Test"."dbo"."Jason$Customer" WHERE (("Name" LIKE '%p%')) ORDER BY 
"No_" OPTION (FAST 10)

The empasis is the Top 100, or even better top 10
This improves performance a lot.
while I was running the test, I was running

DBcC dropcleanbuffers
dbcc freeproccache

To clear the cache so that I would get same results.
Thank you.

kine

There are more issues with this search. If you are using some charset with accents, the search select can look like:

  SELECT .... where Name = '%[Mm][AaÄäÁáâĂ㥹][CcĆćÇç][Hh][OoÖöÓóÔôŐő][Vv][AaÄäÁáâĂ㥹]%

If you enable the Match case option, the select will look like:

  SELECT ... where Name = '%Machová%'

And if you are using case-insensitive collation, it doesn't matter, if you select this, because it will find any variant of the string. It means you can search faster if you select the "Match case" when using CI collation and there is one additional gain: in some languages (like Czech) where are special "double character" (like 'ch' in Czech - it is one character in our alphabet) will this option grant you correct searching when using the local collation for sorting...

NavStudent

I see.
It still doesn't anser, why they wouldn't add TOP 100 or TOP 10 in the sql statement.

Saint-Sage

Because of support for the native database?

NavStudent

They are two different clients. fin.exe and finsql.exe.
Navision has also added finset,findfirst, etc as well.

What I'm asking when it translates the navisision find dialog into sql statement.

DenSter

Those SQL statements are generated by the executables, you can't change those. Every time MS comes out with new exe's, they make small improvements to the SQL statements.

NavStudent

DenSter wrote:

Those SQL statements are generated by the executables, you can't change those. Every time MS comes out with new exe's, they make small improvements to the SQL statements.

I know that. I'm asking why Navision finsql.exe calling the top Number, it will save a lot of processing power.

Marije_Brummel

And you really expect these forum members to answer that?

Please do a product suggestion via the MS site.

NavStudent

Mark Brummel wrote:

And you really expect these forum members to answer that?

Please do a product suggestion via the MS site.

Fair enough. A link would where this is would be helpful.

The reason I was asking it here, to see if I was missing something and the suggestion I had wouldn't be right in certain scenarios.

Thank you.

kriki

[Topic moved from Navision forum to SQL Performance forum]

Nobody

The reason for not "TOP 10" is because the type of ODBC cursor being used.

When "OPTION (FAST 10)" is used at the end of the statment (within a cursor) SQL creates a FFO or Fast Foward only cursor, it returns the first 10 rows but leaves the cursor open and then essentially fetches the rest. It is not as nice as TOP 10, but NAV would need to change the logic to the whole NDBCS.DLL to change this.

The reason the statement is slow is not becuase of the "TOP" or cursor type it is because of the "LIKE" in the where clause. LIKE is very expensive. Hence the reason "Find as you Type" is so costly. Not sure how you would redesign that?

kriki

Nobody wrote:

The reason the statement is slow is not becuase of the "TOP" or cursor type it is because of the "LIKE" in the where clause. LIKE is very expensive. Hence the reason "Find as you Type" is so costly. Not sure how you would redesign that?

Very easy!
Remove the "Find as you type"-toggle from finsql.exe.

Nobody

If only they would do that.. :-)

There are many customers who flat out refuse to turn it off.

One way I found to "somewhat" mitigate the effect is compile a list of all the fields the users will use with "find as you type" and create single column non-clustered indexes on them. This is really more to get field statistics than anything else but it does help somewhat.