Hello,
I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small. From a performance standpoint, should I look at rewriting this query, and how would I do so? What is an alternative to left joins; any examples anyone has?
Thanks.
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
To the wise,
This may be a very simple problem but it's been racking may brains for a while and I just can't seem to think it through clearly.
I'm trying to return a query which uses a left join and where. I'm hoping to get a result set which shows - let us says all the departments in a company. I would like to see all the department but only the names of department heads that earn 20.000+.
In MS Access I used a subquery. My subquery returned only departments with department heads that earned 20.000+ - I then left joined the departments table to that query - no problem.
With MSSQL I've tried IN, ANY, ALL but my result sets only returns the departments that earn 20.000+ and the employees for those particular departments.
I'm thinking there must be some way of doing this without having to use a union clause.
Thanks for taking the time to read this message through.
Sincerely,
Arthur Lambiris
My understanding of relevant topics as well as SQL Books Online definition of left outer joins is that each record in the left table will be retrieved and where no associated right record exists then null values will be displayed for records in the right hand table but I've obviously misunderstood and would be grateful if someone could show me how to produce the required effect.
My scenario is pretty simple: 2 tables DiningTables and Reservations with columns as follows:
DiningTables: TBL_ID and TBL_Location - TBL_ID is the primary key
Reservationss::RES_TBL_ID and RES_Diner_Name - RES_TBL_ID is the primary key
There are 8 records in DiningTables and 4 records in Reservations and the objective is obtain the following output:
TBL_ID RES_Diner_Name
1 Jones
2 Smith
3 Bloggs
4 Mack
5 null
6 null
7 null
8 null
The SQL query I used is
SELECT Reservations.RES_Diner_Surname, DiningTables.TBL_ID
FROM DiningTables LEFT OUTER JOIN
Reservations ON DiningTables.TBL_ID = Reservations.RES_TBL_ID
That query generates 11 rows as follows:
TBL_ID RES_Diner_Name
1 Jones
1 Smith
1 Bloggs
1 Mack
2 null
3 null
4 null
5 null
6 null
7 null
8 null
I'm clealry missing something incredibly obvious and I kinda feel like the village idiot and would be extremely grateful for a clue!!
Hi All,
This may be a stupid question. But I just want to make sure i'm going in right direction.
I wrote the following query. My purpose is to retrieve all the members who submitted loan applications.
I just want to make sure my query is right? Can anyone veryfy this query?
Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins?
Code Block
SELECT Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount,
LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus
FROM Member INNER JOIN
MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN
LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN
Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN
Rate ON LoanApplication.RateFK = Rate.Id
WHERE (LoanApplication.Status = 'Submitted')
Thanks
Hi,
First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?
I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:
SELECT A.*
, F.Descricao AS FamiliasDescricao
, M.Descricao AS MarcasDescricao
, I.Descricao AS IVADescricao
FROM Artigos AS A
LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID
LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID
LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID
INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;
The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:
em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s()
em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()
If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.
Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?
Any other sugestions?
Thank you,
Hugo Lopes
Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.
These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this
emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)
emailRecipients table (emailID_PK, RecipientID, SentToEmail)
luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)
Thanks
Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.
Thanks,
Pete
Hi,
I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.
SELECT 01 as WHSE_ID
, A.On_Time
, B.Early
, C.Late
, (D.AVG_Duration / (A.On_Time + B.Early + C.Late))AS AVG_Duration
, E.DelDate
, F.*
FROM
(SELECT COUNT(SDD_Status) AS On_Time
, SDD_Date as On_Time_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) a,
(SELECT COUNT(SDD_Status) AS Early
,SDD_DATE As Early_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early'
And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) b,
(SELECT COUNT(SDD_Status) AS Late
, SDD_Date As Late_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) c,
(SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration
, SDD_Date As Stay_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) d,
(SELECT DISTINCT(SDD_Date) AS DelDate
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date)e,
(SELECT *
FROM WAREHOUSE_METRICS
WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
AND WHSE_ID = 2
GROUP BY MTRC_DTE
, MTRC_ID
, WHSE_ID
, INVN_LVL_CS_CNT
, INVN_LVL_DLLR
, INVN_LVL_PLLT_CNT
, RCV_CS_CNT
, SHP_CS_CNT
, SRVC_LVL_PCT
, SCRTCH_CNT
, DROP_CNT
, RPLNSH_CNT
, DMG_CNT
, RET_CNT
, PICK_CYC_CNT
, PICK_ERR_CNT
, RSV_CHK_CNT
, ERR_CNT
, DLY_CS_VRNC
, DLY_DLLR_VRNC
, CMB_THRUPUT
, DELAY_MINS_CNT
, DELAY_PCT
, UNLOAD_AVG
, LABOR_AVG
, SELECT_HR_CNT
, CRT_USERID
, CRT_DTE_TME
, UPD_USERID
, UPD_DTE_TME) f
WHERE a.On_Time_Date = E.DelDate
AND B.Early_Date = E.DelDate
AND C.Late_Date = E.DelDate
AND D.Stay_Date = E.DelDate
AND F.MTRC_DTE = E.DelDate
GROUP BY E.DelDate
, A.On_Time
, B.Early
, C.Late
, AVG_Duration
, A.On_Time_Date
, B.Early_Date
, C.Late_Date
, D.Stay_Date
, F.WHSE_ID
, F.INVN_LVL_CS_CNT
, F.INVN_LVL_DLLR
, F.INVN_LVL_PLLT_CNT
, F.RCV_CS_CNT
, F.SHP_CS_CNT
, F.SRVC_LVL_PCT
, F.SCRTCH_CNT
, F.DROP_CNT
, F.RPLNSH_CNT
, F.DMG_CNT
, F.RET_CNT
, F.PICK_CYC_CNT
, F.PICK_ERR_CNT
, F.RSV_CHK_CNT
, F.ERR_CNT
, F.DLY_CS_VRNC
, F.DLY_DLLR_VRNC
, F.CMB_THRUPUT
, F.DELAY_MINS_CNT
, F.DELAY_PCT
, F.UNLOAD_AVG
, F.LABOR_AVG
, F.SELECT_HR_CNT
, F.CRT_USERID
, F.CRT_DTE_TME
, F.UPD_USERID
, F.UPD_DTE_TME
, F.MTRC_ID
, F.MTRC_DTE
Order By E.DelDate
Please excuse the length of the code but one of the tables has a lot of columns.
Can anyone tell me if it's possble to do a join on an inline view?
Any help would be greatly appreciated.
Thanks in advance,
John
Hello,
I am trying to understand the concept of left joins. I have the following query and am not sure about the left joins.
I am familiar with joins but the left join below is a little confusing.Below it seems like a third table is involved. Is this because there is no column to map to in the from table? Also, since tables sl and sc are mapped based on the SecurityID column and sl and ex do not have any common columns, table sc is mapped to ex using the left join? Which table's data will be returned based on the left join?
I checked the column type for the Exchange column(ex.LSECode) and it appears varchar(3).
SELECT SecurityID = sl.SecurityID
, Security = RTRIM(sec.Name) + ' - ' + RTRIM(sec.Description)
, Ticker = ISNULL(ids.RIC, ids.Ticker)
, Sedol = ids.Sedol
, ISIN = ids.ISIN
, Exchange = ex.LSECode
, Country = cty.iso_code
FROM #SecList sl
JOIN SecurityClassification sc
ON sl.SecurityID = sc.securityId
AND sc.source = 99
LEFT JOIN exchange_table ex
ON sc.ExchangeID = ex.exchange
Thanks in advance!!!
novicesql123
Hi,I'm curious about the computational complexity of a query I have. Thequery contains multiple nested self left joins, starting with a simpleselect, then doing a self left join with the results, then doing a selfleft join with those results, etc. What puzzles me is that the timerequired for the query seems to grow exponentially as I add additionalleft joins, which I didn't expect. I expected the inner select toreturn about 25 rows (it does), then I expected the self join to resultin about 25 rows (it does), etc. Each join just adds another column; itdoesn't add more rows. So the left part of the join is staying the samesize, and so is the right part of the join, since I'm always joiningwith the same table.So I would think the time for this query should be (time to join 25rows against the source table) * (num joins), but it seems to besomething like (num rows) ^ (num joins). Any ideas? I'm just trying tounderstand the system a little better. (But if you have any ideas aboutimproving the query, I'm always open to those, too.)The execution plan is what you'd expect: an index seek loop-joined withanother index seek, the results of which are merge-joined with anotherindex seek, the results of which are merge-joined with another indexseek, ad nauseum, until a final "compute scalar cost (39%)" and "select(0%)"For the brave and curious, I've pasted the query below.Thanksselect right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((selectdistinct cp_yyyymm from cp_deal_history where cp_deal_id in('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E','BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymmbetween 200304 and 200504) as x left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') astable0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') astable10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymm
View 4 Replies View RelatedHello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys. I bolded the three IDs that could be NULL or have a foreign key for a value. An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet. Right now I only get profiles that have foreign key values and it misses the rest in the search. So NULL MakeID or ModelID no result on that item my SQL statement below. Using SQL Server 2005. Pretty new this to SQL and databases but so far this has been the only trying part.
Thanks
String dbsql = "SELECT a.EquipmentID " +
" , a.SerialNo " +
" , b.Category " +
" , c.Subcategory " +
" , d.Make " +
" , e.Model " +
" , f.Status " +
" FROM tblEquipInfo a " +
" , tblEquipCat b " +
" , tblEquipSubcat c " +
" , tblEquipMake d " +
" , tblEquipModel e " +
" , tblStatus f " +
" WHERE b.Category = '" + val + "' " +
" AND a.CategoryID = b.CategoryID " +
" AND a.SubcategoryID = c.SubcategoryID " +
" AND a.MakeID = d.MakeID " +
" AND a.ModelID = e.ModelID " +
" AND a.StatusID = f.StatusID";
Until today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.
In other words:
LEFT JOIN LeftTable.ID = RightTable.ID
would pull all the records from LeftTable and those that matched from from RightTable and that:
RIGHT JOIN RightTable.ID = LeftTable.ID
would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?
Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?
Thanks in advance.
drop table percentMinorities;
create view percentMinorities as
select s1.schoolid,
round(count(s2.studentid)/count(s1.studentid),2) as percentWhite,
round(count(s3.studentid)/count(s1.studentid),2) as percentBlack,
round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic,
round(count(s5.studentid)/count(s1.studentid),2) as percentAsian
from
students as s1 left join
(students as s2 left join
(students as s3 left join
(students as s4 left join students as s5
on s4.studentid is not null and s5.ethnicity = 'A')
on s3.studentid is not null and s4.ethnicity = 'H')
on s2.studentid is not null and s3.ethnicity = 'N')
on s1.studentid is not null and s1.ethnicity = 'O'
group by s1.schoolid
I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.
SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id
Hi,
The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.
declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on
SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'
The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.
I have a situation where I'm trying to add a text field to allowlarge Notes to be linked to record in an existing table (rather thansimply adding the new field into the table since relatively fewrecords will have the Note)Tables are basically:T1IDInvNumFK----------------------40|142|243|244|1T2MatDetIdEqpNote-------------------------------------------------40|text44|additional NoteI'm trying to create a subform that will allow the user to view/updaterecords in T1 (there are other fields in T1 that aren't relevant tothe problem) and also add a record into the T2.EqpNote field or edit arecord that already exists.This query works in Access (allows me to type in text into the EqpNotefield and automatically create a new record in T2 with the T1.Id valuein the T2.MatDetId field) :SELECT T1.ID, T1.InvNumFK, T2.EqpNote, T2.MatDetIdFROM T1 LEFT JOIN T2 ON T1.ID = T2.MatDetIdWHERE (((T1.ID)=10230));but when I try to 'translate' it into a version that I can use for thesubform in the adp (it's an Access 2000 project with a SQL Server 2000backend) the 'child' record does not get added into T2.Here's the record source I'm using for the form in the adpSELECT T1.ID, T1. InvNumFK, T2.MatDetId, T2.EqpNote FROM T1 LEFTOUTER JOIN dbo.T2ON T1.ID =T2.MatDetIdWHERE T1.InvNumFk = XX--'master' tableCREATE TABLE [dbo].[T1] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[InvNumFK] [int] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblBSMaterialDet] WITH NOCHECK ADDCONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED([ID]) ON [PRIMARY]GO--Sub tableif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[T2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T2]GOCREATE TABLE [dbo].[T2] ([MatDetId] [int] NOT NULL ,[EqpNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[T2] WITH NOCHECK ADDCONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED([MatDetId]) ON [PRIMARY]
View 1 Replies View Related
Hi everybody,
In SQL Server is there a quick way to automatically insert empty value instead of NULL? I have a long Insert-SQL command with LEFT JOIN and don't want to use ISNULL for every field.
Researching on this in the meantime myself and I believe there is no way.
Thanks in advance.
I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).
I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.
Basically I have a query:
SELECT ....
FROM TABLEA
INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.
If I simply change the query to:
SELECT ....
FROM TABLEB
INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.
The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.
Any ideas how I can fix this?
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
I am having problems rewriting this proc as a TVF. I have been over the samples but I am struggling
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Management
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub pnetWMI(ByVal sComputer As String, ByVal sWQL As String, ByVal sCounter As String)
Try
Dim searcher As New ManagementObjectSearcher( _
"\" & sComputer & "
ootCIMV2", _
sWQL)
For Each queryObj As ManagementObject In searcher.Get()
Dim record As New SqlDataRecord( _
New SqlMetaData(sCounter, SqlDbType.VarChar, 100))
SqlContext.Pipe.SendResultsStart(record)
record.SetString(0, queryObj(sCounter))
SqlContext.Pipe.SendResultsRow(record)
Next
SqlContext.Pipe.SendResultsEnd()
Catch ex As Exception
Dim sp As SqlPipe = SqlContext.Pipe()
sp.Send(ex.Message)
End Try
End Sub
End Class
This what I have. It doesn't like my table definition or my use of the input parameters in the sub. Whats wrong?
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Management
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
_
Public Shared Function fnetWMI(ByVal sComputer As String, ByVal sWQL As String, ByVal sCounter As String) As IEnumerable
Return New SqlDataRecord(New SqlMetaData(sCounter, SqlDbType.VarChar, 100))
End Function
Public Shared Sub FillRow(ByRef PerfValue As SqlChars)
Try
Dim searcher As New ManagementObjectSearcher( _
"\" & sComputer & "
ootCIMV2", _
sWQL)
For Each queryObj As ManagementObject In searcher.Get()
Dim record As New SqlDataRecord( _
New SqlMetaData(sCounter, SqlDbType.VarChar, 100))
SqlContext.Pipe.SendResultsStart(record)
record.SetInt32(0, queryObj(sCounter))
SqlContext.Pipe.SendResultsRow(record)
Next
SqlContext.Pipe.SendResultsEnd()
Catch ex As Exception
Dim sp As SqlPipe = SqlContext.Pipe()
sp.Send(ex.Message)
End Try
End Sub
End Class
Hi everyone
I've built an HttpModule and hooked into the Application_AuthorizeRequest to do some URL Rewriting. Basically what I'm doing is grabbing the URL such as www.mysite.com/about/, then doing a database lookup on the path (/about/) to see if I have a corresponding record for that string, and then rewriting the URL if a result is found (to something like www.mysite.com/About.aspx?id=1).
My problem is that I am getting the following intermittent SQL error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Whenever it happens I can hit refresh straight away and it will work fine. It also happens each time I upload a new web.config to my server.
Any help would be appreciated!
Thank you
Hi Friends,
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)
Does anybody have any suggestions to rewrite the 2nd WHEN part of the query??? Thank you.
------------------------------------------------------------
update t_pgba_hdr
set HCFA_PLACE_TRMT_CD2 =
case when (select max(b.HCFA_PLACE_TRMT_CD)
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2) like '[A-Z]%'
then '99'
when (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2) in '[0-9]'
then '0' + (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2)
end
------------------------------------------------------------
-soumya
Is there a way to stop SSRS from rewriting my SQL? I can write an SQL statement that will execute within seconds within SQL Server Management Studio. But when I put that SQL into a dataset in SSRS, SSRS rewrites the SQL and makes the WHERE clause much more complex. Now the SQL takes minutes to complete.
Is there a way to stop this?
Rob
I have a table PLACE with a character column CODE
[Code] [nchar](4) NULL
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
SELECT RIGHT(RTRIM('0000'+ISNULL([Code],'')),4)
FROM [Place]
WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies View Related-- Why is the left table in a LEFT JOIN limited by the where clause on the right table?eg
DECLARE @LeftTable TABLE (LeftID INT NOT NULL IDENTITY(1, 1), LeftValue INT NULL)
INSERT @LeftTable (LeftValue)
VALUES (111)
INSERT @LeftTable (LeftValue)
VALUES (222)
[code]....