Import queries and UPEND operation (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 15:04
Joined
Sep 21, 2011
Messages
14,044
Well at the bottom of that link Colin states :

NOTE:

Indexing the fields will make the process slower as the indexes will also need to be updated
 

jaryszek

Registered User.
Local time
Today, 08:04
Joined
Aug 25, 2016
Messages
756
ok thank you.
Sorry i didnt notice that.

Ok so it is possible to fasten delete queries in this case?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:04
Joined
Oct 17, 2012
Messages
3,276
The word you want is 'hasten'. 'Fasten' means something completely different. :)

Indexes covering your WHERE clause *MIGHT* speed things up - it depends on whether the search speed enhancement from the WHERE clause is greater than the reindexing speed penalty the index itself imposes.

Basically, it's probably going to be on a case-by-case basis, but I think the answer for the vast majority of cases is going to be that the index slows things down overall. The only way to be sure, however, is to run a bunch of comprehensive tests.

If only I knew someone around here who likes to do that sort of thing....
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:04
Joined
Feb 19, 2013
Messages
16,553
it really depends on how many records are in the temp table and how complex the query is to update the destination tables.

Appending data to an indexed temp table will be noticeably slower for a large dataset. In which case I would create the temp table without indexes, populate it and then add the indexes which will be much faster.

Depends on how you are updating but another way is to link to the source directly - something like

Code:
INSERT INTO tblDestination (fld1, fld2....)
SELECT (fl1, fld2....)
FROM (SELECT * FROM [Sheet1$A:Z] IN 'C:\…..\myExcel.xlsx'[Excel 12.0 xml; HDR=Yes;IMEX=2;ACCDB=Yes] WHERE somecriteria) as XL
 

isladogs

MVP / VIP
Local time
Today, 15:04
Joined
Jan 14, 2017
Messages
18,186
I thought I had covered the pros and cons of indexing in my various articles.

Indexing speeds up searching. The improvements can be dramatic on large datasets e.g. 250x faster on fields with a large number of possible values
However indexing slows down both append and update queries. In my tests it added about 50% to the time.

Also, upend queries are in my experience slower than doing both append and update separately

So whether to index may depend on which of these you need to do most often
So I agree with previous comments that decisions should be made on a case by case basis
 

jaryszek

Registered User.
Local time
Today, 08:04
Joined
Aug 25, 2016
Messages
756
thank you Guys!

The word you want is 'hasten'. 'Fasten' means something completely different.

good point!

Indexing speeds up searching.

Ok i read a lot that indexes speed up searching but what kind of searching we are talking about? To use filter on query or what? What does searching means here?

Best wishes,
Jacek
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:04
Joined
Feb 19, 2013
Messages
16,553
there are three methods of searching

find - where a searchvalue is found in an existing recordset - but still displays all records
filter - where a recordset is reduced to display just the records where the searchvalue is found
criteria - where a recordset is created to contain only those records where the searchvalue is found. I would include joins as effectively being a criteria

all will perform better with indexing - the one exception being when you are searching for a value within a value - i.e. like *searchvalue* or like *searchvalue. However Like searchvalue* will still use indexing

Sorting also benefits from indexing - but don't over index, just index those fields regularly sorted on

see this link for more about indexing https://www.access-programmers.co.uk/forums/showthread.php?t=291268
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:04
Joined
Feb 28, 2001
Messages
26,999
This topic moved so quickly I didn't notice it earlier. Here is my take: The question of deletion falls into two parts.

Part A: Defining what you want to delete - will greatly benefit from the presence of indexes.

Part B: Deleting what you have defined - will be slowed down by the need to maintain the indexes as well as the associated records.

There is only ONE WAY (and this has been discussed before) to quickly get rid of a lot of data from temp tables. That is to delete the whole table at once. Which of course leads to bloat very quickly. Which you then avoid by putting the temp tables in separate files that you map, use, unmap, and delete (the WHOLE FILE). That's as fast as it gets when dealing with huge amounts of data. OR you never use a temp table at all - you just map to the original source.

If you are using indexes to be selective AND you are not deleting everything when you are done, then you cannot delete your temp tables. BUT you could do something to mark them so that subsequent operations ignore the deleted records. Because there is a third strategy. Add a field to the temp table that says "MarkedForDelete" (or pick a shorter name like "Used"). Then in subsequent queries, add the criteria "AND (Used=False)" so you don't re-use a used record. But DON'T DELETE IT! Just leave it around until you are ready to do a bulk operation that will erase the whole thing.

Stated another way - if this IS just a temp table, DON'T delete ANYTHING a little bit at a time. Just mark stuff as unusable and then, when you are done, get rid of the WHOLE THING AT ONCE. Why delete in retail when you eventually plan to delete wholesale?
 

jaryszek

Registered User.
Local time
Today, 08:04
Joined
Aug 25, 2016
Messages
756
thank you , great explanation The_Doc_Man, love your ideas and solutions

Best wishes,
Jacek
 

Users who are viewing this thread

Top Bottom