Explain (more is better?)

We have a large number of tables > 700 with a large majority in the range of > 1GB Some of the queries on the database can take a number of minutes especially when multiple joins are being performed. Now I have always assumed the lower the cost of query the better :-) However I am starting to doubt this when I run a low cost query on a number of frequent hit tables it can take upto 2 mins to return data If I re-structure the query the cost goes up to 2500 but data is returned in seconds ? Would I be right in assuming the cost is based on use of indexes and amount of data retrieved and not a lot else so a high cost query is not always bad. and if so what about OPTCONF hmm 0,1 or 2 :-)

In article <fApY5.29470$eT4.2358818@nnrp3.clara.net>, John Berry <johnberry@clara.net> writes >We have a large number of tables > 700 with a large majority in the range >of > 1GB > >Some of the queries on the database can take a number of minutes especially >when >multiple joins are being performed. > >Now I have always assumed the lower the cost of query the better :-) > >However I am starting to doubt this when I run a low cost query on a number >of frequent hit tables it can take upto 2 mins to return data >If I re-structure the query the cost goes up to 2500 but data is returned in >seconds ? > Run you run the full update statistics? Perhaps the stat are out of date. >Would I be right in assuming the cost is based on use of indexes and amount >of data retrieved and not a lot else so a high cost query is not always bad. > >and if so what about OPTCONF hmm 0,1 or 2 > OPTCOMPIND = 0 favors indexes and should be used on OLTP systems. >:-) > > -- David Williams