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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

SQL Insert with Index Scan Problem....

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
 
Hi,   I'm having a very strange problem when inserting rows into a table. Let me try to explain a little better: Table1 (for example Book) with a lot of fields and one foreign key to Table2 (idVolume) and a primary key (identity - idBook). This table has a clustered index only on the primary Key. (this table has something like 10 Millions rows) Table2 (for example Volume) with a lot of fields and a primary key (idVolume) identity. (this table has hundreds of rows)   Now, the problem starts when I make a insertion on Table2. When I made an insertion, it was taking minutes to finish, so I stopped it. After looking into the execution plan of the query, there was something very very strange, there seems to be a clustered index scan on Table1... the problem is that since Table1 has so many rows, this index scan takes forever... Can anyone explain me why does the insertion on Table2 makes an clustered index scan on Table1? It really doesn't make any sense to me.   Thanks in advance. João Teixeira


View Complete Post


More Related Resource Links

Cannot insert duplicate key row in object 'Table' with unique index 'Column_Id' Problem

  

All, We have a User where there is an Access Application installed. Every Day he enters some Account Numbers in it. This Access application is hooked to SQL Server 2008 which updates a table in SQL Server. Now last 2 weeks user had some Connection Issues so he did not enter any Account Numbers in the Access application instead he just noted them on a Excel sheet.

Now I did not want him to insert manually, so I took that Excel sheet, loaded account numbers in a Temp table and tried to insert all the Missing Account Numbers in the Main table. But it failed with the following error: Note: Account_No has a Unique Non Clustered Index on it and it is not a Primary key also. How can I insert them? (There are no duplicate account numbers as well that I am trying to insert its all Unique Accout Numbers)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Table_Name' with unique index 'Accoutn_No'.
The statement has been terminated.

 


SharePoint Calendar Error: The "ListViewWebPart" Web Part appears to be causing a problem. Index was

  
In one specific SharePoint calendar, on one specific week in that calendar when we select the "more items" link in the monthly view (or the expand all option) we get the following error:
The "ListViewWebPart" Web Part appears to be causing a problem. Index was outside the bounds of the array.

Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.
Troubleshoot issues with Windows SharePoint Services.

If we select a different week the expand and collapse works fine in that calendar.

Problem insert POCO to Velocity Cache

  

Hello,

I want to put POCO object to Velocity cache. How can I serializable ICollection to Velocity cache, or exclude ICollection from serialization?

I got this exception

Type 'System.Collections.Generic.ICollection`1[Model.Collection]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute.  If the type is a collection, consider marking it with the CollectionDataContractAttribute.  See the Microsoft .NET Framework documentation for other supported types.


Thanks

Tomas



Nested listview fk-problem with insert

  

Hi! 

My problem is this, i have a nested listview that works fine but if i need to do an insert and the nested listview dont have retrived any values (that is no posts created) i cant get hold of the fk that i need. If i put a hiddenfield in the parents listview with the fk how can i find it in codebehind when i do an insert? 

Best 

E


Insert into and Store procedure problem

  
Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa

Basic data type problem with database insert

  
Hi,I'm trying to write some values into a database, but I think there's an issue with the data types.  Here's what I've got: public static string Hello(string pageId, string inOut) { string itemId = ""; string period = ""; var cookie = HttpContext.Current.Request.Cookies["visitorGUID"].Value; SqlConnection conn; SqlCommand comm; conn = new SqlConnection(GlobalSettings.DbDSN); comm = new SqlCommand("INSERT INTO myTable (memberGUID) VALUES (@memberGUID);", conn); comm.Parameters.Add("@memberGUID", System.Data.SqlDbType.Text); comm.Parameters["@memberGUID"].Value = cookie; try { conn.Open(); comm.ExecuteNonQuery(); return "Item added: cookie = " + cookie + " / pageId = " + pageId + " / inOut = " + inOut + " / pageId = " + pageId + " / itemId = " + itemId + " period = " + period; } catch { return "Item caught: cookie = " + cookie + " / pageId = " + pageId + " / inOut = " + inOut + " / pageId = " + pageId + " / itemId = " + itemId + " period = " + period; } finally { conn.Close(); } } At the moment I'm just trying to insert the value of the cookie into the database as a test.  It works fine when I

Index droping problem in SSIS package?

  
Hi All, I have one ssis package. After load the staging data I am creating index in one column. I am dropping the index end of package. But sometimes after create the index if I got any error I need to re run the package, already index is created so that’s why  once again I will get a error. So that purpose drop index statement I used before create index sqltask.If the first time package runs the table don’t have index. If the table have index it will drop the index otherwise it will skip that task. I used below code. But I am getting the error index is not dropped properly. IF EXISTS (SELECT name FROM sys.indexes WHERE name =   N'IND1_TRAN_DT ')  DROP INDEX  IND1_TRAN_DT  ON SLS_STNT_DTL_STGNG;   Thanks CMK

Bulk Insert Problem

  
I am trying to perform a bulk insert on pipe delimited file (approx 25 columns,5000+ rows of data).  Normally this is no problem however this file has a header and trailer record with no delimiters (1 column).  I could use a utility like grep to remove the header/trailer record but I would like to capture the entire file inside sql server. I can bulk insert the entire file into a single column (wide) that would include the delimiters.  My problem from there is to process the single column data into the appropriate rows /columns.  I am looking for a set solution as opposed to a cursor type solution. HTML?  Seems slow.  Cross Apply?   Changing the file format is not a viable solution. I’m likely to have more than a few of these oddball formats with a variable number of columns.  Any ideas?   I thank you for your time on this puzzle.

Table scan going on apart from having an Index

  

Hi,

I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.

