How to speed up an X++ SQL Query

(0) Share

Report

Posted on by cowboyott

200

I am trying to Select the latest SalesLine with a specific serial number. Here is my query:

Select FirstOnly SalesLine Order By SalesLine.createdDateTime Desc
Exists Join InventDim Where InventDim.inventDimId == SalesLine.InventDimId
&& InventDim.inventSerialId == "Serial Number";

It is very slow, and I need a way to increase speed. Should I add indexes on InventDim.InventSerialId and SalesLine.InventDim?

Thanks.

*This post is locked for comments

All responses (3)

Answers (0)

cowboyott 200 on at

Like (0)

Report

RE: How to speed up an X++ SQL Query

Bingo. I don't know why I didn't order by recId to begin with. Thanks!
cowboyott 200 on at

Like (0)

Report

RE: How to speed up an X++ SQL Query

Thanks Sukrut,

There may be multiple sales Lines with the same serial (I know this isn't normal, but its possible in our scenario). So, I need the latest salesLine, hence the order by. I am using the exists join, because I only need the SalesLine info, not the invent dim Info. I thought that would be faster.
cowboyott 200 on at

Like (0)

Report

RE: How to speed up an X++ SQL Query

Actually, it looks like both those indexes exist already...