Index / Join / Where Clause Very Slow
Jul 15, 2007
Hello,
first of all, some facts of the case:
Table Master Table Dimension
ID Code Price ID Name
1 A44333 5000 1 "Scanner"
2 D442 3000 2 "Notebook"
3 D6644 4000 3 "Banana"
I join both tables on ID and search one time for ID and another time for Name. Looks like
(a)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE master.id=1
AND Code like 'A44'
(b)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE Name = 'Scanner'
AND Code like 'A44'
Why does query (b) take longer than query (a)? Dimension has 12 Rows and
Master has about 24M Rows.
For index I did
Create Index IX_Master_ID on Master(ID)
Create Index IX_Master_Code on Master(Code)
Create Index IX_Dimension_ID on Dimension(ID)
Create Index IX_Dimension_Name on Dimension(Name)
I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out?
Thank you
Silas
View 9 Replies
ADVERTISEMENT
Jun 2, 2014
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies
View Related
Jun 20, 2008
hello friends
i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :)
i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :)
cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1")
End If
If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2")
End If
If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3")
End If
End If
Dim searchword As String
If Request("TextBox3") = "" And Request("TextBox2") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'"
End If
If Request("TextBox3") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'"
End If
If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'"
End If
SqlDataSource1.SelectCommand = searchword
End Sub
View 11 Replies
View Related
Apr 15, 2008
Hi,
hoping some one can explain the following
I have a large data set with a few million records in it,
in query analyser if i run the following query it will return 260 results in 19 seconds
the Tel_ID is an int the InvoiceNo is an nvarchar
SELECT * FROM Call_Data WHERE Tel_ID=18668 AND InvoiceNo='519657'
if i run this query then i get the same result but in less than a second
SELECT * FROM Call_Data WHERE Tel_ID=18668 AND InvoiceNo=519657
???
View 9 Replies
View Related
Jan 12, 2007
Dear Experts,I have a fairly simple query in which adding a where clause slowsthings down by at least a factor of 100. The following is the slowversion of the query-------------------------SELECT * FROM( Select x.event_date From x FULL OUTER JOIN yON x.event_date = y.event_date) innerQWHERE ( innerQ.event_date >= {ts '1980-01-01 00:00:00'} )------------------------Removing the where clause makes the query run quickly. This seemsextremely strange because it seems like SQL Server should simply beable to take the results of innerQ and discard anything with a datethat doesn't match. If I instead split the query into two pieces whereI create a temp table and put innerQ into that and then do the select *WHERE (...) from the temp table things work fine.Any thoughts on what SQL Server might be doing to make things slow andhow I can fix it?Thanks,-Emin
View 9 Replies
View Related
Sep 29, 2015
I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
[code]....
View 2 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
Mar 6, 2002
This queries performance is acceptable (about 1 second) when run like this:
SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id AND b.ff3 = 'T'
WHERE a.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue'
It is terrible (60 seconds) when run like this:
SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id AND b.ff3 = 'T'
WHERE b.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue'
I need the date range to come from the b.maydate. The field is indexed. If I run another query directy on table2 b without a JOIN and using b.mydate for some daterange it is quite fast. Any idea how to speed this up?
Thanks,Adrian
View 1 Replies
View Related
Jan 21, 2008
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Thanks
View 6 Replies
View Related
May 11, 2007
We are running MS RS and SQL Server 2000 SP3.
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
Thanks,
View 3 Replies
View Related
Apr 15, 2015
I have an index on a date field but, the datatype is nvarchar. If I convert to datetime in my where clause, will the index still be used? For example
select MRN, Name, AppointmentDate
from DATA
where convert(datetime, AppointmentDate, 121) between '2015-04-01' and '2015-04-30'
View 7 Replies
View Related
Sep 21, 2007
I think I am getting 0 records returned... because....
I am joining the third value based on a zip code. The two tables if directly compared to each other would never have an = match.
SELECT t2.company_name, t2.firstname, t2.lastname, modelname, configname, format, version, username, t2.zip,
t2.country
FROM EtechModelRequests JOIN
CC_Host.dbo.USR_SC as t2 ON
Cast(t2.user_id As char) = username
--JOIN
--Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON PostalCode = zip
WHERE RequestDateTime > CONVERT(DATETIME, '2007-09-1 00:00:00', 102) AND interfacename LIKE '%download%' AND
result=0 AND country='CA'
--AND t3.PostalCode Like 'z1x%'
ORDER BY company_name
I was trying to do it by using a Where clause AND t3.PostalCode Like 'z1x%' that I will later turn into an Input Parameter after I get it working.
Is there anyway to trim the PostalCode to the first three characters during the join process?
Something like Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON LEFT(PostalCode, 3) = zip
Not sure I got the LEFT function syntax correct even. Help appreciated.
View 1 Replies
View Related
Apr 22, 2008
I'm joining one table on to another table using one of 2 possibile fields (so table 1 key one can either match table 2 key 1 or key 2)... When the first key is null for a record, the script is to attempt to join using the second key instead. It is possible to have both values present, in which case the first one should be used.
I've taken a few runs at this so far:
...
from table1 t1
left join table2 t2
on
(t1.key1 = t2.key1
or
t1.key1 = t2.key2)
If either t2.key1 or t2.key2 are populated, this works. Unfortunately, it's bringing back multiple records if both key1 and key2 are populated. Question # 1... Is there a different relational operator I can be using instead of OR that would logically look like 'if thie first key didn't find anything try the second instead'?
As an alternative, I've put the NVL to use...
NVL(t2.key1, t2.key2) = t1.key1
That seems to work, but it's pretty heavy on the server. Any suggestions on how else to handle this scenario would be greatly appreciated
(and ya, I didn't design this datamodel).
View 6 Replies
View Related
Aug 15, 2007
view 1
I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.
view 2
notes field
I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.
I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)
eg.
VIEW 1
row1 row2 row3 note_row (from view 2)
fsdfs sdfsdf sdfsdf <no note>
sdfs sdfsd sdfsd "note"
sdfsdf sdfsdf ssdfsd <no note>
so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?
THANKS!
View 4 Replies
View Related
Apr 21, 2006
Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:
Descriptions table:
ID | Title
Doc1 | Document 1
Doc2 | Document 2
Documents table:
ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189
View:
ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)
So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.
In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.
It's running on SQL Server 2005 with 2GB of RAM.
Any suggestions on tracking down the reason for the slowness would be great.
Thanks in advance!!!
-Dan
View 8 Replies
View Related
Oct 31, 2005
i have a MSSQL 2000 database with about 30 tables in it. On one of those tables i've defined an full text index on an image field. In this table are around 500 records with binary files.
it functioned well for a time but now when i try to upload a file into the table this goes extremely slow (300 KB takes over 3 minutes).
i tried disabling "change tracking" but this didn't help a thing
adding blobs to other tables (without fulltext index on it) still goes fast.
what could be a reason that the uploading goes so slow??
View 3 Replies
View Related
Mar 26, 2008
What is the equivalent for INCLUDE clause (in Create index syntax) in SQL Server 2000.
SQL Server 2005 will support Include clause in Create index syntax . How to attain it in SQL Server 2000
Example : I have below query executable in SQL Server 2005
CREATE INDEX Index_Name ON mytable(col1 ASC) INCLUDE (name,id);
What is the equivalent for the above query in SQL server 2000
Thanks ,
Sushma
View 1 Replies
View Related
Nov 16, 2007
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
View 5 Replies
View Related
Oct 28, 2007
Hi,
I'm executing a nested queries consisting of LEFT OUTER JOIN for:
Duration = 27 sec;
Reads = 1690;
Number of users = 1.
This is extremely slow for a small set of data. I'm afraid that when we have a larger dataset and more concurrent users that query will take forward.
What am I doing wrong for my query to be taking too long?
Here's the query:
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where B.Business_Id IN (
Select B.Business_Id from Busines as B
JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id
JOIN Address as A ON A.Address_Id = BA.Address_Id
WHERE A.City_Name like '%New York%'
and A.state = 'NY') AND ( FREETEXT(B.Business_Name, 'Designer')
or BSC.Service_Category_Id IN
( Select SC.Service_Category_Id from Service_Category as SC
where FREETEXT(SC.Service_Category_Name, 'Designer') )
or BSSC.Sub_Category_Id IN (
Select SSC.Service_Sub_Category_Id from Service_Sub_Category as SSC
where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer') )
or BS.Service_Id IN (
Select S.Service_Id from Service as S
where FREETEXT(S.Service_Name, 'Designer') ) )
----------------------------------------------------------------------------------------------------------------------------------------
Here's the SQL Query Plan:
|--Nested Loops(Left Semi Join, OUTER REFERENCES.[Business_Id], [BS].[Service_Id], [BSC].[Service_Category_Id], [BSSC].[Sub_Category_Id]))
|--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Sub_Category].[Business_Id] as [BSSC].[Business_Id]))
| |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service_Category].[Business_Id] as [BSC].[Business_Id]))
| | |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service].[Business_Id] as [BS].[Business_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES.[Business_Id]))
| | | | |--Sort(DISTINCT ORDER BY.[Business_Id] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[BA].[Business_Id]))
| | | | | |--Hash Match(Inner Join, HASH.[Address_Id])=([BA].[Address_Id]), RESIDUAL[PB].[dbo].[Address].[Address_Id] as .[Address_Id]=[PB].[dbo].[Business_Address].[Address_Id] as [BA].[Address_Id]))
| | | | | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Address].[PK__Address__0519C6AF] AS ), WHERE[PB].[dbo].[Address].[State] as .[State]='NY' AND [PB].[dbo].[Address].[City_Name] as .[City_Name] like '%JayVille%'))
| | | | | | |--Index Scan(OBJECT[PB].[dbo].[Business_Address].[ClusteredIDX_Business_Address] AS [BA]))
| | | | | |--Index Seek(OBJECT[PB].[dbo].[Busines].[UI_ukBusiness] AS ), SEEK.[Business_Id]=[PB].[dbo].[Business_Address].[Business_Id] as [BA].[Business_Id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT[PB].[dbo].[Busines].[PK_Busines] AS ), SEEK.[Business_Id]=[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service].[ClusterIDX_Business_Service] AS [BS]))
| | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service_Category].[ClusterIDX_Business_Service_Category] AS [BSC]))
| |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Sub_Category].[ClusterIDX_Business_Sub_Category] AS [BSSC]))
|--Concatenation
|--Filter(WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id] = [Full-text Search Engine].[KEY]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Category].[PK__Service_Category__15502E78] AS [SC]), SEEK[SC].[Service_Category_Id]=[PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Sub_Category].[PK_Service_Sub_Category] AS [SSC]), SEEK[SSC].[Service_Sub_Category_Id]=[PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
|--Clustered Index Seek(OBJECT[PB].[dbo].[Service].[PK__Service__117F9D94] AS ), SEEK.[Service_Id]=[PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]) ORDERED FORWARD)
|--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]))
|--Remote Scan(OBJECTFREETEXT))
Please Help....
View 2 Replies
View Related
Feb 12, 2004
I have a table "Users" like this:
GroupId
CompanyId
UserId
I need to query the users getting the company's and group's names, but I only know how to join one table. Example:
Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id
Hon can I add the companies table in the Join ?
Thanks,
Moshe
View 2 Replies
View Related
Dec 1, 2005
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
View 2 Replies
View Related
Feb 26, 2004
In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.
In layman's terms, what is the difference? Any examples? Thanks in advance.
ddave
View 6 Replies
View Related
Mar 7, 2008
I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes
SELECT
SEP_ACCOUNT.sep_acct_cde as Account,
FUND.fund_cde as FUND,
BRKG_FACT.accum_unit_cnt as Units_Purchased,
BRKG_FACT.transaction_amt as Amount_Purchased
FROM
BRKG_FACT
SEP_ACCOUNT
FUND
where
BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num
brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM
brkg_fact.SEP_ACCT_ID_NUM = 5 and
brkg_fact.product_cde <> 'MM' and
brkg_fact.transaction_amt <= 0 and
brkg_fact.source_sys_id_num <> 3 and
brkg_fact.source_sys_id_num <> 5 and
BRKG_FACT.trans_process_dte >= '1/1/2008' and
BRKG_FACT.trans_process_dte <= '1/2/2008'
order by fund_cde
current output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED
U BLCD -0.01137 -1.48000
U BOND -0.01283 -1.67000
U CGDE -0.06743 -0.95000
U EQIN -0.13277 -2.39000
U GRST -0.11799 -4.07000
U IX4S -0.53996 -12.55000
U LCCS -0.18216 -5.31000
wanted output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED
U BLCD -0.01137 -1.48000
U BOND -0.01283 -1.67000
U BWDS NULL NULL
U CGDE -0.06743 -0.95000
U EQIN -0.13277 -2.39000
U GAFR NULL NULL
U GRST -0.11799 -4.07000
U IX4S -0.53996 -12.55000
U LIGE NULL NULL
U LCCS -0.18216 -5.31000
View 2 Replies
View Related
Aug 23, 2007
Using SQL Server 2005
I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped.
I need a to show the count of each events that have happened to it within a certain time period.
This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table.
What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count)
Any thoughts? I'm a complete newbie at this by the way.
Thanks
Code Snippet
SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit
FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate)
GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit
ORDER BY DeviceStatusWords.DeviceName
View 5 Replies
View Related
Aug 14, 2001
Hi,
I'm trying to inner join an update statement.
Something like this:
update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point
where #point_connection_temp.next_point is null
order by a.key_fld
I'm getting an error message:Incorrect syntax error near AS
Any help will be greatly appreciated.Thank you!!!!!!!!!1
View 1 Replies
View Related
Jul 29, 2005
Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause? My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined.
Filtering in where clause....
Code:
select...
FromtProject p with (noLock)
jointProjectCall pc with (noLock) on P.ID = pc.project_id
jointStore S with (noLock) on pc.store_id = s.id
jointZip Z with (noLock) on Z.zip5 = s.zip5
jointManager M on M.ID = case ... end
leftjoin
(
selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed
fromtCall C
whereAnswer > 0 and question_id in (1, 2)
group by projectCall_Recnum
) as C on pc.recnum = c.recnum
wherepc.removed = 0
andp.cancelled = 0
andp.deleted = 0
ands.closed = 0
ands.deleted = 0
andyear(getDate()) between year(P.startDate) and year(P.expDate)
Filtering in joins...
Code:
select...
FromtProject p with (noLock)
jointProjectCall pc with (noLock) on P.ID = pc.project_id
and pc.removed = 0
and p.cancelled = 0
and p.deleted = 0
and year(getDate()) between year(P.startDate) and year(P.expDate)
jointStore S with (noLock) on pc.store_id = s.id
jointZip Z with (noLock) on Z.zip5 = s.zip5
and s.closed = 0
and s.deleted = 0
jointManager M on M.ID = case ... end
leftjoin
(
selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed
fromtCall C
whereAnswer > 0 and question_id in (1, 2)
group by projectCall_Recnum
) as C on pc.recnum = c.recnum
View 1 Replies
View Related
Jan 11, 2005
Having problems rewriting my join condition using the "inner join" syntax.
My query, working with an intersection table:
SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur
WHERE
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID
This works fine, but i want to write it using 'inner join' style, so I tried:
SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u
INNER JOIN Accounts_UserRoles ur
ON
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID
which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.)
Any ideas as to how I'm screwing this up would be appreciated.
Thanks,
Gordon Z
View 3 Replies
View Related
May 25, 2012
I have tables A and B.
Table A has data, but B may have or not.
I have to get -- at least! -- the A table data.
It works when I dont have the where clause:
select * from A left outer join B on A.IDUser = B.IDUser
But it doesn't when I try to use the WHERE clause:
select * from A left outer join B on A.IDUser = B.IDUser
where A.IDUser = 2
View 3 Replies
View Related
Apr 25, 2014
I've come across a piece of code which i have never seen before.
ON T.CT_YEAR in
(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)
[Code] ....
The CT_Year column is simply C for current year L for last year, O for Other, N for Next.
The Function simply returns the year value.
View 4 Replies
View Related
Aug 12, 2014
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
[Code] .....
View 2 Replies
View Related
May 28, 2008
I have a SQL query where am using WHERE clause as a result of which the NULL values are getting filtered...can u please help me to tranform this query into LEFT OUTER JOINS so as to avoid this filtration....my query is
SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE FROM EALPS_DRWREVSPLJTS
A,EALPS_DRW_REV_SPL B,EALPS_DRW_REV_LINE C,EALPS_WELDTESTCRIT D,EALPS_ACTV_SEQ E WHERE B.SPOOL_ID=A.SPOOL_ID AND
B.LINE_ID=C.LINE_ID AND D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID AND E.ACTIVITY_CODE='VT' AND E.JOINT_ID=A.JOINT_ID
Thanks and Regards,
parama.
parama laha
View 6 Replies
View Related
Feb 12, 2008
Hi Faculties,I have two queries which give me the same output.-- Query 1SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.idWHERE cat.id = 1;-- Query 2SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.id AND cat.id = 1;The first query uses the WHERE clause and the second one has all theconditions in the ON clause. Is there anthing wrong with the secondapproach in terms of performance? Please suggest.Thanks in advanceJackal
View 6 Replies
View Related
