Hi Barry, I would love to try also but I don't think I have time right now as this is well overdue but will try to and let everyone know if manage to
Thanks James -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bayliss, Barry Sent: 28 September 2005 16:18 To: [email protected] Subject: RE: [Axapta-Knowledge-Village] To add or not to add an index? James, One possible way to determine the best solution is to run a test script and time the process. You might try the combinations 1. Temporary table with unique index 2. Permanent table with unique index 3. Temporary table with an index and in code check for duplicates (I believe 1 should be faster than this option). 4. Permanent table with an index and in code check for duplicates (I believe 2 should be faster than this option). 5. Permanent table with unique index, using caching. I would be interested in the results. Barry. -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of James Flavell Sent: Wednesday, 28 September 2005 2:57 PM To: [email protected] Subject: [Axapta-Knowledge-Village] To add or not to add an index? Hi I have an import script that is inserting/updating info to an Axapta temp table that has about 7 fields in it. In my import script I need to go into this temp table to check if a record already exists based on three of the fields that are being imported (i.e. checking what I am importing whetehr it has been imported before etc). I am wondering is it worth while to put a unqiue index on this table for these 3 fields or not? I am expecting maybe to have 50,000 records in this temp table every time I run the import script and will need to search it maybe 500,000 times during the import.... If there is no index will SQL do some 'optimization' so the performance difference is minimal? (I mean the number of fields on the table is not that many more than the index and I am wondering given the small size of the record SQL will cache or keep all the records in memory - although since it is a TMP table I am not sure will any of this be stored in the SQL or on the client - should I cahnge from TMP to normal table???) Appreaciate some advice so as to speed up this import Thanks James Sharing the knowledge on Axapta. Yahoo! Groups Links Sharing the knowledge on Axapta. Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/kGEolB/TM --------------------------------------------------------------------~-> Sharing the knowledge on Axapta. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/Axapta-Knowledge-Village/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
