Performance Of Joining Two Table In Diffrent Databse
Nov 19, 2007
I have two databses SIS and SIS_Pro. Users tables should be used in both of them because I have some relations between this table with other table in SIS and SIS_Pro. Users in SIS only have one column and it is the UserId which is the primary Key in both of them, but in SIS_Pro Users table have Firstname Lastname and... now. In my program I need some informatin from SIS and some from SIS_Pro so I create a view which is joining of forexample exam in SIS and Users in SIS_Parnian, becuase I don't have the firstname and lastname in a Users table which is in SIS_Pro databse.Does it reduce the performance?is it better to copy datas which are in Users in SIS to Users in SIS_Pro( I mean all columns firstname, lastname ,,.....)
Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.
A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.
Hi I need to know if it is possible to write on a table from another database and this using a stored procedure. If yes what is the correct syntax to connect to the other database.
Hai Everbody, for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance. Thanks and regards Biju.S.G
I have to export the table data from my databse into text files as I nedd to put it in Informix database using a sheel script. Is there a way by which I can do this.
Is there any other way by which I can put the data from SQL Server to Informix.
I run into a problem when asking to show a query of employee vacation days.
table 1: column1 Â is dates e.g. 2015-01-01 2015-01-02 2015-01-03Â . . . 2015-12-31
table2: employeeID vacation_date Tom 2015-01-03 Tom 2015-01-04 David 2015-01-04 John 2015-01-08 Mary 2015-01-012
My query output need to be:
2015-01-01 2015-01-02 2015-1-03 Tom 2015-01-04 Tom 2015-01-04 David 2015-01-05 2015-01-06 2015-01-07 2015-01-08 John 2015-01-09 2015-01-10 2015-01-11 2015-11-12 Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
I am looking for an alternate logic for below-mentioned code where I am inserting into a table and having left join with the same table
insert TABLE1([ID],[Key],[Return]) select distinct a.[ID],cat1,cat2Â from (select ID,[Key] Cat1 ,[Return] cat2 from @temp as temp) a left join TABLE1 oon a.ID= o.ID and a.Cat1 = o.[Key] and a.cat2 = o.[return] where [key] is null order by ID
i made a backup devices for the app db and for every one of the system database. and with those devices backup and restore.
can i manually configure the system db or i must restore them? i have an issue where i downloaded and installed v.9.00.2047 while my backups where on v.9.00.1399
I have a table as below: user_id app_id form_id 1 a 1245 1 a 1345 1 b 1548 2 a 1245 2 a 1345 2 a 1411 2 b 1678 2 c 1245 3 b 1678 Now I want compare user 1 with user 2, and find out the records for user 2 where there are no app_id and from_id the same as user 1, so the output should be" 2 a 1411 2 b 1678 2 c 1245 Please advise how I can do this. Many thanks.
I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.
The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,i.e., they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.
Companies_Addresses id (PK) companyID (FK) companyDetailID (FK) addressID (FK)
I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement.
Code: SELECT DISTINCTAddresses.city as branchCity, Addresses.street as branchStreet FROMCompanies LEFT JOINCompaniesDetails AS cd ON companies.companyID = cd.companyID
LEFT JOINCompanies_Addresses AS c ON c.companyDetailID = cd.companyDetailID LEFT JOINAddresses ON c.addressID = Addresses.addressID
WHERE Companies.name LIKE 'abc' ANDCompanies.status_indicator like 'Current'
If a table gets joind with itself and then joined again with itself, is it possible to perform one kind of outer join from the third table to the second tabe and having the range of records in the second table limited to the joins between the first and the second table?
Join on RegionID Join on RegionID and City Show records not qualifying for the joint but limited to the scope of of records established by the first joint _______________ ________________ _______________ | | | | | | | RegionID One | | RegionID One | | RegionID One | |_______________| | City New York | | City New York| |________________| |_______________| _______________ ________________ _______________ | | | | | | | RegionID One | | RegionID One | | RegionID One | |_______________| | City New York | | City New York | |________________| |_______________| _______________ ________________ _______________ | | | | | | | RegionID One | | RegionID One | | RegionID One | |_______________| | City Buffalo | | City New York | |________________| |_______________|
This is regarding the joins...for instance I have three Tables A, B, C. And they are related to each other. If I am joining A->B,B->C then is it necessary to join A->C too? what happens if I omit join A->C? and what happens if I include join A->C too?
Hello allLet's say I have 1 table "contract" containing the following data:id year sales45 2005 10045 2004 9589 2005 25089 2004 27512 2005 42I want to make a table with one unique row for each id and then a column for2004 sales and 2005 sales, like this:select a.id, a.sales, b.salesfrom contract a, contract bwhere a.contract=b.contract(+)and a.year=2005and b.year=2004The rows for id 45 and 89 are shown perfectly. But id 12 is not shown at allbecause it doesn't have a record for 2004!! I don't know why 'cause Iouterjoined the tables.It works perfectly when I have two distinct tables for each year (forinstance contract_2005 and contract_2004). So the problem seems to be in thefact I like to join one table with itself.Someone has a solution for this?thanks!Maarten
I'm having a problem where I get diffrent results between SQL2000 and 2005.
I have have the following view:
Code Block
SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
ALTER VIEW [dbo].[vUsrMsgInfo] AS SELECT dbo.vMsgInfo.Body, dbo.vMsgInfo.FinalBody, dbo.vMsgInfo.BodyType, dbo.vMsgInfo.MsgID, dbo.vMsgInfo.Status, dbo.vMsgInfo.Creator, dbo.vMsgInfo.Created, dbo.vMsgInfo.StatusTime, dbo.vMsgInfo.StatusReason, dbo.vMsgInfo.Via, dbo.vMsgInfo.RouteAt, dbo.vMsgInfo.Source, dbo.vMsgInfo.Priority, dbo.vMsgInfo.Subject, dbo.vMsgInfo.RouteRuleName, dbo.UsrMsgInfo.Usr, dbo.vMsgInfo.SequenceNo, dbo.vMsgInfo.SendEnv, dbo.vMsgInfo.EnvCount, dbo.vMsgInfo.OwnerOrg, dbo.vMsgInfo.OwnerPerson, dbo.vMsgInfo.ToWildAddr, dbo.vMsgInfo.BodySize, dbo.vMsgInfo.RoutedRoute, dbo.vMsgInfo.NoteBody, dbo.vMsgInfo.ToOrg, dbo.vMsgInfo.CreatorOrg, dbo.vMsgInfo.FaxMode FROM dbo.UsrMsgInfo RIGHT OUTER JOIN dbo.vMsgInfo ON dbo.UsrMsgInfo.Message = dbo.vMsgInfo.MsgID GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
I run the following query:
Code Block Select * from vUsrMsgInfo where usr='cdk'
In SQL2000 I get a resultset that includes nulls and cdk in the usr column.
In SQL2005 however I only get results that has cdk in the usr column.
Is there a way to replicate the 2000 results in 2005??
I have a problem with the dates and "datetime" picker in my report (sqlserver 2005 sp2).
My reginal settings on the server are: "hebrew" - "Isreal".
I have a report with a datetime picker.
If I'm opening the report from: http://myserver/REPORTSERVER and I'm writting in the datepicker textbox the value "01/04/2008", and then click on the calander icon it opens on the on the 4th of january 2008.
If I'm opening the report from: http://myserver/REPORTS and I'm writting in the datepicker textbox the value "01/04/2008", and then click on the calander icon it opens on the on the 1th of april 2008.
Does anybody know this bug and how to wotk with it?
What sort of script would convert a pre-existing table into the second below?
I only want to merge the columns with Primary_IDs 1111 & 3333 to have the same Secondary_ID values, without duplicating any similar Secondary_ID values between the 2 which I've marked in red below.
Select: C1.firstname, C1.lastname, SP.lastyearsales, A.city From: SalesPerson as SP join Employee as E1 on Sp.salespersonID = E1.EmployeeID Right Join Employee as E2 on E1.EmployeeID=E2.EmployeeID join Address as A on E2.AddressID = A.AddressID Right Join Employee Contact as C1 on E1.ContactID = C1.ContactID
1. [Relshp].[PersonId] to primary key [Person].[PersonId], and 2. [Relshp].[RelatedToPersonId] to primary key [Person].[PersonId].
What kind of index structure would best support those FK constraints?
Would it be:
a) One combined index: CREATE INDEX IX_Relshp ON Relshp (PersonId, RelatedToPersonId) or b) Two indexes: CREATE INDEX IX_RelshpP ON Relshp (PersonId) CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId) or c) Two "mirrored" combined indexes: CREATE INDEX IX_RelshpP ON Relshp (PersonId, RelatedToPersonId) CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId, PersonId)
Sorry if this has been asked before, I looked through the FAQ but could not find an answer to the following.
I have a Project table which contains amongst other fields, a CreatedByID field and a LastModifiedByID field. Both these fields point to a User table. What I would like to do is get the two usernames from a query on the project table.
I know how to get one usename using the following Sql command but how do I get access to the second username ?
SELECT Project.Name,User.Username FROM Project,User WHERE Project.ID=@id AND User.ID = Project.CreatedByID;
use default pubs database in sqlserver2000. use authors table and publishers table.
Write a query to list first name, last of all authors and name of the publisher (if any) present in the same city as the author. If no publisher is present in the city where the author is located then the column should contain a NULL value. If there is more than one publisher in the city where the author is located, then the details of the author are to be repeated for each publisher.
but there is no field match between authors table and publishers table.
My main table has the following structure:t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]I want to join this table with the following second table:t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),(NULL,1,"value2")]The join should first try to find a match on id_primary and only if thatfails it should find a match on id_secundary. Every row in t1 is matchedagainst a single row in t2.The following query works:selecta.name, isnull(b.value, c.value)fromt1 a left outer join t2 b on a.id_primary = b.id_primaryleft outer join t2 c on a.id_secundary = c.id_secundaryI'm wondering though if it would be possible to write a query that only usest2 once, since it actualy is quite a complex query that is calculated twicenow. Any ideas (besides using a temp table)?
I have query that takes 12 minutes to execute. The query uses around 9 tables but I have narrowed down the problem to one table that has over 65 million rows. The problem table has only 3 fields
The query uses the primary key of this table to perform the join. FieldTwo and FieldThree are only used as output parameters.
I noticed if I remove FieldTwo and FieldThree from the output (but still leave the table in the query), the query executes in 1 second. However if I include FieldTwo and FieldThree in the output, the query takes over 12 minutes to execute.
I cannot index FieldTwo and FieldThree because of the field size and I cannot reduce the size of the fields because of the data that needs to be stored in it? How can I index or do something similar to speed up the table look up.
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _SubCategories.SubCategoryName, _SubCategories.SubCategoryName, LinkName FROM _Links INNER JOIN _SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId INNER JOIN _SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _Links_Categories.CategoryName, _Links_StatusCategories.StatusName, LinkName FROM _Links INNER JOIN _Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId INNER JOIN _Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:
Users ID Username
ChangeLog ID Description UserID DateChanged
The Users table will contain one record for each user in the system.
The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.
The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.
So far i have written this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged FROM Users LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID) ORDER BY ChangeLog.DateChanged DESC
However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged FROM ChangeLog LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID) ORDER BY ChangeLog.DateChanged DESC
The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.
I'd appretiate any help you can give me at all, thanks in advance!
