Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 28th, 2004, 04:08 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default how to make the following query as efficient as po

There is a parameter in WHERE clause. I want to use the following result (say A) inner join another query (say B) which also has the same parameter in WHERE clause to get the query C. The query speed is slow.(we can take the parameter criteria away from where clause in A and B, and put it in C's where clause). I do not know if there is a good way to make the query as efficient as possible.

Thanks for your concern, and thank you for your any answer in advance!

SELECT DTree.DataID, DTree.Name, LLAttrData.VerNum,
    LLAttrData.DefID, MAX(CASE WHEN ((DefID = 1682417 AND
    AttrID = 3) OR
    (DefID = 1682385 AND AttrID = 2) OR
    (DefID = 1682445 AND AttrID = 3) OR
    (DefID = 1682425 AND AttrID = 3) OR
    (DefID = 1682453 AND AttrID = 2)) THEN ValStr ELSE NULL
    END) AS DwgNo, MAX(CASE WHEN ((DefID = 1682417 AND
    AttrID = 8) OR
    (DefID = 1682385 AND AttrID = 5) OR
    (DefID = 1682445 AND AttrID = 7) OR
    (DefID = 1682425 AND AttrID = 7) OR
    (DefID = 1682453 AND AttrID = 6)) THEN ValStr ELSE NULL
    END) AS Sheet, MAX(CASE WHEN ((DefID = 1682417 AND
    AttrID = 6) OR
    (DefID = 1682385 AND AttrID = 4) OR
    (DefID = 1682445 AND AttrID = 5) OR
    (DefID = 1682425 AND AttrID = 5) OR
    (DefID = 1682453 AND AttrID = 4)) THEN ValStr ELSE NULL
    END) AS Version, MAX(CASE WHEN ((DefID = 1682417 AND
    AttrID = 11) OR
    (DefID = 1682385 AND AttrID = 8) OR
    (DefID = 1682445 AND AttrID = 8) OR
    (DefID = 1682425 AND AttrID = 8) OR
    (DefID = 1682453 AND AttrID = 7)) THEN ValStr ELSE NULL
    END) AS Title
FROM DTree INNER JOIN
    LLAttrData ON (DTree.VersionNum = LLAttrData.VerNum) AND
    (DTree.DataID = LLAttrData.ID)
WHERE (((DTree.Name) LIKE '%parametr%') AND ((LLAttrData.DefID)
    = 1682417 OR
    (LLAttrData.DefID) = 1682385 OR
    (LLAttrData.DefID) = 1682445 OR
    (LLAttrData.DefID) = 1682425 OR
    (LLAttrData.DefID) = 1682453))
GROUP BY DTree.DataID, DTree.Name, LLAttrData.VerNum,
    LLAttrData.DefID
  #2 (permalink)  
Old May 28th, 2004, 10:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

Adding indexes to the where clause parameters can help speed up the query.

Brian


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I make this query michael193nj Access 3 March 19th, 2008 05:13 PM
Help with efficient coding (part of Dynamic Query miamikk SQL Language 1 July 17th, 2007 05:32 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.