Quicker Way To Create Indexes
Jul 18, 2006
Hi,
I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.
Any ideas?
View 5 Replies
ADVERTISEMENT
Jul 1, 2014
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
View 5 Replies
View Related
Mar 13, 2000
When creating the indexes on the table, SQL 7.0 also created some type of system indexes ( ex. _WA_Sys_curr_int_rate_2C88998B nonclustered, statistics, auto create located on PRIMARY, etc. ). I want to know how can I clear up these system indexes.
View 1 Replies
View Related
Mar 3, 2006
Slayer writes "Probably a really lame question but in the absence of our DBA I have been asked to create a couple of indexes on a table. No problem with that, just need to know if this is a safe operation whilst users are using the database.
Thanks for your help"
View 2 Replies
View Related
Oct 29, 1999
Hello,
A few months ago, the company I work for switched to SQL 7.0 from 6.5. All is running fine but recently I noticed that we have a ton of indexes on each table that start like this: _WA_SYS_....0B679CE2 (here are some specific examples: _WA_SYS_PRODUCT_LINE_0B679CE2, _WA_SYS_RMA_DISPOSITION_0B679CE2)
Did SQL 7.0 do this? If so why? What is it trying to do? Why doesn't it use current indexes (clustered and non-clustered)? Why don't they get Fragmented like User defined indexes? I have a lot of questions associated with this if someone can explain it.
Tech Net, MSDN, BOL and other manual resources don't mention anything about it. At least I haven't found anything on it and I have spent some time looking.
Please help if you can.
Troy
View 2 Replies
View Related
May 24, 2014
1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.
2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.
View 9 Replies
View Related
Jun 15, 2008
I found out how to create clustered index using TSQL, but how can you create clustered index and manage indexes using SQL Server Management Studio?
Thanks
Goldmember
View 2 Replies
View Related
Jun 17, 2006
First, I'd like some help stablishing if it's a good idea or not what I'm thinking, and second is what would be the easyist way to achive the result.
Background. We have a multicompany ERP system (200GB, 200+ tables). All data tables have a companyid, counter1id, counter2id, fields. Each table has a primary clusterd index on these fields in that order. And also has multiple other indexes that usually include companyid filed first. ( Note we have 10 companies, ids are 1..10, companies 2 and 5 are the most active, inserts, selects ). MY GUESS IS THAT THIS IS VERY BAD HAVING COMPANYID FIRST. AM I CORRECT? LEADS TO AL LOT OF FRAGMENTATION? VERY BAD TO FIND DATA ON THE INDEXES?
I want to re-create all my indexes and have them counter2id, companyid, counter2id. ( counter1id is a counter for each company, and counter2id is a global counter like identity ) GOOD OR BAD IDEA?
Since I have hundreds of indexes what is the correct procedure? I was thinking on scripting the database, then dropping all indexes, then recreate clustered indexes, then recreate other indexes. Do I have to take care of anything else foreing relationships, etc.
View 6 Replies
View Related
Sep 1, 2000
Hi everyone,
I know that statistics called _WA_... are created on tables when auto create statistics is set on a database. Is this an indication that queries against the table would perform better if indexes were created on the columns in question? (The tables I'm interested in optimising are used equally for transactional querying and reporting)
Thanks for any replies!
Les
View 1 Replies
View Related
Apr 19, 2006
I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
View 3 Replies
View Related
May 19, 2004
is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanx
View 5 Replies
View Related
Mar 12, 2007
With help of others on this group, I've been learning and researchingabout indexes; an area I neglected.I see I can specify which filegroup I wish to create an index, whichthe default is Primary.I have more than one drive in my SQL server where I put data and logson their own logical raid groups.My databases are SIMPLE, so they dont use much, if any logs (none as Iunderstand).I was thinking of adding an additional file to my database and use itsolely for the indexes.Any thoughts?SQL Server 2005 Enterprise x64 SP28 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.Thanks,Rob
View 2 Replies
View Related
May 22, 2007
I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
View 4 Replies
View Related
Oct 30, 2007
Hi
I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:
SELECT TB1.product, TB2.product
FROM TB1
JOIN (select distinct product
from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse
ON TB2.product LIKE '%' + TB1.product+'%'
which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?
Thanks in advance...
View 9 Replies
View Related
May 29, 2008
Instead of selecting records like this:
Select website from wce_contact where uniqueid like
'O,U4Ba-l2Nmc' or uniqueid like
'O90kja8,aofc' or uniqueid like
'OIiZnaAHj1fc' or uniqueid like
'oho9mc2HA4fc' or uniqueid like
'ogJrMa.YV9mc' or uniqueid like
'PNocYaQtA6fc' or uniqueid like
'NLWTfcUSbXec' or uniqueid like
'O_eHjaNhPtfc' or uniqueid like
'OK(Qkau,H(fc' or uniqueid like
'nYcUfcCQbXec' or uniqueid like
'O-R5oa2C-2fc' or uniqueid like
'O-R5oa2C-2fc' or uniqueid like
'nPYhfat5eJec'
Is there anyway i can select these contacts without having to copy and paste 'or uniqueid like' numerous times?
Thanks!
View 2 Replies
View Related
Apr 9, 2006
I have a small archiving SSIS pacakge that I use to archive off old orders. I was OLEDB and found that I would be deleting about 1 order (plus all its relational records) a second. Try to find some speed I tried the ADO.net connetion and turn pooling on with a min of 10. Now I am getting about 100 order records deleted a second.
Has any one else found that that ADO.net connection is faster than the OLEDB (I would have thought it would be the other way around).
The
Control flow items I am using are the Execute SQL Task and the queries are like this
Insert into arcprod.wh1.pickdetail
Select * from wh1.pickdetail where status='9' and orderkey = @Pram1 and
pickdetailkey not in (select pickdetailkey from arcprod.wh1.pickdetail)
...
delete from wh1.pickdetail where orderkey = @Pram1
etc
View 1 Replies
View Related
Jul 20, 2005
Hi,How Can I create a case sensitive unique index so that A1 and a1 are treatedas different ?I dont mind if I have to make a global DB change.Steve
View 1 Replies
View Related
May 26, 2015
I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.
View 3 Replies
View Related
Mar 12, 2008
Hi all,
I can't find the answer to the following question.
Why is Visual Studio quicker then the ReportServer.
I've got a report with more then 20 parameters and several are muli-select.
When I'm opening the report in Visual Studio it appears with in one or two seconds whereas I open it on a reportserver it takes 8 to 15 seconds to open or to refresh. After that I can enter the report parameters and then the report is running in approximately the same time.
I know that the refresh is by default, so I can't change that, but the users have problems with wating 15 seconds between entering the parameter values.
The development machine is 2,8 GHz P4, the reportserver is a 4 proc 16 GB databaseserver.
The development machine is just being used editing the rdl in Visual Studio.The databases are kept on the database server.The network speed is 100mbps.
Is the only alternative to build my ASPX and reportviewer component inside of that APSX.
Thanks,
Eric
View 10 Replies
View Related
Jul 17, 2003
How to script all the PK/FK/constrainnts/indexes with create and drop statements?
As you know, we can't script 'drop statements' for primary keys etc..
Any help in giving me a script which does all the above is greatley appreciated..
Thanks,
Di.
View 1 Replies
View Related
Aug 29, 2013
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
View 4 Replies
View Related
Nov 15, 2007
I sure would like a quicker way to get to a script's code, then:
1. double-click
2. ctrl+tab to switch to script item in list
3. click on "Design Script" button.
I'm constantly editing scripts and I would LOVE to be able to right-click, "Design Script", bypassing the silly dialog. I've tried creating a macro with no success. While recording, when I click on "Design Script" from the dialog, it actually brings up the macro script in the editor. This task is frequent enough, that it deserves its own context menu entry.
Any ideas?
View 4 Replies
View Related
Apr 21, 2008
Hi Everyone,
Am a third year student doing work placement.
Could anyone please give me clues on how to go about extracting data from a LDAP and then into an SQL database?
1 A defined subset of data is to be extracted from GDS on a nightly basis,
2 Then imported into a SQL database for quick & easy retrieval.
3 A web interface is required to present data retrieved from the SQL database.
I will appreciate every assistance.
Regards
Lidiolo
View 6 Replies
View Related
Sep 18, 2007
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Mar 3, 2014
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
View 3 Replies
View Related
Jul 20, 2005
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies
View Related
Jul 20, 2005
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies
View Related
Mar 26, 2008
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Any help would be appreciated. Thanks!
View 9 Replies
View Related
Jul 31, 2002
Sir,
1. How do I call the indexes created in SQL server for a table from Front end VB programming ?
2. How can I use SEEK command with ADODB control ?
Please give me some tips & samples
Sundar Raman
View 1 Replies
View Related
