Query from Sage

[LukeS]

I'm running my query from linked table from sage SALES_ORDER but it takes very long to show result. Even if I want to see only last 100 records or records from last 2 weeks. Is there any way to load and show results quicker ? Can anybody help me with this problem?
Thank you.

 

[JHB]

Show the query's SQL-String.
What are the tables linked from, MS-Access back-end, SQL-Server or ??
What is very long - a minute, ½ hour or ??

 

[LukeS]

SELECT SALES_ORDER.ORDER_NUMBER, SALES_ORDER.ORDER_DATE, SALES_ORDER.NAME, SALES_ORDER.GLOBAL_DETAILS, SOP_ITEM.DESCRIPTION, SALES_ORDER.DESPATCH_STATUS

FROM SALES_ORDER INNER JOIN SOP_ITEM ON SALES_ORDER.ORDER_NUMBER = SOP_ITEM.ORDER_NUMBER

ORDER BY SALES_ORDER.ORDER_DATE DESC;

*

But even simple select * query* takes long time to show results.

Table are linked from access using ODBC to sagev21. It takes around 2-3 min.



Sent from my SM-T800 using Tapatalk

 

[Minty]

Are you using the correct sql client for the version of SQL server sage is running ?

 

[LukeS]

Yes, I hope so

Sent from my SM-T800 using Tapatalk

 

[LukeS]

How can I check this ?

Sent from my SM-T800 using Tapatalk

 

[CJ_London]

the problem may be that the tables are not indexed and you have no control over that. It has been a while but I seem to recall that when you link you are asked for a primary key to uniquely identify the record - may just be for table updates (which you won't be able to do anyway) but worth checking

You could try importing the data into local tables and add you own indexing

 

[LukeS]

I have chosen primary key indexed in that table by ORDER_NUMBER as all tables have that number

Sent from my SM-T800 using Tapatalk

 

[LukeS]

If I will use dlookup function just to find and display 1 specific record, it showing up in a second. So I was thinking if I will choose to show only Top 100 in my SQL string should be quicker but it's not. Any idea ? Thanks for helping.

Sent from my SM-T800 using Tapatalk