wannabe google fast

[bill shockley]

Why is my db so slow?

SELECT DISTINCT allMarkets.Symbol
FROM allMarkets;

This query returns about 5000 symbols from a table of 15 million records in about 15 seconds.

Table is un-indexed. 4 fields: Symbol, Date, Close, Market

"Market" field entries are one of four single letters: Y,N,M, or A.
"Symbol" field entries are 3-5 letter symbols like MSFT or CSCO.
"Date" is short date.
"Close" is a single-type number like 46.76.

All fields are occupied.

Total database size is 377MB, which, by my calculation, is about 6 bytes per field.

Dual core processor @~3GHz
2GB memory

[orange]

Create a index on symbol.

To see the importance of an index watch the free video tutorial here
http://www.datapigtechnologies.com/f...earchform.html

He shows performance before index and after indexing.

[TheShabz]

"Table is un-indexed"
is probably the issue at hand. And if you want to be Google-fast, ASPX + AJAX is your route.
Also, is this a local database? linked tables are dependent on your network connection.

Try running the compact+repair and see if that speeds you up any. Though honestly, 15mil records in 15 seconds isn't bad.

[ajetrumpet]

"Table is un-indexed"
is probably the issue at hand. .

if it's even an issue at all.

15 seconds for any query statement on 15 million is pretty good. I personally wouldn't be complaining

[boblarson]

Use an index. It should speed up the process considerably. Indexing is a MUST when using large datasets. Otherwise it has to search every single record when it wouldn't need to.

[bill shockley]

Create a index on symbol.

To see the importance of an index watch the free video tutorial here
http://www.datapigtechnologies.com/f...earchform.html

He shows performance before index and after indexing.

Indexing seems to have taken about 3 seconds off the
15-second one -- it was actually more like 12 seconds
before the indexing.

There's on average 3400 records per symbol
A total of about 4700 symbols
3400 * 4700 = 16 million

is this a local database?

local

15mil records in 15 seconds isn't bad.

Instantaneous on one, 64 seconds on another with identical result, only
difference the "distinct" stipulation.

And if you want to be Google-fast, ASPX + AJAX is your route.

I guess I've maxed out on speed. But I would like to
know how google does it. Will look into ASPX + AJAX, although if it costs
anything it probably won't happen.

Try running the compact+repair and see if that speeds you up any.

Had already done that. Reduced size considerably.