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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Guid clustered index fast select and slow insert

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server


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?


View Complete Post

More Related Resource Links

Simple select on client very slow, fast on server


We copied a database from SQL Server 2005 to SQL Server 2008 R2 on a new server.

On the new server (24 cores, 24 gig memory) simple select statements from my client with SSMS that returns 10000 rows take about 50 seconds. The same query on the old server (1 core) takes less than 1 second. Directly on the new server the query runs very fast (also less than 1 second).

Execution Plans on both servers are exactly the same.

Client Statistics shows on the old machine under "Client processing time" 656 and on the new server 51018.

The same problem occurs on all other clients and with any other larger query/resultset.

It looks like a network issue. But the new server is located in the same datacenter and has a 1000Mbps network connection.

Any help is greatly appreciated.


FAST search query is slow...

Hi, we are using fast search for sharepoint 2010. We are crawling an external data source and this is used for search queries. we have around 30 metadata properties and all are full-text indexed. however, the search operation is slow for a database of 5k records in intranet (webserver and database server are in different geographic regions). typically a 100 item search takes 9-10 sec. it get worse over internet and the search query varies from 10-45 sec for 100 items. Any pointers will be helpful. thanks.  

SQL Insert with Index Scan Problem....

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

Does inserting a non clustered index reduce IO?

I ran a report on my prod server (Performance - Top queries by total IO) and found the following query the 2nd highest.   select * from Table1 where Col1 is null order by Col2, Col3, Col4 Table has 100,000 rows. 1 pk and about 30 columns After running it against engine tuning advisor it advised, if i created a non clustered index on Col1, Col2, Col3 and Col4 it would increase the performance by 77%. But when i run the command just by it self it runs and completes instantaneously. What am i missing? Should i create the index to reduce the IO's as the Advisor suggests?    

Clustered vs NC index storage differences?

Does clustered index takes more disk space than NC for the same filed(s) for large tables? Also is CI takes longer (time) to build than NC if you have large tables? Any microsoft articles supporting these aruguments.


My application (LabVIEW with ADODB._Connection) produces 1000 new float values  every 10ms. Unfortunately it takes 600 ms to insert these values row wise into my 1000 column table, using the folowing command: INSERT INTO Benchmark_Table VALUES (404,0.000,0.136,0.807,0.633,0.372,0.530,... ); Is this something I have to live with, or is there a faster way to do that? I'm using SQL Server 2008 R2.

Insert a "Select User" row in Linq for a dropdown list

I'm new to Linq. I have searched and searched the web for a soluion, and can't find anything. I have a Linq query and I want to insert a row ("Select User") to the top before I pass it to the drop down list. I've been trying to use the Union but to now avail (it keeps telling me that my object doesn't support the Union method). My code, prior to attempting to inser a row, is very simple.public SelectList DropDown_Users()        {            var context = new VivarianDataContext();            var query = from t in context.AspnetUsers                        select new { t.UserId, t.LastName };            list = new SelectList(query.AsEnumerable(), "UserId", "LastName");            return list;        }Now I try to insert a row and I found this on the internet and it seems to say that his solution will work. But it is filled with syntax errors. http://magicode.wordpress.com/2009/08/20/inserting-an-item-in-iqueryable-object-using-union-met

Form Display is very slow to display, (using large multi-select lists)

