help for the speed of this query?

SELECT DISTINCT  some fields from table A, B, C, D, E FROM A, B, C, D, E WHERE A.ID = B.ID AND A.ID = C.ID AND A.ID = D.ID AND A.ID = E.ID; ID is primary key for table A and are foreign keys for tables B, C, D, E. It is one to many relationship Each table has around 500 records. I do a query like above. It takes around 6 seconds. I try to create index for ID in table B, C, D and E. But it not help. Can someone show me anyway I can improve the speed for this query. I have SQL Server 2000. Thanks any help.         __________________________________ Do you Yahoo!? Friends.  Fun.  Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/

----- Excess quoted text cut - see Original Post for more ----- Use the JOIN statement rather than just doing it in the WHERE - it should speed up your query in one "simple" step

John Ho wrote: ----- Excess quoted text cut - see Original Post for more ----- Please show us the query execution plan. Jochem

To clarify... What usually happens when you do A,B,C,D,E is that the dbms creates a "Cartesian Product" which is of size size(a) x size(b) x size(C) x size(D) x size(E) and only then does it start restricting the results using the WHERE clause A Join, on the other hand works incrementally, 2 tables at a time, applying the "WHERE/ON" join restriction as it goes, and is thus significantly more efficient. -Dov   _____ Sent: Monday, June 07, 2004 9:50 AM To: CF-Talk Subject: RE: help for the speed of this query? ----- Excess quoted text cut - see Original Post for more ----- Use the JOIN statement rather than just doing it in the WHERE - it should speed up your query in one "simple" step   _____  

I change the above query to SELECT DISTINCT  some fields from table A, B, C, D, E FROM A JOIN  B ON A.ID = B.ID JOIN C ON A.ID = C.ID JOIN D ON A.ID = D.ID JOIN E ON A.ID = E.ID; BUT still run 6 second like old query. ----- Excess quoted text cut - see Original Post for more -----

try this: select distinct myFields from (((A inner join B on B.ID=A.ID) inner join C on C.ID=A.ID) inner JOIN D on D.ID=A.ID) inner join E on E.ID=A.ID is this what your query looked like? If not, does this help it? -dov   _____ Sent: Monday, June 07, 2004 10:44 AM To: CF-Talk Subject: Re: help for the speed of this query? I change the above query to SELECT DISTINCT  some fields from table A, B, C, D, E FROM A JOIN  B ON A.ID = B.ID JOIN C ON A.ID = C.ID JOIN D ON A.ID = D.ID JOIN E ON A.ID = E.ID; BUT still run 6 second like old query. ----- Excess quoted text cut - see Original Post for more -----   _____  

yeah I use either join or innter join like you. And both has same speed 6second. ----- Excess quoted text cut - see Original Post for more -----

John Ho <johnho70@yahoo.com> 06/07/2004 11:11 AM Please respond to cf-talk         To:     CF-Talk <cf-talk@houseoffusion.com>         cc:         Subject:        Re: help for the speed of this query? Dov Katz wrote: size(a) x size(b) x size(C) x size(D) x size(E) and only then does it start restricting the results using the WHERE clause I was reading Joe Celko's chapters on trees/hierarchies last week and one small point stuck -- Celko states that performance diminishes when querying 5 or more tables ... now I have a better understanding why (cartesian product). btw, the Celko books are well worth the time/money! http://www.celko.com/books.htm) Also, I just ran a test against a 'normalized' table (setup like 1-1-1-1-1-1-many...) <cfquery name="test" datasource="#application.dsn#">         SELECT                 ObjectivesMaster.Objective,                 ObjectivesXref.LangId,                 ObjectivesXref.TrackId,                 ObjectivesXref.CategoryId,                 ObjectivesXref.GroupId,                 ObjectivesXref.TaskId,                 ObjectivesXref.ObjectiveId,                 ObjectivesXref.ListOrder,                 ObjectivesXref.Notes         FROM                 ObjectivesMaster, ObjectivesXref         WHERE                 ObjectivesXref.LangId = #t#         AND                 ObjectivesXref.TrackId = #u#         AND                 ObjectivesXref.CategoryId = #v#         AND                 ObjectivesXref.GroupId = #w#         AND                 ObjectivesXref.TaskId = #x# </cfquery> This took about 200 milliseconds on my 'average' laptop (1 ghz/256mb) running ms access. Currently there are about 7000 records in this ...Xref table and when it goes into production, this particular table will grow to 20K records (or more... and hopefully leveling off before it hits 6 figures!). The target DB is to be Oracle 9i once we fill out more paperwork than a pentagon lawyer (and undergo the interrogation of the IT security analysts ;) I've had the opportunity to rebuild this structure from scratch and have tried to embrace 'normalization' concepts for 2 basic reasons: 1) tighter/easier code and 2) SPEED! I hope some of this is helpful, -mike John Ho wrote: yeah I use either join or innter join like you. And both has same speed 6second. ----- Excess quoted text cut - see Original Post for more -----

