HUGE ODBC table, SLOOOW Query, ideas/answers?

[CptCrusty1]

I'm trying to do research against an ODBC (Oracle) table that is 1.4 million records and about 40 fields. This table is large and touched by many hands throughout the day. My queries, even simple ones where I've reduced the number of fields, are very very slow, or lock up. Without going to a PassThrough query (which I could do, but, its slower to write), would reducing the table size (archiving some records) speed things up?

Thanks.
Crusty

I live to work and I work to live.

 

[r937]

pass-through queries are slower to write????

r937.com | rudy.ca

 

[CptCrusty1]

Only slower to write in that it you have to actually type it out rather than drag and drop. On-the-fly querying is always faster with the QBE.

I live to work and I work to live.

 

[lynchg]

Yes, archiving records would speed things up. Also check the indexes on your Oracle table, and if you have a DBA that could perform explains on your SQL, it would help make things run faster.

 

[CptCrusty1]

The crappy thing about this massive table is that it has NO indexes. Its a very poorly designed table, but it was tiny when it was built. As is the case with these things, I grew with regular feeding and now we're stuck with it.....

I live to work and I work to live.

 

[lynchg]

Do you have anyone with DBA abilities that could perform a "tune-up" on that table?

Linking from Access to an un-indexed Oracle table with 1.4 million records is a guarantee of poor performance. Archiving will help some, but indexing fields that you commonly use in your queries would likely make a huge difference.

Regular maintenance of tables (especially large ones), is a necessary part of using databases.

 

[CptCrusty1]

There are so many people touching this table that the impact of just "changing" things needs to be researched and evaluated. Thus a blanket change will not happen quickly.

Would anyone have a link to an article or maybe some white papers to support this, i.e. reducing the number of records?

Crusty.

I live to work and I work to live.

 

[lynchg]

Reducing the number of records is easy, you just need to know what criteria you want to use to decide which records get archived.

Do you have audit fields in this table? Like the date the record was originally inserted? and/or last updated?

Someone needs to decide which records can be removed, then you can get help here on how to carry that out.

 

[PHV]

Anyway, just creating index in an existing table will not have any other side effect than eventually speeding things up ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

I just learned that there are two indexes on the table, although, that doesn't change the size of the table. Since I keep crashing, I'm building a PassThrough to help me with the here and now issue and I'll deal with the deleting later.

If anyone has any links to anything supporting this, I'd love to have some documentation. The IT pukes (not all IT pukes are pukes...) would never take my word for it since 1. I'm not an IT puke, and 2. since I'm not an IT Puke, I can't possibly know what I'm talking about and know how to do research.... LOL
Thanks.
Crusty

I live to work and I work to live.

 

[lespaul]

Like, PHV said, adding an index to a table doesn't make ANY changes that would affect anything except the speed of the queries using those fields.

 

[r937]

adding an index to the table does reduce the speed of INSERTs and DELETEs

but it sounds like with this table, nobody would notice

regarding a link to supporting information, give your IT folks the link to this thread

feel free to have them contact me through my site (see sig), i will simply tell them to add the indexes necessary to optimize your pass-through SQL

come to think of it, you should go through channels and have them write the pass-through SQL for you, and hand it over to you only after they've tested it and are willing to stand behind its performance

i love going through channels



r937.com | rudy.ca

 

[CptCrusty1]

Ah... There's the rub...

1. I called them Pukes.. they might not like that.
2. If I request something, it takes TWO MONTHS to have the process completed and put into production.
3. They are snobby and would thumb their collective noses at the thought of consulting an outside source (which is why I'm looking for written papers/docs/etc). Thanks for the offer tho....

I live to work and I work to live.

 

[r937]

two months?

you might think that a slowly performing query is a technical problem

but you don't have technical problems

you have management problems

two months!!

okay, request it anyway

;-)



r937.com | rudy.ca