Performance Issues: comparing 2 db (1 Viewer)

[smbrr]

Hello,

An intern joined for a few month, a few months ago, and developped an Access database. He was in comp sci school but having worked with him a bit, he was not very good at anything computer related, especially not programming and SQL.
Anyways, I had too much work and let him do his thing. The end result needed a lot of polishing (i've been polishing it for months already) but it worked alright if you didnt look too deep.

The database schema was so simple it's not even a schema: the dude just took the 3 excel sheets our data is based on and created 3 tables from them. No foreign keys, no 'brand' table or 'product type' table, no indexes, just a big load of all the info in a table.
For our user to load the databases, we make excel or access VBA macros. In all of his, he used the access import tool line, and that caused a boatload of issues that i'm still resolving to this day.

On the other hand I have another database I made myself, with lots of tables and foreign keys to reduce the weight, indexes put on the heavy use fields to speed up querying.
All my macros to load it run through ADODB recordsets.

Both database are very similar in one thing: they have that one main table with all the big data in it.

So I made essentially the same query for both: queried directly from access all the fields on the main table (no joins) with a parameter on the "year-month" field (long integer) set at 201410.

Main table in my db: 3 millions records overall.
Query results: 50k records, 5 minutes to display it

Main table in his db: 16 millions recods overall.
Query results: 660k, less than 5 seconds to display it

Yes, his db runs much better than mine. I have 5 indexes on this table, could this be causing the slowing down? Could it be that he used access loading tool and I used ADODB? (but if this was the case, it should be gone after I repair&compact both, and I did that a lot)
Could it be that my table does have like 15 columns more than his? (the number of fields doesn't factor in, does it?)

If you read this story and something becomes evident to you that I didn't realize please do tell, because I'm losing my self-esteem quickly

Thank you

Here are the queries
Mine:

SELECT VIQMEL.*, VIQMEL.Annee
FROM VIQMEL
WHERE (((VIQMEL.Annee)=201410));

His:

SELECT [Retail Info].*, [Retail Info].Période
FROM [Retail Info]
WHERE ((([Retail Info].Période)=201410));

 

[spikepl]

So is there or is there not an index in each table on the WHERE clause parameter? If that specific column is not indexed then this query will be slow, irrespective of how many indexes you have.

And what exactly is being measured in each: time to execute query extracting data from one already existing table and displaying it directly on screen in datasheet view of the query ?

 

[smbrr]

Yes, indexes on the WHERE parameter in each tables, because I indexed it in his table.

Time measured is from clicking "execute" to getting the number of returned records in the little box at the bottom.

I didnt measure it accurately because the difference in time is so huge, when his table has so much more rows to handle.

Another thing to note, my table is also linked into another database, but I don't think that factors on performance since I ran the query directly in it.

I don't get how two same query using the same data type and index can be so incredibly slower.

 

[spikepl]

the number of fields doesn't factor in, does it?

It definitely does, any text you search for will tell you to ask only for the data you need. I just do not know how much, If his query yields one column and yours 1+15 ...?

And you do ask for redundant data in that the query column with the criterion will be shown twice.

Another thing to note, my table is also linked into another database, but I don't think that factors on performance since I ran the query directly in it.

So now we have yet another db mixed in. So where is the actual data, and on what db is the query executed? Remember we know only what you write - we cannot see your setup.

 

[smbrr]

He returns about 15 fields and I return 30, but even reducing this to 1 field only, it doesn't seem to go faster.

As for the other database, it's just using my table through ODBC link, but I made my query directly in the file that has the table in it.

 

[spikepl]

I made my query directly in the file that has the table in it.

a
and you ran it there too?

and both test db's are located on same hardware?

 

[smbrr]

Yes to both. I ran them together on my computer. Maybe I should have ran only 1 at a time but Access doesnt seem to use much of my cpu/ram if any at all, honestly.

 

[spikepl]

As far as I can see the only potential difference remaining is data. If you have some memo or embedded ole fields that are more "full" than his. What are the field types involved?

Anybody else with any other take on this: please do chime in.

 

[smbrr]

No, on the contrary, my fields are limited in size (ie I set some text field at 20 chars because I don't need more) when his are 255 chars etc

No memo or OLE or images or anything weird.