SQL takes forever

L

loftty

New Member
Hi All,

I have the below query which is quite simple, but there is 3.5 million records, so the query takes forever. Is there some magic trick I can do to make it faster?

SELECT num
FROM pub.jobevt

I have also tried this but still takes quite a while


SELECT num
FROM pub.jobevt
WHERE num='1'

Any help is much appreciated.

Regards,

Loftty
 
There never was a magic button, there is none and there won't be any.

If I would need to speculate I would say that you're running an ancient version of ? and probably the field you're querying after is not indexed. But, as I said, this would be speculation ...

HTH, RealHeavyDude.
 
Hi,

I have progress openedge 10.1a driver installed the version is 5.10.0037. I have ran the update SQL to create the indexes for that table but it had no effect. These are what I ran.

update table statistics for pub.jobevt
update index statistics for pub.jobevt

Regards,

Loftty
 
Did you COMMIT WORK?

What you have done is "update statistics". That does not create indexes, it simply provides the optimizer with data regarding which (if any) existing index would be the most effective one to use. If the db schema has no index on the "num" field then you will always have to scan the entire table to resolve the query. Regardless of whether or not you have updated the statistics.

Also -- if you really and truly need to retrieve 3.5 million records it will take some time. At a guess a minimum of a few minutes even on a very well tuned system.
 
Hi,

No I did not commit my work.

I have now tried
update table statistics for pub.jobevt; commit; but I get the error "System.Data.Odbc.OdbcException: ERROR [HY000] [DataDirect][ODBC OPENEDGE driver][OPENEDGE]Syntax error in SQL statement at or about "; commit;" (10713)"

Any idears?

Regards,

Loffty
 
You must log in or register to reply here.
Back
Top