Filter working very slow..

vsalot

Hi All,

We are using Nav 5.0 SP1 with latest Hotfix, with SQL 2005. When I am doing Field filter on Item Table having 16Lacs record, with PIPE (|) sign, system got hang. I am not able to get the result. for one record, or with .. sign it is working fine. The same was working fine in 3.7 Native database.. we are getting the result in one second.. where as after migration we are getting the result after 30 Mins.. Pls suggest me..

Belias

30 mins?!
start code coverage or (in this case better) sqlprofiler to see wich query is slow.
You can also try to set the same filter by code and do a findset and see if there's a difference, if so, maybe you have some bad code under form/table triggers...

What do you mean with "16Lacs"?

vsalot

No code written.. I checked using Client monitor.. Check the query below.
I am doing simple filter on Item list Form on No. Field. (1|2)

SELECT * FROM "HO$ITEM" WHERE (("No_"='1' OR "No_"='2')) AND "No_">='1' ORDER BY "No_"

In client monitor this query is showing time of 30 Min... If i try 1..2 then it will come within a second.. This table is having 16Lacs record.. In native Database the same (1|2) is working very fast.. no issue with the same.. Database size is 400GB. I tried everything.. Reindexing, Optimization.. Tried with -T4119 flag.. But still no result.

Belias

again...what are "Lacs"? i can't even find this word in google...

The query is correct (at least, also my nav does this query if i apply the filter)...but which query is run if you apply filter 1..2?
how many flowfilters do you show?

vsalot

if i am running 1..2 then it will show the below query..

SELECT * FROM "HOTEST$Item" WHERE (("No_">='0268' AND "No_"<='0269')) AND "No_">'0268' ORDER BY "No_" .

Total number of records are 1600000..

No flowfilters on the form... running very fast in Native... same database, same form

Belias

SELECT * FROM "HO$ITEM" WHERE (("No_"='1' OR "No_"='2')) AND "No_">='1' ORDER BY "No_"

SELECT * FROM "HOTEST$Item" WHERE (("No_">='0268' AND "No_"<='0269')) AND "No_">'0268' ORDER BY "No_" .

why the companyname is different?
are you running both queries on the same company?if not, you should do it.
What is the result set of your queries?
Is the key maintained in sql?

P.s.: (Actually this is a question, not an answer)strange where clause in the second query, maybe it depends from the search method "=><"?and from the fact that the company is different and item no. 1 does not exist?

vsalot

By mistake.. I put the query from my test server.

Yaa I am running both filter in the same server, same compnay, same database only.. for 1..2 i will get within a second whereas 1|2 i will get in 20-30 mins.

Primary key is "No." only & that is Clustered index & is maintained at SQL.

It seems that problem is with Search method '=><'.. SQL 2005 is having strange behaviour with the method.

SELECT * FROM "HO$ITEM" WHERE (("No_"='1' OR "No_"='2')) AND "No_">='1' ORDER BY "No_"

SELECT * FROM "HO$Item" WHERE (("No_">='1' AND "No_"<='2')) AND "No_">'1' ORDER BY "No_" .

Belias

#-o
Answering my own question of post before (and this could guide you to solve your problem).
do you know that filtering 1|2 is different than 1..2?
sql orders the numbers differently than an FDB database:
in sql, number 2 is not right after number 1 ("no." field of item is a code), while in nav it is.
1..2 filter makes sql return a subsequent set of records, while when using 1|2, sql has to jump from 1 record to another (which can be distant some million of records than the first one) example:

1
11
1111
1111123
12345678
...
2
21
22345

Why sqlserver hangs?we should wait for a sql expert, i can't just guess

...ok, i can't stand it: i'll try to guess

maybe the 'OR' clause of the slow query leads sql to do a table scan: as i said, there can be million of records between 1 and 2. You should have coded your items in a different way, in my opinion (and in microsoft opinion, too), like
0000001
0000002
0000003
In this way, both sql and nav will work smoothly