Hi, I am looking for some suggestions on how to speed up a few very slow forms. We have created some Leaver and Joiner type forms for our IT guys. They contain some lookup data , and we need to pick multiple values from a hidden list. Unfortunately this (Systems/Roles) list is large (3000 rows and growing). On a couple of forms there are 2 of these multi-select boxes, and things are getting pretty slow to display. Any suggestions? ---------- Background Info / things I have tried: The forms are based on Site content types and Site Columns. A set of 5 Combo boxes is slower than one Multi-select list. SPDesigner is available but I do not have Central Admin rights I am not alowed to write/install custom code on the server. (Visual Studio code / Codeplex code / extra Features etc) :-( I have had to resize the Multi-select list, so am using the SPServices JQuery call to make the multi-select box wide enough for the text. But this is extra overhead so I might have to back that out / replace with a fixed width for the multiselect box (if I can find it's id, so I can do that in Javascript). Filtering the Multi-select box is possible, but only after the page has initially loaded (it seems) (again I tried the SPServices Jquery call) The Company intend to add more Content types, so I am trying to avoid a custom Newform.aspx for each type (Leaver / New Joiner / Transfer/ C

XMLDML insert is dog slow for real inserts in SQL Server 2005

I put together a little sample after noticing my XML solution was taking more than three seconds every time I inserted something over 20 records with a reasonable number of attributes. XML updating is apparently not ready for production code in 2005. This is faster in SQL Server 2008, and might work for some applications, but you'd actually write the code a little differently there. In any case, it's far slower than writing out the string yourself. Anyhow, if anyone asks you why you're not using XML.modify('insert in SQL Server 2005, you can run this for them.  DECLARE @now DATETIME, @then DATETIME, @elapsed INT, @now_disp NCHAR(24); SET @now = GETDATE(); SET @now_disp = CONVERT(NCHAR(24), @now, 126); RAISERROR('starting timer at %s', 11, 1, @now_disp) WITH NOWAIT; DECLARE @root XML, @insert NVARCHAR(MAX), @counter INT, @child XML, @att NCHAR(3), @attdex tinyint; SET @root = '<root/>'; SET @counter = 24; WHILE @counter < 26 BEGIN SET @counter = @counter + 1 SET @child = CAST('<child/>' AS xml) SET @attdex = 0; WHILE @attdex < @counter BEGIN SET @attdex = @attdex + 1; SET @att = CHAR(96 + @attdex) + 'ow'; SET @insert = 'SET @child.modify(''insert attribute ' + @att + ' {"value"} into (/child)[1]'');'; EXEC sys.sp_executesql @insert, N'@child xml OUT', @child

select LEN from left index in sql statement ?


hi all

i use this select statement to select lenght after the (.) in my sql statement




listprice1 ,



How To Map A GUID Variable And Use It In An OLE DB Source Data Flow Select Query


I have a simple SSIS package that does the following. 

On the control flow tab, there is an Execute SQL task that calls a stored procedure and returns a GUID. 

I have a package scoped variable that stores a UNIQUEIDENTIFIER output from the stored procedure. It appears that the package variable data type does not include a GUID data type so I tried to use string instead.

Next, the Execute SQL task is mapped to a data flow task. 
Within that data flow task I have an OLE DB Source which connects to a SQL Server Destination task.

On the OLE DB Source Editor screen I have a query that performs the following:

SELECT ID, SessionKey, ....
FROM dba_DroppedLoginsLog
WHERE SessionKey = ?

I have clicked on the parameters button to map the Parameter to my variable called User::SessionKey. This is the output variable from my Execute SQL Task component on the Control Flow tab. This value should be a GUID, but again, I don't see a data type for that.

When I execute the package it fails on the OLE DB Source with the following error:
[OLE DB Source [1908]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE D

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

non-clustered index



Can we you create a non-clustered index over a column already having clustered index



How to use the table which is created with CTE(Common Table Expression) more than one select,insert


I faced one problem while working with Common Table expressions in Sqlserver 2008.

the problem is How to use the table(means temporary table)  which is created  by with  CTE's after that one select statement.

Actaully the table(which is created using with CTE) is automatically deleted, one's after execution of any select,insert,update or create view statement .Right!

But I needed that table second time also. How it is possible.

please help if any one knows..........

Thanks in Advance........


Do I really need Clustered Index??


I read in so many articles, some books and listen some expert's words and even microsoft recommended that, A table with Clustered Index will performs better than Heap in most of the cases. I too believed same until today. But today, I saw in my project, out of 2,000 tables we have, only 50-60 tables have CI only, and remaining tables are all heap only. Still our 90% of stored proc. always will takes less than 1 or 2 seconds, even when they tested with millions of data. 

            I never had chance to look into the DB, as i will not work directly with proj. dev. Today only I saw this. i asked one of my colleague about this. He replied that, when insertion/updation/deletions performed, CI will take more time, If it is heap, it wont take more time. Yes, What ever he is telling is true. It needs to adjust the indexes, page splits also might happens...  

a Heap has following advantages over CI.

1. Insertion/Updations/Deletions faster...

        Yes, as its an web application, which will be continously use by the people, no of DML operations will happen. 

2. Coming to Selections, I saw the tables have Non-CI, which almost covers all the queries.... and more over the Because of the Paging implementation, always, only 25-50 records will be selected,  

sql insert very slow on bulk table



We are using SQL Server 2005 x86 enterprise edition. We have one table which has 13 crores / 130 million records. When we insert a record in that table it is taking around 30 seconds. Can u please help optimize it.

The table currently has 1 primary key (Clustered Index) and 4 non clustered index.

Please help.



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!!





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