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


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

will create / drop a lot of indexes affect sql performance?

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
 
if so, how badly it affects the performance? disk, memory or cpu?


View Complete Post


More Related Resource Links

Create a placeholder for drop down lists

  

First time post here, if i'm in the wrong area please move as necessary. Thanks

I'm using VWD with a database back end. I have a list of teams which are marked off by league, level, division, teamid and then using gridview for the list of players per teams.


I've got the drop downs working correctly but running into a little snag and that is on the auto postback.

When I only have one option come up in a list, i obviously don't change that list and thus it doesn't change the next drop down because there is no post back. is there a way to use a "header" value?

Example of my data

League Level Division Team

NHL 1 West Vancouver
NHL 1 West Calgary
NHL 1 West Edmonton
NHL 1 Central Chicago

OHL 2 West London
OHL 2 West Guelph

NBA 1 Central Chicago
NBA 1 Central Detroit
NBA 1 Central Milwaukee

So my drop downs choose by league, then level then division and then team then shows the player roster for that selected team.

The problem I run into is when only one value exists based on the previous choices I've made. If I choose NHL then Central only Chicago shows up in the example above. Because I'm not choosing between it and another value, the drop down does not put in a post back and thus I don't get a list of the team and then the players

How does the IN ( ) clause affect performance?

  
When you have a query like this... Select * from Table1 WHERE a=1 and b=2 and c IN (5,6,7,8,9,0,1,2,3,4) How does SQL process that? I am trying to figure out if there is any real performance difference between the IN ( ) clause and a join to a temp table. Thanks! Jim

Drag Drop adorner visual performance

  
Hi, I've been modifying my application based on a MSDN blog post on WPF drag and drop.   While I found the blog post very helpful, what I've noticed when trying to use a visual for a drag indicator (rather than either a default or custom cursor), is that the visual doesn't 'keep up' with the cursor movement.  That is, if I move the mouse quickly during a drag/drop operation, there is a space between the visual adorner being moved around and the cursor.  When I stop movement (but still have the mouse button down so the drag/drop operation is still active), the visual 'catches up' with the standard cursor.  And if I were to move the mouse slower, then the standard cursor and the drag drop visual positions are kept in sync. This lag between the movement of the 'real' cursor and the visual is even more obvious in my application than in blog post sample application, although it is present in the sample application as well.  My question is, is there anyway to improve the performance of a custom drag drop operation using a visual such that I can quickly move around the mouse and not see a gap between the cursor and the visual? Thanks, Notre

Create drop-down filter for External List

  
I hope this is an easy question. I will have an external list of a bunch of people.  There are several ways I want to display this data: All of them, Only the ones that have a value of 1-4 in the status field, Only the ones that have any other value in the status field, and people that have a value of 1-4 in the status field, but are at a certain location. I want to be able to put a drop-down filter above the list where people can select that. Could someone please point me in the right direction? Time is of the essence.  

Dynamically Drop and Create tables, overcoming 4000 charachter limit

  
We have a Navision SQL-server database for 7 companies. Each table starts with the name of the company. Except for the CompanyName-part, the tables names are equal. I made a Foreach loop that dynamically transfers tables/data from the the Navision-database to a staging-database. I also want to Drop and re-Create the tables dynamically. However, if I try to do that via an expression in a SQL Task, SSIS complains about the 4000 character limit. What is a better way to do this? I want to execute SQL-commands and have SSIS replace part of the tablename with the contents of a variable (being the name of one the 7 companies). I have no access to the Management Studio or the DB so I need it to be done within the package. What is the best way to do that?

Improving performance of UPDATE CUBE by executing preceding CREATE CACHE for the same cell / subcube

  
Hi All, Recently I came across one of suggestions by Microsoft Advisory consultant regarding optimization data write-back pattern for the SSAS cubes. It was recommended issuing CREATE CACHE command following by UPDATE CUBE for the same cell/subcube definition: CREATE CACHE FOR [MYCUBE] AS ("Cross join of all the dimension members defining subcube to be updated") UPDATE CUBE [MYCUBE] SET ("Cell") = 100, ("Another Cell") = 200,... etc. for every cell. This seems quite weird, but based on our benchmark the entire process (CREATE CACHE and UPDATE CUBE) takes less time than just UPDATE CACHE. Folks, can someone advice on the insights of the process? Shell this approach to be suggested for an enterprise-level planning application? Regards, -h

Create a picture that can be marked by the user using drag and drop

  
I am trying to see if it's possible for me to have a picture like of a car and have it to where users are able to take a symbol and mark up the diagram with it.  Without needing a template pen.  Thanks for any help and advice.

100% performance drop with TDE

  
I am trouble shooting a prod issue but to keep things simple. On a test server, with nothing else running. DBCC dropfreebuffers between tests I executing a Select Into  into  that includes a group by. First I run it on the database unencrypted then I encrypt the database using 256 AES. I flush the cache and execute the statement again. It takes more than twice as long to execute on the encrypted database.  An hour on the unencrypted and 2 hours on the encrypted.  FYI the server 16  CPU with 64 Gigs. I have repeated this test multipe times with the same resultes. Is anyone else seeing this type of performance hit with TDE ?

Re creating indexes while data load - Performance Consideration

  

Hi,

In the ETL design we have followed drop index before data load and recreate it after load. But looks like this approach is time consuming.

Is there any better data load design approach? Will table partition solve this problem?

Thanks,


- JL

How to auto create (or Generate a script) which can create indexes on subscriber

  

In our scenario, subscriber and publishers have same tables and we want the indexes also to be same at all places..

How to auto create (or Generate a script) which can create indexes on subscriber which are already added on publisher.. Is there a auto way or is there any article somewhere which can help..

 


IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance drop for WCF service in IIS after worker recycling

  

Hello, we are currently doing some performance tests for our WCF services running on IIS 7. Performance is as expected with moderate CPU and memory usage (CPU usage less than 40%). We are facing a problem when IIS recycles a worker process: After the recycle the throughput drops by about 1/2 with multiple times longer response times.

Some details about the setup:

  • Dedicated machine for performance testing, no other services installed
  • Services used by tested services are installed on a second, dedicated machine as mock services
  • The problem is significant for services that use other services using WCF (i.e. tested services are both, server and client)
  • The service parallelizes requests to a WCF mock service on the second machine (about 8 outgoing requests per incoming request)
Any ideas what might be happening here?

How much can affect the performance heavily loaded sql-server intensive use of such tools as SQL Ser

  

How much can affect the performance heavily loaded sql-server intensive use of such tools as SQL Server Profiler ?

5%   ?

15% ?

30% ?


Does dropping a table automatically drop its contraints and indexes?

  
When I have a table that has foreign key constraints and/or index and generate a delete script with Management Studio, the script explicitly drops all constraints/indexes before dropping the table.  Is this necessary?  Or does dropping a table automatically "cascade delete" all that table's constraints, indexes, etc...?

Updating multiple Indexes on a table - Performance issue

  

Hi

I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

any advice greatly appreciated

Conan


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