.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Problems joining Partitioned Tables and using the partitioning!

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server
 

Hi,

I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.

So, i am trying to use a query like this:

select ....
FROM SNAP S
INNER JOIN TABLE_A A
 ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
 ON S.DAY_CODE = B.DAY_CODE
 AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923

Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It runs for more than 12 hours and no results ).

If i change the query to:

More Related Resource Links

Problems joining Partitioned Tables and using the partitioning!

  

Hi,

I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.

So, i am trying to use a query like this:

select ....
FROM SNAP S
INNER JOIN TABLE_A A
 ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
 ON S.DAY_CODE = B.DAY_CODE
 AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923

Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It runs for more than 12 hours and no results ).

If i change the query to:

Joining complex SQL tables

  
Hi, I am new to SQL query. I would like to join two SQL queries (MS Office Query and Computer Details Quer) into one. The tables output can be found at http://rapidshare.com/files/412281201/SQL_query.zip Or, from lsoon3@gmail.com Thanks for your guidance, below are the query:  1) MS Office Query: ================ select v_Add_Remove_Programs.DisplayName0 AS [MS Office Edition] from v_Add_Remove_Programs where DisplayName0 like 'Microsoft Office Project Professional%' OR DisplayName0 like 'Microsoft Office Visio Standard%' OR DisplayName0 like'Microsoft Office Visio Professional%' OR DisplayName0 like 'Microsoft Office Enterprise%'OR DisplayName0 like 'Microsoft Office Standard%' OR DisplayName0 like 'Microsoft Office Professional%' 2) Computer Details Query: ======================= select  distinct  v_R_System.ResourceID,  v_R_System.Netbios_Name0 AS [Computer Name],  v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],  v_Site.SiteName as [SMS Site Name],  [Top Console User] = CASE  when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')  then 'Unknown'  Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0  End,  v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],  v_GS_OPERATING_SYSTEM.CSDVers

Joining tables from 2 SQL Server CE databases

  
Is it possible to join 2 or more tables in different SQL Server CE databases? I am currently using VB.NET 2008 and SQL Server CE 3.5.

joining two tables using dataservice

  
joining two tables using dataservice

ASP.NET insertion and joining data between tables

  
I have the following tables:1.) PRODUCT_INFORMATION2.) CUSTOMERS3.) DISTRIBUTORS4.) PRODUCT_NAME5.) CONTROLLEREach table has a primary key assigned. The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table. I want to know how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION  table?Please suggest!!

DROP CONSTRAINT AND ADD/MULTI TABLES PUBLISHER, PROBLEMS IN SUSCRIPC....

  
Sorry my terrible english,   I will try to explain my problem, I made some data changes at the publisher and I had to throw away the PK and FK cascading multiple tables. The problem I have is that these changes were trying to apply to the subscribers and can not do because it does not in order which I did step by step through the Publisher. subscribers now show me this error and does not synchronize anything, and takes 0.5 sec and gives the error infinitely I need help, which infinitely grateful Error messages: The schema script ' if exists (select * from sysconstraints where constid = object_id(N'[FinancieroAbonos_PK]')) if object_id(N'[dbo].[FinancieroAbonos]') is not null exec('ALTER TABLE [dbo].[FinancieroAbonos] DROP CONSTRAINT [FinancieroAbonos_PK] ')' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 The constraint 'FinancieroAbonos_PK' is being referenced by table 'FinancieroAbonosContabilizacion', foreign key constraint 'FinancieroAbonos_FinancieroAbonosContabilizacion_FK1'. (Source: MSSQLServer, Error number: 3725) Get help: http://help/3725 Could not drop constraint. See previous errors. (Source: MSSQLServer, Error number: 3727)

DataAdaptor/Dataset problems when no row present in database tables

  
Hi All, I'm trying to use a DataSet to maintain some rows for a table, and when I've finished my changes, send all changes to the database using a SqlDataAdapter.    I find if there are no rows in the table in the database then I am getting a 'Object reference not set to an instance of an object' when I try to access the table in the Dataset. Is there a way to work with a Dataset like this ie. I start off with an empty table and I wish to add rows, to access the structure of the table rows, build rows, then add them and do the update on the SQLDataAdapter. Thanks, Sinead Here is my code: protected SqlDataAdapter memberDA = new SqlDataAdapter(); protected DataSet memberDS { get { if (ViewState["memberDS"] != null) return (DataSet)ViewState["memberDS"]; else return new DataSet(); } set { ViewState["memberDS"] = value; } } protected SqlDataAdapter getDataAdapterForMembers() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["SiteDBConn"].ConnectionString; memberDA = new SqlDataAdapter("usp_GetMembers", conn); memberDA.SelectCommand.CommandType = CommandType.Stored

