Solved

Simplifying SQL Statement

Posted on 2010-09-22
8
466 Views
Last Modified: 2012-05-10
Does anyone know if this is particularly inefficient and can be simplified to improve efficiency?

 
SELECT DISTINCT c.chartid FROM Chart c, Series s, SeriesLinkData sd, Data_Bloomberg bb
WHERE c.chartID = s.chartID And s.seriesID = sd.seriesID  And sd.DataID = bb.DataID
AND ( bb.Name LIKE '*Gluck*' OR bb.BbSymbol LIKE '*Gluck*' OR bb.BbType LIKE '*Gluck*' OR bb.BbName LIKE '*Gluck*' )
UNION
SELECT DISTINCT c.chartid FROM Chart c, Series s, SeriesLinkData sd, Data_LocalDB db
WHERE c.chartID = s.chartID And s.seriesID = sd.seriesID  And sd.DataID =db.DataID
AND ( db.Name LIKE '*Gluck*' OR db.DbDomain LIKE '*Gluck*' OR db.DbTag LIKE '*Gluck*' )
UNION
SELECT DISTINCT c.chartid FROM Chart c, Series s, SeriesLinkData sd
WHERE c.chartID = s.chartID And s.seriesID = sd.seriesID 
AND ( c.Title LIKE '*Gluck*' OR sd.Name LIKE '*Gluck*' OR sd.DbCriteria LIKE '*Gluck*' OR sd.BbField LIKE '*Gluck*' )

Open in new window

0
Comment
Question by:billelev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:daddotnet
ID: 33740257
Try each of the queries separately, getting a query plan if you're able, and find out if it's one or more of them is causing performance issues that you can target specifically.

If you are able to create indexes on the tables, create them using the fields you're joining on as unique indexes (if applicable). If indexes already exist, ensure you're using all fields in the primary/foreign keys that apply to all tables in your joins.

If you are dealing with very large sets of data, run preprocessing queries to pull the data you're interested in (by date range, for example) into temporary tables then run your output queries against those temporary tables.

-DdN
0
 
LVL 8

Expert Comment

by:rushShah
ID: 33740815
hi try this query..

SELECT		DISTINCT c.chartid 
FROM		Chart c
INNER JOIN	Series s ON c.chartID = s.chartID
INNER JOIN	SeriesLinkData sd ON s.seriesID = sd.seriesID
LEFT JOIN	Data_Bloomberg bb ON sd.DataID = bb.DataID
LEFT JOIN	Data_LocalDB db ON sd.DataID =db.DataID
WHERE		( bb.Name LIKE '*Gluck*' OR bb.BbSymbol LIKE '*Gluck*' OR bb.BbType LIKE '*Gluck*' 
			OR bb.BbName LIKE '*Gluck*' )
			OR ( db.Name LIKE '*Gluck*' OR db.DbDomain LIKE '*Gluck*' OR db.DbTag LIKE '*Gluck*' )
			OR (c.Title LIKE '*Gluck*' OR sd.Name LIKE '*Gluck*' OR sd.DbCriteria LIKE '*Gluck*' 
			OR sd.BbField LIKE '*Gluck*')

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 334 total points
ID: 33741564

One change in above query.
use INNER JOIN instead of LEFT OUTER JOIN.

secondly,
in your third union, u use c.Title LIKE '*Gluck*' so in above query that will be common.is this ok?

third point is Chart c, Series s are common.
so u can make accss query of that two and then join with other table.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 334 total points
ID: 33741623

regards to my 3rd comment

-------------------------------------------------------

Save below is access query - ChartData

SELECT      c.chartid,c.Title,s.seriesID,sd.DataID,sd.Name,sd.DbCriteria,sd.BbField
FROM
            Chart c INNER JOIN Series s
ON c.chartID = s.chartID INNER JOIN SeriesLinkData sd
ON s.seriesID = sd.seriesID

Your Main Query will be

SELECT C.Chartid
FROM
      ChartData C, Data_Bloomberg bb, Data_LocalDB db
WHERE
      C.DataID = bb.DataID
AND C.DataID =db.DataID
AND
 ( bb.Name LIKE '*Gluck*' OR bb.BbSymbol LIKE '*Gluck*' OR bb.BbType LIKE '*Gluck*' OR bb.BbName LIKE '*Gluck*' )
OR ( db.Name LIKE '*Gluck*' OR db.DbDomain LIKE '*Gluck*' OR db.DbTag LIKE '*Gluck*' )
OR (c.Title LIKE '*Gluck*' OR sd.Name LIKE '*Gluck*' OR sd.DbCriteria LIKE '*Gluck*' OR sd.BbField LIKE '*Gluck*')
0
 

Assisted Solution

by:irenetheodora
irenetheodora earned 166 total points
ID: 33742335
SELECT DISTINCT c.chartid
  FROM Chart c
left join Series s
        on c.chartID = s.chartID
left join SeriesLinkData sd
        on s.seriesID = sd.seriesID
left join Data_Bloomberg bb
        on sd.DataID = bb.DataID
left join Data_LocalDB db
        on sd.DataID =db.DataID

WHERE  bb.Name LIKE '*Gluck*'
      OR bb.BbSymbol LIKE '*Gluck*'
      OR bb.BbType LIKE '*Gluck*'
      OR bb.BbName LIKE '*Gluck*'
      OR c.Title LIKE '*Gluck*'
      OR sd.Name LIKE '*Gluck*'
      OR sd.DbCriteria LIKE '*Gluck*'
      OR sd.BbField LIKE '*Gluck*'
      OR db.Name LIKE '*Gluck*'
      OR db.DbDomain LIKE '*Gluck*'
      OR db.DbTag LIKE '*Gluck*'
0
 
LVL 1

Expert Comment

by:amarsale
ID: 33745506
put a join first on the tables which have less number of rows.
0
 

Author Closing Comment

by:billelev
ID: 33750093
no
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33751296
No means?
u didnt get solution?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question