where (datestamp>getdate() - 'sep28 1902 12:00 AM')

I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query. 

If the index is place inappropriately there would be index scan but here I have table scan.

Could anyone help me in finding out why this is happening.

 

-Thank you,

Jayasree


Table scan going on apart from having an Index

  

Hi,

I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.

where (datestamp>getdate() - 'sep28 1902 12:00 AM')

I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query. 

If the index is place inappropriately there would be index scan but here I have table scan.

Could anyone help me in finding out why this is happening.

 

-Thank you,

Jayasree


remote server problem ; index file cant find my layouts

  

hi !

i finally managed to finish my first dynamic application with Webmatrix and razor , everything works well locally.

But there are issues online.

i got an index file and a layout file.

/test/index.cshtml

/test/layout/_layout.cshtml

in the index file , i've got the following code :

LayoutPage = Href("~/layouts/_layout.cshtml");

when i call the index page with

http://mysite.com/test/index.cshtml

everything works

but when i try

http://mysite.com/test (without the / at the end )

the server throws this :

Server Error in '/test' Application.

The specified layout page 'test/layouts/_layout.cshtml' could not be found. The following paths have been used: ~/test/layouts/_layout.cshtml;~/Shared/test/layouts/_layout.cshtml;

it seems it cant find the layout file.

any suggestions ?



insert date problem when culture is arabic

  

Hi

I want to insert date value in database

when my page loads i am setting the culture into ar-sa

and when insert date i am getting the problem

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Public Sub Add_Date(ByVal title As String, ByVal Schedule_date As Date)
        Try
            command.CommandText = "sp_Schedule_DML"
            command.CommandType = CommandType.StoredProcedure
            If Schedule_date = Nothing Then
                command.Parameters.AddWithValue("@optSelect", 0)
                command.Parameters.AddWithValue("@title", title)
                command.Parameters.AddWithValue("@sdate", System.DBNull.Value)
            Else
                command.Parameters.AddWithValue("@optSelect", 0)
                command.Parameters.AddWithValue("@title", title)
                command.Parameters.AddWithValue("@sdate", Schedule_date)
            End If
            If connection.State = Data.ConnectionState.Closed Then
                connection.Open()
            End If
            command.ExecuteNonQuery()
            If connection.State = Data.ConnectionState.Open Then
                connection.Close()
            End If
        Catch ex As Exception
            Throw New Exce

Query defaults to a Clustered Index Scan instead of using an Index Seek

  

I have query that behaves much differently the moment I modify the Where clause

 

The following query runs fast!!

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

SELECT

 

e51.ENTITY_ID, e51.PERF_FREQ_CODE, e51.END_EFFECTIVE_DATE,

Insert XML data into table problem

  

For the life of me I cannot figure out what's wrong with my SP. I'm receiving an XML document with a bunch of invoices like

<Invoices><Invoice><A><B><C></Invoice><Invoice><A><B><C></Invoice></Invoices> 

And I need to write each invoice as a record in SQL Server 2005

I'm getting the following error

Msg 170, Level 15, State 1, Procedure pcdAddInvoices, Line 39
Line 39: Incorrect syntax near '.'.

 

CREATE PROCEDURE pcdAddInvoices
 -- Add the parameters for the stored procedure here

 @msg xml
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 INSERT INTO [Promacoin].[dbo].[Tblinvoices_DAX] (ObjectID,ActivityID,ResourceID,ContractID,Document
           ,_LineNo,ContractorNO,Description,Qty,AmntTotal,DateInvoice,ProdWeek,ProdYear,Employee
           ,DocType,Comp_ERP_id,ContractorName,Eenheid)
 SELECT M.Invoice.value('/ObjectID','NVARCHAR(16)') AS ObjectID,
   M.Invoice.value(

Guid clustered index fast select and slow insert

  

Hi,

Don't know if this question is in wrong forum but I didnt find a better place.

I have a table called "CompanyApplications". The first column is a guid and is the PK (not clustered). The other colum is also a guid and is the company identifier. This is a guid because it comes from another table (the "company" table), an old table used for other stuff. Then I have a third and forth column for "ApplicationURL" and "ApplName.

My application will do alot of (since 30 000+ users) "select ApplName, ApplicationURL from CompanyApplications where companyId='<the company guid of the user>' order by ApplName). It will make very few inserts into this table. Only when one is inserting a new application.

I have some questions about this since I know it's not good to have a guid as clustered since it will have SQL Server to always resort the clusted index (because guids are "random"):

1. Is it wrong to have the companyId column as clusted in this example? If so, what is better?

2. I know inserts will be relatively slow when using guid as clustered. But in my case the insert does not need to be fast. Will the slow insert also affect selects from other users. I mean, will select's be slow for other users when the index is resorted becuase one user make an insert?

 


Index Property Notify Problem

  

A class has index property. For Example:

class A



{



  private Hashtable ht = new Hashtable();



  public object this[string name]



  {



    get { return ht[name]; }



    set { ht[name] = value; }



  }



}




The class implements property changed notify. Add codes as below:

class A : INotifyPropertyChanged



{







  <strong>public event PropertyChangedEventHandler PropertyChanged;



  public void OnPropertyChanged(string name)



  {



     if (PropertyChanged != null)



     {



       PropertyChanged(this, new PropertyChangedEventArgs(name));



     }



  }</strong>







 

[MOSS 2007]Problem on crawl and index

  

Hi all,

 

I'm here because I need some help on MOSS 2007 search and I don't find anything to solve my problems.

I have some problems when I try to crawl my sites collections.

On the server (MOSS 2007, Windows Server 2008 64 bits, SQL Server 2008 32 bits), I create many Web App fot my sites collections.

Each Web App has her own content data base.

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