Joining tables from diffrent sites

  
We would like to setup some Master Lists under the Home site such as "Customer Data", and "Employee Data" and then under our multiple Sub-Sites setup Lists that have columns joined back to those tables created on the home site.  I can Join Lists which are under the same Sub-Site however I am not able to join Lists which are under the Parent Site. Is there a way to do this?   Thanks

adding mor tables to datasource view causes writeback performance problems

  
Hi,

I've got a very strange behavior in one of our SSAS databases. We had performance issues so i've tried to put only the essential parts into one OLAP Database

the result is:

Just by adding more tables to the datasource view the writeback performance drops from 0,2sec to 3 sec. No changes to the cube, no changes to the dimensions.
The t-sql statement fired for the writeback remains the same.
Any idea?

regards,

Michael

Problems trying to format HTML tables

  

I have two one-line x 2-column HTML tables that I want to horizontally line with each other.  To be specific, here's the formatting I'm looking for:

                                 ----------------------------------------------------------------------------

                                 |                    Feet of Advance:  | [TextBox] ft                       | 

                                 ----------------------------------------------------------------------------

                                 |         

Problems in deploying dimensions composed of related tables

  

Hello,

I have a new dimension with attributes from different related tables but when I try to deploy it, I get the error message "Errors in the high-level relational engine. The table that is required for a join cannot be reached based on the relationships in the data source view." I checked the DSV and I have these tables with relations! What am I doing wrong?

Thanks.


Indexes on Partitioned Tables

  

I have a table that is used for staging.  It has a unique clustered index on it that has the same partitioning as the table.  I want to know if there is a way for me to add a non-clustered, non-partitioned index to the table and still be able to do the partition swapping.  Are there any work arounds?  I have found that because the indexes are 'not-aligned' the swapping fails to insert into the table with the multiple indexes.

Thanks.


Need some help with logic joining 2 tables if record doesn't excist yet

  

Hello,

I have a table "assignments" with records that are  assigned to multiple "users" by the "department" they belong to. Each user then can have it's own "status" for each assignment.

When the user  logs-in i need to show all his assignments and the status.(but status record doesn't exist yet)

When a new assignment is created I don't want to have to create a new record relating the assignment to the user. I want the relationship to be by the department the user belongs to and the department the assignment was assigned to.

Only thing i can think of is when the user logs in to load the assignments that belong to the user's department then look thru all the records. For each record loop thru the status table. If a record exist show the the status. if it doesn't then make default value and when the users clicks to view the assignment a new status record will be added.

Only thing i don't like is looping thru the status records while looping thru all the assignments.

How else can I do it? I don't see how could join the assignments table and the status table if the status record doesn't exist yet.

Thank you

tables:
[ASSIGNMENTS]
assignmentID
departmentID

Joining of Tables

  

Firstly,Ive created a table "StudentInfo" in Ms Sql .

Secondly,Ive a "ContactList.xls" in Excel 2007 which I had already imported it into MS SQL.

The problem is,how can i join the StudentInfo and ContactList into 1 table.


joining 3 tables and using join to only include records not found in other table

  

Hi,

I have 3 tables. I would like to get the number of records in table A that do not have cooresponding co_asset_ids in TABLE C but also need
to be be found in TABLE B and where the asset_id = 3. Said another way: If TABLE A record is in TABLE B and does not find a co_asset_id of 3 as found in table C then show these records.

assetno 4 and 5 are irrelevant but added to fill out the data more.

I know I could do this getting the co_asset_id column into TABLE A and then do some joins between TABLE A and TABLE C and leave TABLE B out, which I have done in the past. I would like to finally know if this can be done by using the 3 tables together in one elegant query.

The result of this query for this example data  would be:
TableA
assetno
1
3

Table A
assetno
1
2
3


Table B
assetno    co_asset_id
1              11
2              22
3              33
4              44
5              55

TABL

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Top .NET Performance Problems and how to avoid them

  
Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend