optimizing sql

[powerprinting]

I understand that SQL statements are optimized when you include a WHERE statement as long as there exists an index on the fields you specify in the condition. Does it follow that this statement is not optimized?

select * from mytable;
inner join zfield2 on zfield2.thisfield=mytable.thisfield;
inner join zfield2 on zfield2.thisfield=mytable.zfield;

My question is would my statement better optimized for speed if I add a WHERE to it?

FYI, If it interests anyone, I've also learned that if you have deleted records in a database and you want optimization, it helps to have a an index on deleted ex: index on deleted() tag delrec. This is only if deleted is set to 'on'. If 'off', its aleady optimal.

 

[craigber]

Without knowing what the index tags are, there is no way to determine if the statement is fully optimized. However, you need to put the table.field information on the left-hand side of the equal sign.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[Mike Lewis]

Powerprinting,

would my statement better optimized for speed if I add a WHERE to it?

No. Simply adding a WHERE does not optimise the query, even if the fields in the WEHRE clause are indexed. The issue is more the reverse: If you have a WHERE and the fields are not indexed, the query won't be optimised.

In your case, it's more important to make sure that there are indexes on the fields used in the join. If mytable.thisfield and mytable.zfield are indexed, you will get much better performance than if they are not.

if you have deleted records in a database and you want optimization, it helps to have a an index on deleted

That's a commonly held view, but it's not quite as simple as that. In many cases, what you say is correct. In those cases, it also helps to either have DELETED set ON, or to include WHERE NOT DELETED() in the query. However, in other cases, doing that will actually slow down the query. It all depends on the 'spareseness' of the deleted records.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[powerprinting]

Craig, are you saying that this
inner join zfield2 on zfield2.thisfield=mytable.zfield

should actually be this?
inner join zfield2 on mytable.zfield=zfield2.thisfield

About deleted records.

My understanding was that with deleted set to ON, since deleted records would not appear in the result, that Foxpro essentially adds "and ! deleted()" to the clause. Which also simply means, no index, no optimization. I guess its nickpicking in a way, but we require deletes for a period of time and speed is an issue to us. Thanks for the responses.

 

[craigber]

That's what I'm saying.

As for deleted records, YMMV...you need to test with SET DELETED ON/OFF and an index on deleted as the results depend on the percentage of deleted records in the table.

Reading up on Rushmore will help you understand this all. There is an article on my web site that will get you started.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[powerprinting]

thanks Craig

 

[MarciaAkins]

Hello Powerprinting.

>> FYI, If it interests anyone, I've also learned that if you have deleted records in a database and you want optimization, it helps to have a an index on deleted ex: index on deleted() tag delrec. This is only if deleted is set to 'on'. If 'off', its aleady optimal. <<

FWIW, there are occasions where a fully optimized query will run more slowly than one that is partially optimized.

You may want to check out the May 1999 issue of FoxPro Advisor for an article by Chris Probst called "Rushmore -- Less is More"

Rushmore -- Less Is More
In some cases, conventional wisdom leads to slower performance.

Article Summary:

For the best performance, should you always have an index tag for every expression used for filtering? Maybe not!

Also, you can use SYS( 3054 ) to check to see if the query is fully optimized. You can check out the deatils of SYS( 3054 ) in the on-line help.





Marcia G. Akins

http://www.TightlineComputers.com