Solved

Simplifying SQL Statement

Posted on 2010-09-22
8
461 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

14 Experts available now in Live!

Get 1:1 Help Now