Accessing large tables

Question

Accessing large tables

Jéan Moore

SSC Rookie

Points: 43
More actions
August 10, 2006 at 1:20 pm

#116049

I am trying to access a very large table through a slow connection (64K) with the following statement:

SELECT DISTINCT location
FROM sites
WHERE ( item_no = 54234)

There are over 500 Million records and I keep getting timeout errors. What can I do to get the data into my report?

A million thanks in advance......

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

The issue is likely not the network speed, it's the size of the table and the potential query plan required.

Is there an index on the table with column [item_no] as the 1st index column ? How about [location], is it in any index ? How many potential distinct values of [location] are there ?

Lowell SSC Guru Points: 323546 More actions · Answer 2

several things you can do:

change your default timeouts: the default is 30 seconds.

dim Conn as adodb.Connection

Conn .CommandTimeout = 300 'seconds=5minutes

Conn .ConnectionTimeout = 300 'seconds=5minutes

next i would suggest changing your statement to ignore any locks that might be slowing you down:

SELECT DISTINCT location

FROM sites NOLOCK

WHERE ( item_no = 54234)

you may not have permissions to do this, so you might want to get with the DBA about adding/checking the existing indexes:

lastly, make sure there is an index on item_no, since that is what you are searching for;

you should check the existing indexes first to make sure it isn't already covered by an index, but if it didn;'t exist, you'd want something like this:

CREATE INDEX IX_ITEM_NO on sites(item_no,location)

[edited after posting] PW says the same thing: check existing indexes, see if you can put one on; we posted at the same time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jéan Moore SSC Rookie Points: 43 More actions · Answer 3

I'm accessing this from a remote system that I have only read only access. There's an index on the location column but not on the item_no. Thr return records should not exceed a couple of hundred records.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

>>but not on the item_no

You'll have to increase the timeout, then. With no index on item_no, you're looking at a 500 million row tablescan which will take a considerable amount of time.

Jéan Moore SSC Rookie Points: 43 More actions · Answer 5

Well all,

I appreciate your wisdom. I'll see what I can do to convence the admin over there to change things. Again, you all are awsome.....