All I want for Christmas is a real "SELECT" statem

Nobody

Currently when we use a FINDFIRST, FINDLAST, FINDSET, FIND(‘+’), or FIND(‘-‘) NAV does a “SELECT *” or select all from the table even though we do not need all the columns. This means even though we may have an index that satisfies the WHERE and ORDER BY clauses it still has to look at the clustered index to pull the column data because it is not on the index page. In SQL 2005 we have the option to include columns in the index that are not part of the index, but adding all the columns in the table to each index so it does not have to go back to the clustered to pull the column creates massive indexes.

Example from NAV:
Code Unit 5771 - Release(SalesHeader : Record "Sales Header")

SalesLine.SETCURRENTKEY("Document Type","Document No.","Location Code");
SalesLine.SETRANGE("Document Type","Document Type");
SalesLine.SETRANGE("Document No.","No.");
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETRANGE("Drop Shipment",FALSE);
SalesLine.SETRANGE("Job No.",'');
IF SalesLine.FIND('-') THEN BEGIN
First := TRUE;
REPEAT
IF (("Document Type" = "Document Type":: Order) AND
(SalesLine.Quantity >= 0)) OR
(("Document Type" = "Document Type"::"Return Order") AND
(SalesLine.Quantity < 0))
THEN
WhseType := WhseType::Outbound
ELSE
WhseType := WhseType::Inbound;

IF First OR (SalesLine."Location Code" <> OldLocationCode) OR
(WhseType <> OldWhseType)
THEN
CreateWhseRqst(SalesHeader,WhseType);

First := FALSE;
OldLocationCode := SalesLine."Location Code";
OldWhseType := WhseType;
UNTIL SalesLine.NEXT = 0;
END;

This creates the SQL statement:

SELECT * FROM "CRONUS USA, Inc_$Sales Line" WITH (UPDLOCK) WHERE (("Document Type"=?)) AND (("Document No_"=?)) AND (("Type"=?)) AND (("Drop Shipment"=?)) AND (("Job No_"=?)) ORDER BY "Document Type","Document No_","Location Code","Line No_"

The record size for this statement is 1669 bytes


Would it be possible in the future to do something like FINDFIELDS(‘+’) (“field1”, “field2”, and so on) or FINDFIELDS(‘-‘) (“field1”, “field2”, and so on) so we could pull just the fields we need? This would make query tuning much easier and help with performance and cut down on the size of the data go back and forth from the client (or middle tier) to the server.

Code Unit 5771 - Release(SalesHeader : Record "Sales Header")

SalesLine.SETCURRENTKEY("Document Type","Document No.","Location Code");
SalesLine.SETRANGE("Document Type","Document Type");
SalesLine.SETRANGE("Document No.","No.");
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETRANGE("Drop Shipment",FALSE);
SalesLine.SETRANGE("Job No.",'');
IF SalesLine.FINDFIELDS('-')(“Quantity”, “Location Code”) THEN BEGIN
First := TRUE;
REPEAT
IF (("Document Type" = "Document Type":: Order) AND
(SalesLine.Quantity >= 0)) OR
(("Document Type" = "Document Type"::"Return Order") AND
(SalesLine.Quantity < 0))
THEN
WhseType := WhseType::Outbound
ELSE
WhseType := WhseType::Inbound;

IF First OR (SalesLine."Location Code" <> OldLocationCode) OR
(WhseType <> OldWhseType)
THEN
CreateWhseRqst(SalesHeader,WhseType);

First := FALSE;
OldLocationCode := SalesLine."Location Code";
OldWhseType := WhseType;
UNTIL SalesLine.NEXT = 0;
END;

If we could do something like this it would create a much more defined SQL statement that would much smaller record sizes and much easier to tune for performance because we can create an index with the select list included. This would really benefit situations were a table has a IMAGE datatype that cannot be pulled via an index seek.

SELECT “Quantity”, “Location Code” FROM "CRONUS USA, Inc_$Sales Line" WITH (UPDLOCK) WHERE (("Document Type"=?)) AND (("Document No_"=?)) AND (("Type"=?)) AND (("Drop Shipment"=?)) AND (("Job No_"=?)) ORDER BY "Document Type","Document No_","Location Code","Line No_"

The record size for this statement is 73 bytes with a little index tuning it is 64 bytes.

DenSter

I like the idea of being able to define fields in a query, but I don't think that will work with all the validation code.

Steffen_Brodowski

You don't need what you wrote.

You only need to set the key-parameter "SQL-Index" of the given key to

"Document No.,Document Type"

so that the sql-server uses this key for its selection.

Listen: The SetCurrentKey in c/side only say's how you want to sort your resultset.

