Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate.
I googled that, and I only found this useless tip:
Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.
am encountering the NO JOIN PREDICATE warning in a query plan for a particualr SQL statement. In poking around, I've come across psotings that mention the CU 4 for SQL Server 2005. The update applies to a KB article that addresses complex queries with an outer join.
But in my case, it's not an outer join causeing the problem - they are all left joins. Here is the piece of SQL that is running extremely slowly:
Code Snippet UPDATE @TESTTABLE SET [@TESTTABLE].SignInCancelledNoEMR = (SELECT q.cnt FROM (SELECT COUNT(*) AS cnt,cl.Clinic_id AS ClinicIDq,cl.CLINIC_REG_ID AS MarketIDq FROM dbo.tblactivity AS t LEFT JOIN [ERIIProduction].dbo.computer c on t.machineid = c.comp_windowsname LEFT JOIN [ERIIProduction].dbo.clinic cl on cl.clinic_id = c.comp_clinic_id LEFT JOIN [ERIIProduction].dbo.Region r ON cl.CLINIC_REG_ID = r.REG_ID WHERE t.status = '1' AND cast(checkindate as datetime) >= @startDate AND cast(checkindate as datetime) < DATEADD(d, 1, @endDate) AND cl.clinic_reg_id IN (SELECT Item FROM dbo.Split(@regionIDList,',')) AND NOT EXISTS (SELECT 1 FROM [ERIIProduction].dbo.VisitPatient vp WHERE (t.activityid = vp.VSPAT_GalvanonActivityID)) --AND (t.memberid = vp.VSPAT_GalvanonMemberID)) GROUP BY cl.Clinic_id,cl.CLINIC_REG_ID) AS q WHERE q.ClinicIDq = ClinicID AND q.MarketIDq = MarketID)
Before I apply the CU and enable the trace flags, I'd like to know if there is something else I can do within the query itself to make it run faster. I've tried the OPTION(FORCE ORDER ) hint, but that doesn't have any effect on the queery run time. And as a whole, the query cost relative to the entire batch jumped from 75% to 96%.
Granted the execution time of the entire batch dropped from 9 minutes to 1 minute, this query should not even take that long.
I have two tables. Table A has 2162 rows and table B has 101 rows. There isn't any join keys that I can join on these two tables. But I need everything from table B and only one column (Col1) from table A. It will result in a cross join and retruns 218362 (2162 * 101) rows, which is correct. But this takes about 30 seconds to complete. Any workaround to accomplish this? Optimizer shows no predicate joins on the Nested Loop and to me it is correct.
Here is what I have so far.
SELECT B.*, A.col1 FROM TableB B LEFT JOIN TABLE A A ON 1 = 1
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web. Version is SQL 2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?
Thanks!! bbRichbb
SELECT p.Product_Id, MIN(ae.Enum_Value) AS color, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id, COUNT(ps.SWATCH_STATUS) AS total_available_colors FROM Attribute_Enum_Value ae INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id AND ae.Attribute_Type_Id = pae.Attribute_Type_Id INNER JOIN Product p INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr AND p.Product_Id = ps.PRODUCT_ID WHERE (pae.Attribute_Type_Id = 500001) AND (p.Product_Class_Id = 2) AND (p.Status_Code = 'ACTV') AND (ps.SWATCH_STATUS = 'ACTV') GROUP BY p.Sequence_Number, p.Product_Id, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id HAVING (p.Product_Id = 1209645) ORDER BY p.Sequence_Number, p.Product_Id
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass
80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen
during very high traffic. I have isolated the query, included at the bottom of this post
(cleaned up). There is very little info on this event class out on the web. Version is SQL
2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall
direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get
the MJP. I would expect that a query without a JP is a query without a JP and it would be
all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can€™t make it happen in
testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed
light? Know of good links, etc?
Thanks!! bbRichbb
SELECT p.Product_Id, MIN(ae.Enum_Value) AS color, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id, COUNT(ps.SWATCH_STATUS) AS total_available_colors FROM Attribute_Enum_Value ae INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id AND ae.Attribute_Type_Id = pae.Attribute_Type_Id INNER JOIN Product p INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr AND p.Product_Id = ps.PRODUCT_ID WHERE (pae.Attribute_Type_Id = 500001) AND (p.Product_Class_Id = 2) AND (p.Status_Code = 'ACTV') AND (ps.SWATCH_STATUS = 'ACTV') GROUP BY p.Sequence_Number, p.Product_Id, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id HAVING (p.Product_Id = 1209645) ORDER BY p.Sequence_Number, p.Product_Id
hi , what is the definition and difference between predicate and residual predicate. give me some examples..Basically the columns used in where clause are called as predicates. Am i right.
How do I select * remaining Records of a table that don't fall true to the 2nd Select statement? Select * From zz_2007_Booth_Visitors
NOT IN
Select * From zz_2007_Booth_Visitors Where [Product Interest - Actuators] Is Null And [Product Interest - Other Actuator] Is Null And [Product Interest - Chillers] Is Null And [Product Interest - Other Chiller] Is Null And [Product Interest - Electronic Products] Is Null And [Product Interest - Other network interfaces] Is Null And [Product Interest - Fittings] Is Null And [Product Interest - High Vacuum] Is Null And [Product Interest - Other high vacuum actuators] Is Null And [Product Interest - Pick& Place and Transfer] Is Null And [Product Interest - Teflon Products] Is Null And [Product Interest - Training] Is Null And [Product Interest - Valves& Manifolds] Is Null
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
I have always heard that much of t-sql is based on "set theory". I had Set theory in high school and I remember it as being simple Unions, Intersections, Differences of Sets. By a Set I mean a collection such as {2,5,7,8,9, ...) That could well described a single row in a table. By unioning several of these rows we could end up with a table.
But how does that relate to t-sql such as
select * from <table name> Where <condition 1> ?
Is it simply that the result returned by the query is a Set? (if so, a Set is simply being used as a synonym for a Collection. No set theory involved.)
I have been tasked with creating a Data Warehouse.
Problem is that old storage vs reporting debate.
I have determined that the data that I will recieve and store will be like follows (simplified form) for expandability
KEY FldKEy FldData DateTime AuditTrail
Daily I will use this data based on use input process this data into the following format and say if fldkey/ flddata open a cycle. populate row with null close date if fldkey/ flddata closes cycle update row with date
If fldkey/ flddata changes a cutable value update row
if fldkey/ flddata changes a cutable value (type 2 table) insert a row into detail update value and obsolete previous row.
KEY DateStart DateEnd FLDDATA1 FLDDATE2 Op_Cl_IND HEADER Record
Problem: FLDKey is a finite count however the max is undefined.
IS there any way to solve the problem of not being able to nail down users to tell you what they want to cut by. What I have been instructed by mgr (old IDMS) is that they wish to see all on the FldData and have the ability to cut by all of it. However the Flddata could be anything (cannot be indexed).
400,000,000 rows at least.
Do I need to nail the users down or am I am missing something.
I understand that there is much involved in figuring up I/Othroughput, but I'm hoping the answer to the following question willbe a simple A, B OR C.Given the configuration below, what hardware component wouldbottleneck first.A. Drive Spindle ThroughputB. SCSI ControllerC. Other Component(if so what component?)Configuration:Controller: Ultra320RAID Config: RAID 5Drives on RAID: 14 - 73gb/15k - SCSI DrivesType of Writes: RandomQuestion Background:I currently have the configuration above on a local box. Our companyhas ordered a SAN and and has space set aside. What I'm trying toacertain is whether or not I'd be better off staying local or ifputting my DB on a SAN would be more beneficial.Other Notes:I've determined using a formula that determines the max theoreticalI/O operations/sec for the # of drives (spindles) and RAID Config...but I've not been able to establish a relationship betweenOperation/sec -> MB/Sec. I'm sure this is for obvious reasons...If anyone has had a related scenario, I'd be interested in hearingyour thoughts.Thanks,Ornac
My co worker designed a database where retail items can be placed in multiple catagories. This seems odd to me..... In general, Isnt it more normal than not to be only one catarory for each item? For example, lets say I was selling a bowling ball with a picture of Mickey Mouse on it. I can then find this item in the "Mickey Mouse" catagory or in the "bowling ball" catagory but in the database the bowling ball has only one catagoryID. When I worked for a multi-million dollar corporate retail store , an item was listed once in only one catagroy. But i am sure items can be viewed
I know there isnt a single rule, I am just looking for a solution. How should the database sturucture be built with this in mind starting out with what is listed below???? Mabey an attributes table?
I have a general theory question for best practices about upsizing an msaccess 2003 split database design to use SQL server instead of the .mdb for data storage.
My data has grown close to msaccess limit, and ive started experiencing lost connections and corruptions frequently. So the next step is to upsize to SQL.
So far DTS seems to do a better job with bringing in the tables and data (then the upsizewizard).
Does any one have a suggestion on how to deal with the front end connecting the sql backend ?
i understand an ADP project file uses OLE connection, is that a better solution then linked tables directly into SQL threw ODBC ?
Reason i ask is the linked tables seem to not break as many things inside the code.
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table CaseID Patient (Sample data: 101,SallyDoe 102,JohnDoe)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases) CaseID CrnaID TimeIn TimeOut (Sample data: 102,Jack,0800,1030 102,Bart,1030,1130 102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
I run statement down,result is "go". why goes,gone,going is not in the result? SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, ' FORMSOF (INFLECTIONAL, go) ') information table [contain-1] ID firstname 1 go 2 goes 3 gone 4 going
I run query down.result is null. Why is result null? SELECT ID, firstnameFROM [contain-1]WHERE CONTAINS(firstname, 'yaser NEAR pooya') table information: ID firstname 1 ali 2 peter 3 yaser 4 yaserapooya 5 mehdi
Hi,i want to search record in a table where a varchar column is equal toa word or this word with the letter 's' or this word with the letter'e'.Today, i've this query :select * From Table Where Column = 'Word' or Column like 'Word[es]'I want to remove the 'or' of this query to have only one condition.Does someone know a solution to my problem.Excuse my poor english language.I hope you understand my problem and thks for solution.
Hi everybody, I have a problem using the CONTAINS predicate. I use the contains predicate in a web page that students utilize to look for dissertations stored in a database (MS 2000) of the library. The search is one big textbox where students can perform any type of reaserch. For instance, they can insert the author, the title or few keywords of a dissertation. After they have clicked on the button Submit, the words that they have inserted are filtered and combined (with AND) in one string: Example: miami, education --> 'Miami AND Education'
An example of the query that I need to perfrom is the Query Number 1:
select docnumber,Title
FROM [Log].[dbo].[DocTable1]
where contains(SearchColumn,'miami AND education') order by docnumber
SearchColumn is a field (varchar) that is the concatenation of various fields such as Location, Title, Author, etc... that are contained in the same table (See below for the description of the Table DocTable1).
I'm currently testing the search and I have figured out that the query described above does not work. For example, the query should produce the same resultsof the following one, but it does not:
select docnumber FROM [Log].[dbo].[DocTable1] where contains(Location,'miami') AND contains(Keywords,'education') order by docnumber
I need to implement the first query, but i can not understand why it does not produce the same results of the second one. Any Ideas?
I am having problems with the following SQL statement. It works how I want it to work if I use = @SearchQuery but when I try to switch to LIKE I run into problems. I tried it the way it is shown below and I also tried LIKE '%'@SearchQuery'%' with no luck. Does this have something to do with the pattern to match being a parameter? How would I go about doing this correctly. "SELECT server_info.*, network_adapter.* FROM server_info LEFT JOIN network_adapter " + "ON server_info.server_id = network_adapter.server_id WHERE " + DropDownList1.SelectedValue.ToString() + " LIKE '%@SearchQuery%'" Thanks
Anybody know how to do this? "Select something from some table where id in (@IDlist)" where @IDlist is a comma-separated list of values (or something top that effect)
HiI am having a debate with one of the Postgres developers (Tom Lane)according to him ms-sql does not implement predicatelocking(SERIALIZABLE).Where predicate locking is defined as (from the postgres help):"12.2.2.1. Serializable Isolation versus True Serializability"It states: "To guarantee true mathematical serializability, it isnecessary for a database system to enforce predicate locking, whichmeans that a transaction cannot insert or modify a row that would havematched the WHERE condition of a query in another concurrenttransaction"Now I put it to him that that is exactly what SERIALIZABLE does isMS-SQL.But his response was "Only for WHERE conditions that can be expressedas a simple range constraint."Now is he correct i.e SERIALIZABLE works "Only for WHERE conditionsthat can be expressed as a simple range constraint." ?Regards,Daniel RothMCSD.NET
Hi guys, I need to change some characters when reading the values of a certain columns, I am trying to use something like the code below, but the following error: "Cannot use a CONTAINS or FREETEXT predicate on table 'JURNALTRANS' because it is not full-text indexed."
Code Snippet
CASE WHEN Contains (JurnalTrans.DESCRIPTION, 'A') THEN Replace(JurnalTrans.DESCRIPTION,'A','A1') WHEN Contains (JurnalTrans.DESCRIPTION, 'B') THEN Replace(JurnalTrans.DESCRIPTION,'B','B2') ELSE JurnalTrans.DESCRIPTION END AS 'Cost Description',
I am also trying something like this
Code SnippetSELECT DESCRIPTION AS 'X' FROM JURNALTRANS WHERE CONTAINS(DESCRIPTION, 'Y')
but getting the same error "Cannot use a CONTAINS or FREETEXT predicate on table 'JURNALTRANS' because it is not full-text indexed."
Any help will be appreciated, Thanks in advance, Aldo.
I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.
I tried a standard BETWEEN predicate in my WHERE clause like: IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0
But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then.....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.
I have been searching for an escape character or a way of escapingdouble quotes that are actually in a string that I am using in thecontains predicate.Here is an exampleselect *from tablewhere contains(field, '"he said "what is wrong", that is what hesaid"')I need the double quotes in the string because they are part of thetext. Of course, Fulltext search raises the errorServer: Msg 7631, Level 15, State 1, Line 1Syntax error occurred near 'what is wrong", that is what he said'.Expected ''''' in search condition '"he said "what is wrong", that iswhat he said"'.If I remove the double quotes, the search does not return the properresults.Thanks in advance for the helpBill
What is the dowside of not using all uppercase for predicates and key words?
I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?
Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;
Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;
