473,889 Members | 2,031 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CREATE INDEX on large table

SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
that we need to add some indexes for. In a test, it took over 12 hours
to CREATE a new INDEX against this table. One of us suggested that we
create a temp table with the new index and copy the data from the old
table into the new one, then rename it. I understand this took 15
minutes. Why the heck would it be faster to move the data and build
multiple indexes incrementally vs adding an index??

Jul 23 '05
11 27219
I suggest the real issue is:
The only operation which we have reliably reproduced as slow is adding a column with a default value to a large table - which is taking on the order of 20 minutes in the million row range. We do this a number of times.


Why would you frequently add columns to a three million row table?

As far as selectivity goes, you don't gain any advantage to having a
default value of 0 vs. having a default value of NULL - it is a
disadvantage because you are frequently adding columns to a 3,000,000
row table. If the default value is NULL, SQL Server does not have to
rebuild the table when you add the value - NULL is nothing as far as
SQL Server is concerned.

The statistics histogram, based on only a select few companies having
this feature, would look something like:
_______________ __________|

Jul 23 '05 #11
Point well taken regarding NULL vs 0. The reason we use 0 instead of
NULL is because most of the software and reporting is "confused" by
nulls. MFC recordsets return the inconvenient tvalue of

#define AFX_RFX_LONG_PS EUDO_NULL (0x4a4d4120L)

for null integers unless you specifically write code to check for null.
We have a large, old codebase which has no null handling code.

This is a function/service pack, so we commonly add a number of new
fields to support new functionality. In our 3rd test, the upgrade
script took 3 hours - which is in the "normal" range. I guess we are
going to attribute the earlier results as anomalous for now and monitor
for another occurrence. The index is useless for customers not
utilizing the feature, however, if the cost is only 2 minutes, it is
easier to just add the index for all customers. For those customers
using the feature/index, it is highly selective.

Jul 23 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2828
by: Guy Deprez | last post by:
Hi, i'm having a problem to create indexes. STEP 1 ----------- Connection is OK (you can find the string at the end of the message) Table ("Couleurs") creation is OK STEP 2. Index Creation
8
3280
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run simultaneously 4 similar queries it takes nearly 5 minutes instead of 4 times 9 seconds or something near of that. here is a sample query: select mertido, fomeazon, ertektipus, mertertek from t_me30 where fomeazon in (select distinct fomeazon...
7
10824
by: Bing Wu | last post by:
Hi Folks, I have a very large table containing 170 million rows of coordinats: CREATE TABLE "DB2ADMIN"."COORDINATE" ( "FID" INTEGER NOT NULL , "AID" INTEGER NOT NULL , "X" REAL NOT NULL , "Y" REAL NOT NULL , "Z" REAL NOT NULL )
4
5886
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
3
6938
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when inserting/deleting rows from a large table. My scenario: Table (lets call it FACT1) with 1000 million rows distributed on 12
17
8513
by: Jeffrey W. Baker | last post by:
Greetings, I have a 23GB data table upon which I am building a primary key of three columns. The data is mounted in a 137GB device and pg_xlog is mounted on a separate 3.5GB device. I have configured 24 checkpoint segments, which I expect gives me a worst-case usage in pg_xlog of 384MB. Unfortunately, during the CREATE INDEX, pg_xlog becomes full!
2
1546
by: John Wells | last post by:
Guys, I have a general question about designing databases for large data sets. I was speaking with a colleague about an application we're preparing to build. One of the application's tables will potentially contain 2 million or more names, containing (at least) the fields first_name, last_name, middle_name and prefix. A common lookup the application will require is the full name, so prefix +
6
5946
by: Dilip1983 | last post by:
Hi All, I want to delete duplicate records from a large table. There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state. First of all when i tried to rebuild index it showed error as unique key violation. So i want to delete duplicate records for col1,col2,col3,col4 combination. How can i delete the duplicate records from this large table?
1
2611
by: vivek samantray | last post by:
I have a query.When i try to create a index on one of the table the index gets created but when i take the output it stuill shows "TABLE ACCESS FULL" Please see below what i did QUERY ====== SELECT b.DPT_EVNT_ID, b.DPT_ENTITY_ID, a.EDD_DT, b.DPT_DPT_ID, b.OU_ID,
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11207
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10794
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10896
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7153
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4652
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3259
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.