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: Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of James
Flavell
Sent: Wednesday, 28 September 2005 2:57 PM
To: Axapta-Knowledge-Village@yahoogroups.com
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
------------------------ 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/
