Re: SV: [development-axapta] Index & Index Hint - Oracle / MSSQL

[Sonny Wibawa Adi]

Hi
 
For SQL Server, I have a way to check what exactly Axapta application do when we use Index and Index hint. Just run Profiler, use host filter with value of your computer name, check what is the command to SQL Server. I have tried Index command, Axapta convert it to ORDER BY SQL command. When I tried Index hint, Axapta convert it to sp_execute command, using ID and I still don't understand the exact command is.
 
So, when Svein tried to explain:

Running MSSQL 7.0 the use of index worked fine in a specific select statement.
After the upgrade this specific select statement seemed to take forever to execute.
Axapta Index command will be converted into ORDER BY in SQL, so, that is the reason why the statement seemed to take forever to execute. :)
 
Cheers,
Sonny Wibawa AdiSvein Tore Hanssen <[EMAIL PROTECTED]> wrote:


Hi
 
I have made an observation regarding the use of index and index hint in the Axapta application of one of our customers.
Axapta 2.5 MP3SP3 and MS SQL.
 
The customer upgraded to a new version of the MSSQL. (Upgrading from MSSQL Server 7.0 to MSSQL Server 2000)
 
Running MSSQL 7.0 the use of index worked fine in a specific select statement.
After the upgrade this specific select statement seemed to take forever to execute.
 
Changing to index hint made the select statement run quickly like it used to on MSSQL 7.0.
Also simply removing the use of index/index hint made it run quickly.
 
Seems to me that in this case it matters what MSSQL version is running Axapta.
 
Regards
Svein T. Hansen


Fra: HEW (Mag. Helmut Wimmer) [mailto:[EMAIL PROTECTED] Sendt: 11. februar 2004 11:24Til: '[EMAIL PROTECTED]'Emne: RE: [development-axapta] Index & Index Hint - Oracle / MSSQL
HiYou are right. "Index" and "Index hint" will produce a simple "index"statement when you are using MSSQL. There is no difference between the twothen. For Oracle, it's another story.BTW: Sometimes it is better not to assign an "index" command at all. Let theMSSQL figure out the best index. Normally it will find the best one. (That'sa thing somebody told me that was part of the programming team of MSSQL 2Kserver).-----Urspr�ngliche Nachricht-----Von: Rudi Hansen [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 06. Februar 2004 15:02An: [EMAIL PROTECTED]Betreff: [development-axapta] Index & Index Hint - Oracle / MSSQLHow is it with the index & index hint options.It it correct that index hint is the same as index on MSSQL but on Oracleindex hint is really a hint?------------------------ Yahoo! Groups Sponsor ---------------------~--> BuyInk
 Cartridges or Refill Kits for your HP, Epson, Canon or Lexmark Printerat MyInks.com. Free s/h on orders $50 or more to the US & Canada.http://www.c1tracking.com/l.asp?cid=5511http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/saFolB/TM---------------------------------------------------------------------~->Yahoo! Groups Links
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.







Yahoo! Groups Sponsor


ADVERTISEMENT

<NOSCRIPT>
<A href="http://rd.yahoo.com/SIG=12ctvffs1/M=274551.4550177.5718621.1261774/D=egroupweb/S=1705006764:HM/EXP=1077262112/A=1994230/R=2/*http://ad.doubleclick.net/jump/N3349.yahoo1/B1282054.22;abr=!ie4;abr=!ie5;sz=300x250;code=18634;dcopt=rcl;ord=1077175712314611?">
<IMG SRC="" BORDER=0 WIDTH=300 HEIGHT=250 ALT="Click Here">Click Here</A>
</NOSCRIPT>













Yahoo! Groups Links

To visit your group on the web, go to:http://groups.yahoo.com/group/development-axapta/ 
To unsubscribe from this group, send an email to:[EMAIL PROTECTED] 
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.