We have a fair big SQL DB (50 GB). Our application is based on 2.6 and is strongly adapted. With the 3.01B runtime we were very very happy with the performance. Since we have changed to 3.10A the system is much slower. I have compared an older backup of the 3.01B based DB with our running 3.10A DB by use of the SQL profiler. Every thing is the same but with 3.10A there are additional commands (example below). I believe this commands are used to count the lines in a form in order to place the new scrollbar. Is there any option to switch the new scrollbar off or to change the runtime back to 3.01B? Please help me! SET SHOWPLAN_ALL ON go SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”=1 AND “Art”=2 AND “Belegnr.”=‘2020019787’ AND “Nr.”=‘2207028800’ AND “Restbestellungsmenge”=10.0 AND “Eink. von Kred.-Nr.”=‘900847’ AND “Zeilennr.”>380000 UNION ALL SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”=1 AND “Art”=2 AND “Belegnr.”=‘2020019787’ AND “Nr.”=‘2207028800’ AND “Restbestellungsmenge”=10.0 AND “Eink. von Kred.-Nr.”>‘900847’ UNION ALL SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”=1 AND “Art”=2 AND “Belegnr.”=‘2020019787’ AND “Nr.”=‘2207028800’ AND “Restbestellungsmenge”>10.0 UNION ALL SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”=1 AND “Art”=2 AND “Belegnr.”=‘2020019787’ AND “Nr.”>‘2207028800’ UNION ALL SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”=1 AND “Art”=2 AND “Belegnr.”>‘2020019787’ UNION ALL SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”=1 AND “Art”>2 UNION ALL SELECT * FROM “blue1”.“dbo”.“W_ Bike Parts$Einkaufszeile” WHERE ((“Belegart”=1)) AND ((“Art”=2)) AND ((“Restbestellungsmenge”<>0.0)) AND ((“Direktlieferung”=0)) AND “Belegart”>1 go SET SHOWPLAN_ALL OFF go
By additional commands you mean SET SHOWPLAN_ALL ON???
No it is the complete thing. SET SHOWPLAN_ALL ON SELECT * FROM … SET SHOWPLAN_ALL OFF This ist not a request for data the result of this query is a statistical info.
right. This statement compiles an execution plan for the SQL statement, but without executing it. Next time when this statement is passed to the SQL server, it should the execution plan should be retrieved from the cache without recompiling it with virtualy no slowdown. But its questionable if Navision or SQL server doesnt do anything meanwhile. I didnt try going back to 3.01B from 3.10A, but if you can connect to the SQL server with 3.01B or you can make a backup with 3.10A and then restore it with 3.01B client I dont see any problem of going with 3.01B. I would suggest writing to Navision on this or trying 3.60 /beta/ client. As to my knwoledge there is no feature for switching off or skiping a SQL statement passed from Navision to the SQL Server.
If you open a Navison SQL DB with 3.10A the DB is “converted” (it takes two seconds) and you can not open it with an earlier version of the client. Maybe there ist a trick? Navision backup and restore with 50 GB is really a pain. You refer to caching of the execution plan but CPU an IO is uesed every time. Maybe because the “select” differs every time? Is there something wrong with our SQL installation? SQL 2000 SP2 and no changes to the standard installation.
Hi samy, you are right. A 50GB database and the backup is a pain. However, in our test environment we have no problems with a 22GB database (on a SAN) at all. It seems like 3.10A is a little bit faster than 3.01A/B. SQL2000SP2 is installed as well. The SQL-server with Navision server installed has 1.5GB Ram, perhaps that is the key. Regards Walter
The UNION is a time consuming command that’s true. But what surprises me is that when the goal is to count the nr of records… a select * is done : select * where x=y from tablename UNION ALL select * where x=y from tablename UNION ALL If the table has many fields => performance drop. why not just : select field1 where x=z from tablename UNION ALL select field1 where x=y from tablename UNION ALL Gunther
I highly doubt that its only for counting records. SHOWPLAN_ALL gives much more information - keys used, EXPECTED row count of the result set, estimated IO and CPU usage, average row size and much more - and this is done for each operator in the query. For just counting rows only COUNT() function can be used.
I have also seen this phenomena on native db. We have a form with filtering on marked recors. This form was much faster on 3.01B than it is on 3.10A. I can only relate this to the new Toolbar feature since everthing else is the same (HW, network clients etc). //Lars
You are right! I can reproduce this in every form with “filtering on marked records” but our problem is not related to this. I think the performance problem with “filtering on marked records” is an additional bug.
My comments: These statements are indeed for the scrollbar only, and are not executed by SQL Server. Instead the execution plan is retrieved along with rather a lot of other information. The only statistic Attain needs is the estimated row count for the total union query. SQL Server provides this based on the index histograms in sysindexes. This procedure is independent of table size and filter complexity. COUNT(*) is not used because the statement is not executing (since this could take many seconds and is very much dependedent on table size and filter complexity). This is unnacceptable. Gunther, SELECT * is used safely, again, because no execution is performed. You are talking about execution and data retrieval. The reason for the union is that there is no simple way in SQL to retrieve a set less than or greater than some given row, based on a given order by. Either a single statement with many ORs must be used, or several separate statements retrieving distinct ranges, or a union. The union can be done as a single server call and is handled better by SQL Server than ORs. The processing of the statement, even for execution plan only, can take several milliseconds. So there is a 200 ms timeout in Attain (it is executed asynchronously using threading of the ODBC driver) for the retrieval of the execution plan in order to cause no observable delay in the UI (in Navision Server this is 50 ms since Navision Server must physically read the indexes as with a normal NEXT in order to obtain this statistic, which leads to more IO). After which if it is not completed it is cancelled and a fixed value is used for the scrollbar. However, even a complex execution plan, once calculated, is cached by SQL Server so will be much faster to retrieve for subsequent requests. I am interested in why you should be observing such a difference in the UI due to these operations. Can I suggest you try to use Client Montior and see the Eslapsed Time (ms) for these statements, which have a function name of COUNT RANGE. The profiler should also give the duration of the statements on the Server. Filtering on marked records is a separate issue since here, client side filtering is being done on a ad-hoc created minitree, with no suitable index available.
Filtering on marked records should only be done when using th primary key I just found out. This has allways been the fact but for some reason the difference in performance is bigger in 3.10 than in previous versions //Lars
Hi Robert, Your info is very interesting and we have seen the following behavior: For example in a form on table “Debitorenposten” (21) with filter on “Debitorennr”/“Offen” and the key “Debitorennr.,Offen,Positiv,Fälligkeitsdatum,Währungscode” this “query” is used every move up or down: SET SHOWPLAN_ALL ON SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Debitorennr.”=‘19’ AND “Offen”=1 AND “Positiv”=1 AND “Fälligkeitsdatum”={ts ‘2002-03-31 00:00:00.000’} AND “Währungscode”=’’ AND “Lfd. Nr.”>3896547 UNION ALL SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Debitorennr.”=‘19’ AND “Offen”=1 AND “Positiv”=1 AND “Fälligkeitsdatum”={ts ‘2002-03-31 00:00:00.000’} AND “Währungscode”>’’ UNION ALL SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Debitorennr.”=‘19’ AND “Offen”=1 AND “Positiv”=1 AND “Fälligkeitsdatum”>{ts ‘2002-03-31 00:00:00.000’} UNION ALL SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Debitorennr.”=‘19’ AND “Offen”=1 AND “Positiv”>1 UNION ALL SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Debitorennr.”=‘19’ AND “Offen”>1 UNION ALL SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Debitorennr.”>‘19’ SET SHOWPLAN_ALL OFF If we use the Key “Lfd. Nr.” (which is in my opinion not a good one in this case;) the scroll up an down is fast and this “query” is used: SET SHOWPLAN_ALL ON SELECT * FROM “test1”.“dbo”.“W_ Bike Parts$Debitorenposten” WHERE ((“Debitorennr.”=‘19’)) AND ((“Offen”=1)) AND “Lfd. Nr.”>3896547 SET SHOWPLAN_ALL OFF In some forms the “SET SHOWPLAN_ALL” eats up to “220 CPU / 1600 Reads / 230 Duration” every “up/down” We have 70 users so it is possible that many of them navigate in one of the problematic forms at the same time. In fact in daily work the “SET SHOWPLAN_ALL” stuff uses more CPU resources of our SQL server than all the “real query” together! Our configuration of the SQL server: Windows 2000 SP2 MS SQL 2000 SP2 2 x 1266 MHz 512k Tualatin 2 GB ram 6 channel SCSI Raid (U160,256 MB,64/66) 24x 15k disks (system=raid1,log=raid1(no cache),database=raid10) 1000 Mbit Ethernet The performance of the system with runtime 3.01B was absolut perfect! We do not need the new scrollbar in 3.10A! Is there a hotfix or a version past 3.10A without the scrollbar?
Is there a similar problem with 3.10 on the Navision database or is this just on SQL Server ? Thanks.
Going back when Financials was first released, one of the biggest complaints from NSCs was “the lack of a scroll bar” Navision’s reply was that it would dramatically reduce performance of List Forms. Well it looks like they were right. We do need a property on a list form “ShowScrollBar::Yes,No”. _________________________ David Singleton Navision Consultant since 1991 dmks22@comcast.net___________
sammy, What you are describing is strange. 2 things: The SHOWPLAN stuff should only be issued when the table/form is opened or when changing filter/key, not when navigating in it. Secondly, the CPU time on my system for these calls is showing 0 so the 230ms and 1600 reads you report is not ‘normal’ and should not occur within a SHOWPLAN_ALL ON/OFF block. What are the readings for the actual cursor calls (those begining with ‘declare’ and containing sp_cursoropen) when performing up/down etc? These are performing real executions. A thought: Are you using the Find dialog with “find as you type” when observing the problem? Have you reported this issue to Navision? There are currently no versions after 3.10 without the scrollbar.