Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Simplifying SQL Statement

Posted on 2010-09-22
8
Medium Priority
?
474 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 1336 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 1336 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 664 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

971 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