SQL-Performing is very easy in Navision, but you have to know, how the sql-server uses a key for its selection and how it uses a key for order the resultset.

In table 36 or 37 the best key is?

"Document no."

Making a selection! on this key will take:

1 record in table 36
and a maximum of 1000 records in table 37, or does any sales order have more then 1000 lines?

Shure, a selection on table 37 is always more performant when the sql-server has a selection-key with some fields more. But in this case you
only have some milliseconds less.

Your written selection-command will get less bytes, because only the used fields are taken back through the network.

The time you want to perform is not network-traffic. You have to spend the time by making the server faster. You have to help him to be more
performant by its selection and with "order by" in the statement, you wrote, it will not be more performant.

If you want to do your one, why don't you use the MSDAC xxx?
Because you have some performance-problems to merge the result
into navision-table-fields.

What I want for chrismas from Microsoft is only to fix very heavy problems, they exists still since version 4.0!

Greetings
Steve

DenSter

The point wasn't the sorting order, it was the ability to only select certain fields.

Steffen_Brodowski

Why?

I think to get more performance, or not?

DenSter

For one yes, because when you select the columns it would be less network traffic. That's the idea anyway

bbrown

While we're at it, why don't we just wish they would stop treating SQL like an ISAM database.

DenSter

As long as the C/SIDE platform is still supported, which is an ISAM database, that is not going to happen.

bbrown

DenSter wrote:

As long as the C/SIDE platform is still supported, which is an ISAM database, that is not going to happen.

I agree. This is nothing unique to Navision. Over the years I have worked with a number of products that introduced "SQL" versions. Their approach was just like Navision's. Slap the existing system on top of SQL. The goal was not to create a technically superior product, but to simply allow the salespeople to say "Yes, we run on SQL"

TrippyZ

Ouch, I cross trained from Sage Line 500 CS/3 on Unix, and it seems I am back at C-ISAM again!

pdj

DenSter wrote:

The point wasn't the sorting order, it was the ability to only select certain fields.

Not tested - just an idea.
Could using Linkedobjects solve the problem? (I know it would require a view and a table for each situation, but just for proff of concept...)

DenSter

No views have their own weird problems (difficult to update with regular users, you can't test on aC/SIDE database, things like that, not impossible problems but making it difficult to manage), I would not try to solve it that way. Maybe for one or two very specific cases where you'd need to do complicated joins that would work, but I'd stay away from that as a general solution.

pdj

DenSter wrote:

...I would not try to solve it that way...

Maybe not. The original post was about an isolated piece of code, which Nobody suggested to optimize for performance reasons. I would just like it tested with a view, to see if the were any measurable difference. Nobody?
Personally I doubt it has any significant improvement on even isolated pieces of code unless the network is extremely slow (WAN), but I have been surprised by the SQL server before :-)

pdj

Nobody wrote:

This would really benefit situations were a table has a IMAGE datatype that cannot be pulled via an index seek.

This statement I don't understand. Are you saying that when i.e. the Item List form is called it retrieves all the blob-bitmaps to the client as well?

Marije_Brummel

What NAV does is that it determines if there is an image (BLOB) attached by determining the size.

If the size is large it can cause performance issues.

Bottom line is, try to avoid BLOBs in a SQL database, especialy in Setup tables because it can seriously slow down your SETUP.GET statements.

pdj

Mark Brummel wrote:

Bottom line is, try to avoid BLOBs in a SQL database, especialy in Setup tables because it can seriously slow down your SETUP.GET statements.

So MS could make an optimization to the finsql.exe, by changing all SELECT * of tables with blob to only get the non-blob fields. And then wait till the calcfields to actually retrieve the blob. Has anyone suggested that on Partnersource?

PS: The fact that you answered me the 1st of April and I simply can't believe it, still makes me wonder... :?

Marije_Brummel

All of this is on a list somewhere in denmark, but it is all about priorities.

The more people ask for a change, the more likely it is implemented.

Miklos_Hollender

I'm also longing for a real SELECT although for different reasons. Right now I have to create a report to show stock by Bins and Lots (this is one of the things that should be standard because it's the natural integration of two existing functionality). In SQL it would be as simple as SELECT Bin Code, Lot No., SUM(Quantity) FROM Warehouse Entry GROUP BY Bin Code, Lot No. In Navision? The best idea I managed to come up with is to populate a temp table at the beginning of the report and run on it with the ugly Integer-table solution... clearly a hell of a lot of time wasted on something that's supposed to be simple.

davmac1

If you are running in SQL Server, you can always create a view, then define that as a table in Navision.
Works really well for reporting.

kriki

[Topic moved from Upcoming version NAV 5.1 forum to SQL General forum]