Case sensitive/insensitive issue after upgrade (4 to 5)

bab

Hi,
I've the following issue at one of our customer:
They were running NAV 4.00 with SP3 (both client and objects). The database collation was CS AS (Hungarian collation). They decided to do a technical upgrade to 5.00 SP1 (only client), and in the same time change the collation to case insensitive.
The upgrade process was the following (main steps):
1) Create NAV backup with the 4.00 SP3 client
2) Create new db with the 5.00 SP1 client, with CI AS collation
3) Restore the NAV backup
The first thing I've realised was huge performass loss on some reports (db size is about 40GB, with 150 concurent user). These reports were customized, or new reports, and most of them was poorly designed, using wrong indexes, filters etc. That was the "easy" part, but there are still real slow reports, those have to be reviewed. (It's strange, because in version 4.00SP3 the reports just run fine...).
Then I found an other issue: when filtering on Code type fields, if I set a filter which contains wildchar (*) the records can not be found(?). Forexample on the "No." field in the invoice list I set a field filter 'SI*', and I know there are records starting with 'SI', the result is an empty list. If I set a filter on the same filed '@SI*' it works fine...
Any suggestion is appreciated.

kine

Regarding the performance - is Index Hinting disabled?
Try to use SQL profiler for catching the SQL query send to the SQL. It can give you some clue...

bab

Hello kine,
thank you for your reply. As far as I know, index hinting is disabled by default in 5.00 SP1 (please correct me, if I'm wrong), and I did not set any index hint manually.
And one important thing, that I forgot to mention... The customer is using SQL Server 2000...
I'm using the client monitor to analyze the sql queries, and I made some progress, but I don't think that's the way I need... As I said before, I found some customized/new reports, with real bad coding, indexes, etc. But now I found some standard reports with the same performance loss...
Wrong filtering is still an issue, can't figure out what the problem could be. (Now trying to set the db back to CS in a test environment...)
As before, if you have any comments please add it...

kine

Ok. the problem is that NAV 5.00SP1 is recommended only for SQL 2005 because it is not using SIFT but VSIFT and the SQL 2000 is not fully supporting it.

bab

Thanks, and yes, I know that in version 5 NAV uses indexed views instead of SIFT tables, and it works on SQL Server 2005 with best performance, but in the development environment we are using SQL Server 2005, and the problem exists there too...
By the way, I wonder, if I create a report, which lists the G/L Entries is anything to do with the SIFTs... I'm not talking about insert/update/delete is slow, but reports (selects) are.
On and on comments are welcome.

kine

SIFTs are used when calculating sums (through CALCSUMS or CALCFIELDS over SUM FlowFields).

Best will be to use SQL Profiler and look what it is doing, what take so long to read he data, look at execution plant to see if some index will help you etc. Or you can use Client Monitor to catch the data and yu can use the "extended" client monitor to look at them in better way...

bab

Thanks again.
Solving performance issue is in progress. (It's a slow progress, btw...) But I'm not sure, why is that in version 4 reports run (not very fast, but still ok), and in version 5 the same report, with the same data is running not only slow, but in a 'does not compute' way.

In the meantime I've restored the original 4.00 backup to 5.00 with CS_AS codepage (as it was before). I can hardly belive, but the filters are working wrong, as I described in the first post. Now I'll try the same with a Cronus DB ](*,)
Am I really the only man who ran in to this 'undocumented feature'?

kine

Agan, use the SQL profiler, look at the query (you can post it there), it will tell you more. Right now it will be just guessing what is the problem...

bab

Profiler helped a lot...
With one report (which was one of the biggest issues) the problem was that the users were using date filters (it was not visible on the report by default, but most of the users were using it). There was no index defined for such queries (table has about 15M records), it was obvious from the trace log.
The report runs fine now, but I'm not sure why this happend while in version 4 it worked just fine without that index... :roll:
Now, there are about 5-6 reports left (if I know all of them), and filtering is still a big problem.
If any of you has a comment on these...

bab

Finaly solved...
For performance loss get a hotfix (KB954672), and for the filtering issue an other (KB954342).
Happy new year all of you guys...