mavinson@ra.rockwell.com wrote: > > size(a) x size(b) x size(C) x size(D) x size(E) and only then does it > start restricting the results using the WHERE clause Probably, but we don't know that before we see the query execution plan, which John still hasn't sent. But if it does, you might want to force the planner to push the restrictions down in the tree: SELECT DISTINCT A.id, B.w, C.x, D.y, E.z FROM   A,   (SELECT DISTINCT w FROM B) AS B,   (SELECT DISTINCT x FROM C) AS C,   (SELECT DISTINCT y FROM D) AS D,   (SELECT DISTINCT z FROM E) AS E WHERE   A.ID = B.ID   AND A.ID = C.ID   AND A.ID = D.ID   AND A.ID = E.ID; > I was reading Joe Celko's chapters on trees/hierarchies last week and one > small point stuck -- Celko states that performance diminishes when > querying 5 or more tables ... now I have a better understanding why > (cartesian product). That very much depends on whether the query can be optimized and how smart the optimizer is. Jochem

I dont know why this query run with same speed 6second, doesn't matter when I use join or not. I am afraid I will have problem when this database becomes large. Currently if I do select distinct join of 5 tables I have around 1000 records and it runs about 6 seconds. I think it is slow. I have very fast machine.

John Ho wrote: > I dont know why this query run with same speed 6second, doesn't matter when I use join or not. Show us the execution plan. Jochem

Jochem van Dieten wrote: "...the query execution plan..." What's a query execution plan? -mike

mavinson@ra.rockwell.com wrote: > Jochem van Dieten wrote: "...the query execution plan..." > > What's a query execution plan? It is a high level overview of the way in which a database will execute the query, i.e. which indexes it will use, in what order it will do joins and sorts etc. A pretty decent explanation of the path a query takes through a database can be found at http://www.postgresql.org/docs/7.4/static/overview.html Obviously the details (probably the entire paragraph on the rule system) are specific to PostgreSQL, but the key concepts are shared amongst most databases. Jochem

This is somewhat off topic, but MySQL will give you a look at the execution plan for a specific query by prefixing the query with the 'explain' keyword. It lists the tables in the order they will be joined, what indexes will be used, how "good" the relationship is, and a few other things.  It also gives some feedback about the use of the where clause, both for selection, and in processing the joins. I'm sure other DBs have similar functionality, and I've found it quite useful in getting those last few milliseconds out of complex, but performance crucial, queries. Cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

> Jochem van Dieten wrote: "...the query execution plan..." > > What's a query execution plan? When you send an SQL query to your database, the database figures out what it thinks is the most efficient way to process that query. This is the execution plan. You can usually take a look at this plan, and see if it really is the most efficient way to run the query. If you're using SQL Server, you can do this very easily from Query Analyzer by clicking on the "Display Estimated Execution Plan" button on the toolbar, or by using SET SHOWPLAN within an SQL batch. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444

thanks all for your help. I change query like someone show  like and run better less than 1 second. SELECT DISTINCT A.id, B.w, C.x, D.y, E.z FROM   A,   (SELECT DISTINCT w FROM B) AS B,   (SELECT DISTINCT x FROM C) AS C,   (SELECT DISTINCT y FROM D) AS D,   (SELECT DISTINCT z FROM E) AS E WHERE   A.ID = B.ID   AND A.ID = C.ID   AND A.ID = D.ID   AND A.ID = E.ID;

Thanks! Jochem van Dieten wrote: http://www.postgresql.org/docs/7.4/static/overview.html -mike