Hi all,
I have a column which i want to put inside ascending indices, that is, empty column which i want to enter
1,2,3,4.....[tble no. of rows]
so this table:
col1 | col2 | col3
-------------------
| a | b
| c | d
| e | f
becomes:
col1 | col2 | col3
-------------------
1 | a | b
2 | c | d
3 | e | f
thnks,
Ahron
I've created a new table and added data to it. Now I want to index a given column. Will simply creating and saving the index index the column or do I need to do something else to create the actual index?
Barkingdog
Hi
I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?
OK, I imported 680 million records into an unindexed table. That went well.
Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.
It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?
SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN
Help! Thanks in advance!
In the following example, I have a "Person" table, and a many-to-many "Relationship" table which stores the r/ship between any two people:
[Person] [Relshp]
---------------- ---------------------
| PersonId PK|<-.-. | RelshpId PK |
| PersonName | '-| PersonId |
---------------- '--| RelatedToPersonId |
| RelationshipType |
---------------------
There are 2 FK constraints, linking
1. [Relshp].[PersonId] to primary key [Person].[PersonId], and
2. [Relshp].[RelatedToPersonId] to primary key [Person].[PersonId].
What kind of index structure would best support those FK constraints?
Would it be:
a) One combined index:
CREATE INDEX IX_Relshp ON Relshp (PersonId, RelatedToPersonId)
or
b) Two indexes:
CREATE INDEX IX_RelshpP ON Relshp (PersonId)
CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId)
or
c) Two "mirrored" combined indexes:
CREATE INDEX IX_RelshpP ON Relshp (PersonId, RelatedToPersonId)
CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId, PersonId)
I have currently one SQL FT catalog which indices couple of tables in on our server on nightly basis. It does a 'full' indexing of data as originally designed. Now the time it is taking to index all the data is unacceptable to user. I am working on it to make it index 'incrementally'.
But, for a short-term approach I want to find out, can we FT index a 'table A' in two catalogs on same SQL Server?
I am using sql server express edition and when i try to apply full text indexing on the table - the setting is set to "NO" and i am unable to set it to Yes
please advise - thanks Jeff
i have a directory database with approx. 80 million records. i am feeding the database with bulk_insert. Indexing one of the fields took about 8 hrs. After indexing when i run queries with the indexed field the response time is under 1 sec. However if i run select queries with like on non-indexed fields it takes more than 2 mins. So i decided to index 4 other fields in the database and it looks like the indexing process is going to run for 2 days.
i am a novice in SQL database design and i am not sure if this is the best way to index the table. i am just using create index. Any suggestions / advice welcome.
I am using a multi-statement table-valued function to assemble data from several tables and views for a report. To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row. Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns.
The problem I'm having is that the UPDATEs are taking forever to execute. I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns.
In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function.
Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions? I think that would improve the UPDATE performance dramatically.
Thanks,
Lee Silverman
JackRabbit Sports
Hi,
I am using SQL SERVER 2005 Express. I am trying to set up Enable a Table for Full-Text Indexing. I am following these instructions:
How to: Enable a Table for Full-Text Indexing (SQL Server Management Studio)
Hi experts,
Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns
CREATE View hrinu.Parity as
select
T1.Matcle as CorpID,
T2.Nmpres as Name,
T4.DATDEB as LeaveFrom,
T4.TEMDEB as PM,
T4.DATFIN as LeaveTo,
T4.TEMFIN as AM,
T10.LIBLON as LeaveType,
T8.LIBLON as Location,
T12.LIBLON as ParentOrg
from HRINU.zy00 T1,
HRINU.zy3y T2,
HRINU.zy39 T3,
HRINU.zyag T4,
HRINU.zy38 T5,
HRINU.zy1s T6,
HRINU.zd00 T7,
HRINU.zd01 T8,
HRINU.zd00 T9,
HRINU.zd01 T10,
HRINU.zd00 T11,
HRINU.zd01 T12
where T4.Nudoss = T3.nudoss
and T4.Nudoss = T1.Nudoss
and T1.Nudoss = T2.nudoss
and T3.nudoss = T5.nudoss
and T6.nudoss = T1.nudoss
AND T7.NUDOSS = T8.NUDOSS
AND T9.NUDOSS = T10.NUDOSS
AND T11.NUDOSS = T12.NUDOSS
AND T3.IDWKLO = T7.CDCODE
AND T4.MOTIFA = T9.CDCODE
AND T5.IDESTA = T11.CDCODE
and T6.stempl = 'A'
and t7.cdstco = 'z04'
AND T8.CDLANG = 'U'
and t9.cdstco = 'DSJ'
AND T10.CDLANG= 'U'
and t11.cdstco= 'DRE'
AND T12.CDLANG= 'U'
and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
and T6.dtef1s <= getdate() and T6.datxxx > getdate()
Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables.
Also throw some light on how to analyse the execution plan for further enhancements.
Thanks in advance
Regrds
Arvind L
Hi,
The other day we tried online re-indexing feature of SQL 2005 and it€™s performing faster than offline re-indexing. Could you please validate if it€™s supposed to do be this way? I always thought offline should be faster than online.
Thanks,
Ritesh
Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!
View 2 Replies View RelatedI have an SP which concatenates 2 columns in a where clause - ie WHERE [Column1] + [Column2] = @var. Â This, as far as I'm aware, is not going to access any seek on an index on a table.
My task is to create an index on this table and get the SP to access said index.
Aside from combining the two columns into one column on the table, how I can get an SP to access the newly created index when queried by the SP?
One thought of mine is to firstly index Column1 and allow the SP to access this index by inserting the rows into a temp table. Â Then a search on the temp table to retrieve the records on a search on Column2.
I'm working on re-indexing a table using some commands from ALTER INDEX REBUILD from Microsoft. The indexes will be tested for threshold fragmentation. my plan is once the reindex is executed, a transaction backup will occur while controlling the size of the log file. Â The query impose time limitations or stop reindexing after the specified amount of time has elapsed.
my question,
1. How can I integrate a query which checks if transaction log is getting full and which runs a Tlog backup if over 70%
2. How do I impose time limitation?
Hi!! I dont know if this is the correct forum for this or not, but still...Actually i wanted to know some details about SQl's Indexing services.
I found this link on my hosters help pages :
You need to use SQL Query Analyzer tool for this.
This will enable full-text indexing for the current database:exec sp_fulltext_database 'enable'
This creates a catalog:exec sp_fulltext_catalog 'catalogname', 'create'
This enables indexing of a table:exec sp_fulltext_table 'tablename', 'create','catalogname', 'indexname'
This adds a column to an index:exec sp_fulltext_column 'tablename', 'columnname', 'add'
This activates fulltext on a table:exec sp_fulltext_table 'tablename', 'activate'
These two enable automatic filling of the full-text index when changes occur to a table:exec sp_fulltext_table 'tablename','start_change_tracking'exec sp_fulltext_table 'tablename','start_background_updateindex'
From the above i get that i need to set up my database for indexing then make a catalog and then add an index of a table to this catalog. Can anyone point any good tutorials for using this is the proper way so that performance is not affected and tells me details on updating indexes etc(esp using some criterias). Moreover does indexing columns lower the performance? Is there a workaround? I am completely new to this.
What should I be looking at if I have real-time data (constant transactions) writing to a table that is experiencing index type problems? The table needs to constantly be re-indexed, which is slowing the whole transaction process down.
What can I look at or do to rectify this?
Thanks!
Hi,
Can you please help me find out if this statement is always true:
"Adding a new Index slows down updates"
This is more a general question, applicable as well for SQL Server.
If this is not the appropriate subforum then I kindly ask an moderator to move this thread to the appropriate sub-forum.
Thank you,
Ronnyy
Few questions about Indexing on tables:
1) How do I find out if there is any indexing already existing on the tables?
2) How often should tables be re-indexed?
3) Can you refer me to some useful article that talks about basics of indexing and how it could affect peformance?
Thanks
D.Harinath writes "Hi!
What is exact purpose of indexing.How it will increase the performance of the quey.
Can u give me example
*******with code********[not an theory]
For 1)Cluster and Non-Cluster indexing.
when Should use cluster and should not user cluster .What is major diffrence b/w them."
Okay, so i've been creating a .net app that basically gathers data from a web page, and then passes the parameters to a s.p. i wrote in sql, fetches a count, and displays the data to the webpage. My problem layes in that i have the query command timeout set to 1:00 but alot of my quries on the larger tables take longer then that to complete, so the page is timing out quite often.
i KNOW my problem is database design, i'm running an OLAP database. trasactions only occur once a week when we run a federal DO_NOT_CALL database update. i was wondering if anyone would be so kind as to help me tune my database a little more the get some more juice out of it. i can also tell you guys that i've notice every time a query is ran, the Diqk Query length tacs out to nearlly 100% for the entire length of the query. dont know if that helps.
Hi evryone...
I have a problem on indexing. The field PK_hrSetBenefitsLeave is the primary key of the table "hrSetBenefitsLeave". When i see it on the "Manage Indexes and Keys", the identity name became PK_hrSetBenefitsLeave_1. Everytime i change it to its original name will get me error...and i can't save it.
Error msg on saving :
'hrSetBenefitsLeave' table
- Unable to create index 'PK_hrSetBenefitsLeave'.
There is already an object named 'PK_hrSetBenefitsLeave' in the database.
Could not create constraint. See previous errors.
I tried to check using this query.
Select * from Information_Schema.Columns where column_name = 'PK_hrSetBenefitsLeave'
- it gives me only 1 record
my_compdbohrSetBenefitsLeavePK_hrSetBenefitsLeave1NULLNOchar55NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
Thanks.
-RON
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
Hello,I need some help understanding why my indexes do not seem to be affecting mysearches. I would really appreciate help understanding what indexes I needto make this query run faster. I realize that I use wildcards when searchingfor g1.gene_name, but is there anything I can do to make that less of aproblem? I ran EXPLAIN on the search I wanted to optimize and got thefollowing:EXPLAIN SELECT c1.SFID FROM Gene g1, cDNA c1, Transcript t1, Refseq r1 WHERE(c1.SFID = t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND (g1.gene_sym = 'hh'OR g1.genbank_acc = 'hh' OR g1.gene_name LIKE '%hh%')) OR (c1.genbank_acc ='hh' OR c1.SUID = 'hh') OR (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID =g1.SFID AND g1.locuslink_id = r1.locuslink_id AND (r1.mRNA_acc = 'hh'));+-------+-------+--------------------------+------+---------+------+--------+-------------------------+| table | type | possible_keys | key | key_len | ref | rows| Extra |+-------+-------+--------------------------+------+---------+------+--------+-------------------------+| r1 | index | mRNA_acc,llid,rma,rllid | rma | 25 | NULL | 20093| Using index || g1 | ALL | PRIMARY,llid,ggs,gga,gll | NULL | NULL | NULL | 190475| || c1 | ALL | PRIMARY,cga,cs | NULL | NULL | NULL | 43714| where used || t1 | index | gene_SFID,gS,cS,tg,tc | gS | 4 | NULL | 47238| where used; Using index |+-------+-------+--------------------------+------+---------+------+--------+-------------------------+I have the following indexes (which were all added after the database waspopulated):ALTER TABLE cDNA ADD INDEX cga(genbank_acc, SFID);ALTER TABLE cDNA ADD INDEX co(organism, SFID);ALTER TABLE cDNA ADD INDEX cs(SUID, SFID);ALTER TABLE Gene ADD INDEX ggs(gene_sym, SFID);ALTER TABLE Gene ADD INDEX gga(genbank_acc, SFID);ALTER TABLE Gene ADD INDEX ggn(gene_name, SFID);ALTER TABLE Gene ADD INDEX go(organism, SFID);ALTER TABLE Gene ADD INDEX gll(locuslink_id, SFID);ALTER TABLE Gene ADD INDEX gui(unigene_id, SFID);ALTER TABLE Transcript ADD INDEX tg(gene_SFID, cDNA_SFID);ALTER TABLE Transcript ADD INDEX tc(cDNA_SFID);ALTER TABLE Refseq ADD INDEX rma(mRNA_acc, locuslink_id);ALTER TABLE Refseq ADD INDEX rllid(locuslink_id);
View 2 Replies View RelatedHi,
There is a table which I regularly run a select query on.
The select query always has a fixed where clause on only three of the columns with different parameters.
This is a query that runs each time:
select * from tblData
where
PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
This are the types of indexes the table currently has:
One index for each of these three fields
i.e. index1 for PersonNo
index2 for EmployeeType
index3 for DataDate
In addition to the above, I also have created a covering index as follows
index4 for PersonNo,EmployeeType,DataDate
Is what I have enough for indexes on this table please?
Is there anything else I have to do on indexing this table?
Thanks
I need some help with MS Indexing Services, and there doesn't seem to be much support for it on the web. Do you know of any good forums or sites?I'm using MS Indexing Services to power the search feature on my site. Should I be using something else like Sharepoint?
View 4 Replies View RelatedIf you put an index on an integer type column named 'test_column' in a table that had 1,000,000,000 rows in it, and you said select top 50 * from test_table WHERE test_column = 1 since 'test_column' has an index, that would perform extremly fast wouldn't it? Cheers
View 5 Replies View RelatedHi All,
I am working on SQL Server 7.0. Every weekend we go for reindexing of some tables. I want to know if it is possible to run the re-indexing of tables in parallel so that I can save time.
Our database is of size 80GB and one table is around 22GB. Rebuilding of index on this table takes a lot of time and we are unable to index the other tables.
Any solutions/suggestions are mostly appreciated.
Regards,
Mitra
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2....
But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.
I have a database with no index on any table,
I have to pull out records from them, process them and insert into a
set of table in another database. There is no one to one mapping. What
I have been doing is get the data into cursor and manipulate row by row
and insert to target tables. This is very slow even for few thousand
records and we have to do it for few hundred thousands.
The process takes long time to run (hours for 20000 records). I created indexes to speed up the
operation, but with index my process just hangs, I have put some print
statements within the transaction loop that also does not appear on
ISQL, it appears only after I kill the process.
It's all confusing to me, index is not helping at all. I checked the
query plan for queries after creating index, it displays fine but the
stored procedure just stops.
Hi,
I’m using SQL Server 2000. I have a table called Contacts and I would like to be able to have the UserID as an indexed column and to ignore duplicates. I set up the following properties within my SQL Server database table:
Table Name: Contacts
Selected Index: IX_UserID
Column Name: UserID
Order: Ascending
Create Unique
Index
Ignore Duplicate Key
Every time I try to enter duplicates for the UserID column; I get an error that says, “Cannot enter duplicate key row in object ‘Contacts’. Can anyone explain this? Is it possible to create an index column with duplicate data?
Thanks,
Denise
Folks,
I am not really sure how the whole indexing side of MS SQL works (I'm a noob), so my question has 2 parts:
1) Does SQL store every Index in memory?
2) If so, can I perform a SELECT on a table's index(s) without hitting the disk?
For example: I have a table with a column called "Id" which is of type uniqueidentifier. I want to select all of the "Id"s in the table without accessing the server's hard drive (get info from memory).
Thanks in advance!
-agartee
I'm looking for some help on how i should index this table.
current table has about 500k records in it.
the fields in the table are:
member_num (varchar(12), not null)
first_name (varchar(20), null)
last_name (varchar(20), null)
ssn (varchar(50), null)
address1 (nvarchar(200), null)
address2 (nvarchar(200), null)
city (nvarchar(200), null)
state (nvarchar(200), null)
zip (nvarchar(100), null)
phone1 (nvarchar(50), null)
all of the fields are searchable through an asp.net webform.
my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.
Sorry its been a while since I was taught about indexes, Can I place indexes on both FK fields of a Associative table?and what is the recommended number of rows to place an index on a table for SQL server (if different from other DBMS)?and also whats a clustered index?
View 2 Replies View Related