Sunday, April 7, 2013

[T-SQL] Confused selecting Info From Two DB

[T-SQL] Confused selecting Info From Two DB

Confused selecting Info From Two DB

Posted: 07 Apr 2013 12:12 AM PDT

i have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in both DB(but there is no relationship in AgencyBackupDB between Tables).this is my question: I want select all info from AgencyBackupDB in tables SabtHazine and Hazine and HazineGroup.i want if HazineID from Hazine table Doesnt Exist in AgencyBackupDB select it From AgencyDB and do same for HazineGroup.this is script of All Tabel With Test Data[code="sql"]CREATE TABLE [dbo].[HazineGroup]( [HazineGroupID] [smallint] IDENTITY(1,1) NOT NULL, [HazineName] [nvarchar](150) NOT NULL, CONSTRAINT [PK_HazineGroup] PRIMARY KEY CLUSTERED ( [HazineGroupID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_HazineGroup] UNIQUE NONCLUSTERED ( [HazineName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[HazineGroup] ONINSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (6, N'برق')INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (1, N'شستشویی')INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (2, N'نظافتی')SET IDENTITY_INSERT [dbo].[HazineGroup] OFF/****** Object: Table [dbo].[Hazine] Script Date: 04/07/2013 17:34:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Hazine]( [HazineID] [int] IDENTITY(1,1) NOT NULL, [HazineGroupID] [smallint] NOT NULL, [Mablagh] [int] NOT NULL, [HazineName] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Hazine] PRIMARY KEY CLUSTERED ( [HazineID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Hazine] ONINSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (1, 1, 20000, N'صابون گل')INSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (3, 2, 10000, N'بروس')SET IDENTITY_INSERT [dbo].[Hazine] OFF/****** Object: Table [dbo].[SabtHazine] Script Date: 04/07/2013 17:34:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[SabtHazine]( [SabtHazineID] [int] IDENTITY(1,1) NOT NULL, [HazineID] [int] NOT NULL, [EndUserName] [nvarchar](20) NOT NULL, [Tedad] [smallint] NOT NULL, [sabtHazineDate] [date] NOT NULL, [Describtion] [nvarchar](500) NULL, CONSTRAINT [PK_SabtHazine] PRIMARY KEY CLUSTERED ( [SabtHazineID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[SabtHazine] ONINSERT [dbo].[SabtHazine] ([SabtHazineID], [HazineID], [EndUserName], [Tedad], [sabtHazineDate], [Describtion]) VALUES (6, 3, N'SA', 3, CAST(0xC2360B00 AS Date), N'')SET IDENTITY_INSERT [dbo].[SabtHazine] OFF/****** Object: Default [DF_SabtHazine_Tedad] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[SabtHazine] ADD CONSTRAINT [DF_SabtHazine_Tedad] DEFAULT ((1)) FOR [Tedad]GO/****** Object: ForeignKey [FK_Hazine_HazineGroup] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[Hazine] WITH CHECK ADD CONSTRAINT [FK_Hazine_HazineGroup] FOREIGN KEY([HazineGroupID])REFERENCES [dbo].[HazineGroup] ([HazineGroupID])GOALTER TABLE [dbo].[Hazine] CHECK CONSTRAINT [FK_Hazine_HazineGroup]GO/****** Object: ForeignKey [FK_SabtHazine_Hazine] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[SabtHazine] WITH CHECK ADD CONSTRAINT [FK_SabtHazine_Hazine] FOREIGN KEY([HazineID])REFERENCES [dbo].[Hazine] ([HazineID])GOALTER TABLE [dbo].[SabtHazine] CHECK CONSTRAINT [FK_SabtHazine_Hazine]GO[/code]

Query performance help needed

Posted: 06 Apr 2013 07:34 AM PDT

I have multiple pairs of "Offers" and each Offer is comprised of several lists of "Items".My pairs of Offers are defined in one table (OfferOverlaps) with about 200,000 rows, and I have another table (OfferItems) which contains the mapping of each Offer's lists of Items with about 30,000,000 rows.Using these two tables I need to find the rows from OfferOverlap where the two overlapping Offers defined in each row contain at least one common Item in OfferItems.I first tried this:select distinct OO.OfferID, OO.OverlappingOfferID from OfferOverlaps OO join OfferItems OI1 on OO.OfferID = OI1.OfferID join OfferItems OI2 on OO.OverlappingOfferID = OI2.OfferID where OI1.ItemID = OI2.ItemIDBut the performance was not satisfactory.I also tried to use exists in an attempt to try to get the comparison of matching ItemID's to stop immediately after the first match was found rather than evaluating all matches (potentially over a million) between each set of OfferItems:select distinct OO.OfferID, OO.OverlappingOfferID from OfferOverlaps OO join OfferItems OI1 on OO.OfferID = OI1.OfferID join OfferItems OI2 on OO.OverlappingOfferID = OI2.OfferID where exists (select OI1.ItemID intersect select OI2.ItemID)The performance was the same. In fact, the query plans are identical for both of these approaches.I would be very grateful for any ideas you might have about a different approach to this query problem that would yield better performance than these approaches that I've tried.Below is a simple example that sets up an illustration of the query problem.[code="sql"][font="Courier New"]--drop table OfferOverlaps--drop table OfferItems-- 200,000 rows in this tablecreate table OfferOverlaps ( OfferID int, OverlappingOfferID int, constraint cpk_OfferOverlaps primary key clustered (OfferID,OverlappingOfferID))insert into OfferOverlaps values (1, 6), (3, 8), (5, 9);-- 30,000,000 rows in this tablecreate table OfferItems ( OfferID int, ListID int, ItemID int, constraint cpk_OfferItems primary key clustered (OfferID,ListID,ItemID))insert into OfferItems values (1, 1, 111), (1, 1, 222), (2, 2, 222), (3, 1, 333), (3, 2, 888), (4, 1, 444), (4, 2, 555), (5, 1, 555), (6, 1, 444), (6, 2, 666), (7, 1, 777), (8, 1, 333), (8, 1, 888), (9, 1, 999);select distinct OO.OfferID, OO.OverlappingOfferID from OfferOverlaps OO join OfferItems OI1 on OO.OfferID = OI1.OfferID join OfferItems OI2 on OO.OverlappingOfferID = OI2.OfferID where OI1.ItemID = OI2.ItemIDselect distinct OO.OfferID, OO.OverlappingOfferID from OfferOverlaps OO join OfferItems OI1 on OO.OfferID = OI1.OfferID join OfferItems OI2 on OO.OverlappingOfferID = OI2.OfferID where exists (select OI1.ItemID intersect select OI2.ItemID)[/font][/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog