Solved

Simplifying SQL Statement

Posted on 2010-09-22
8
460 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
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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now