Solved

Simplifying SQL Statement

Posted on 2010-09-22
8
465 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
Industry Leaders: 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!

 
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

Technology Partners: 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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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