Creating Indexes On Large Table To Increase Performance

Mar 5, 2008

Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:


CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))

CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))



INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC

INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType

FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID

WHERE RecordedCalls.ID <= '9369907'

AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))

AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)

AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')

AND RecordedCalls.ServerID NOT IN('2')

AND RecordedCalls.AgentID NOT IN('1000010000')

AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))

AND RecordedCalls.IsDeleted='false'

GROUP BY RecordedCalls.CallType

SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')

WHEN 'Arabic' THEN #tempLookups.NameA

ELSE #tempLookups.NameE

END AS CallsType FROM

#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code

DROP TABLE #tempLookups

DROP TABLE #tempTable


Thanks all,
Tayseer

Any suggestions how to create efficient indexes??!!

View 2 Replies


ADVERTISEMENT

Data Warehousing :: Will Creating Partitions On Table Increase Insert Speed

Oct 8, 2015

I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?

View 4 Replies View Related

Improving Large Table Performance

Aug 15, 2007

We have a table that is 800GB. We are planning to re-build the clustered index on this table to a different filegroup. The new filegroup and files associated with it will sit on a SAN which will have a 1.5TB allocation. Does anyone have any suggestions in regards to how many files to have associated with the filegroup to provide optimal performance? Apparently we could have 3 LUNS (500gb each), so would 1 file on each LUN provide additional performance as opposed to one file on 1 LUN?

View 1 Replies View Related

Creating Full-text Indexes On A Table In A Publication

Sep 13, 2006

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

View 12 Replies View Related

SQL 2012 :: Performance Limit On Number Of Indexes Per Table / Database

Oct 1, 2014

Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.

View 4 Replies View Related

Large Table/slow Query/ Can Performance Be Improved?

Jul 20, 2005

I am having performance issues on a SQL query in Access. My query isaccessing and joining several tables (one very large one). The tables arelinked ODBC. The client submits the query to the server, separated byseveral states. It appears the query is retrieving gigs of data from thetable and processing the joins on the client. Is there away to perform moreof the work on the server there by minimizing the amount of extraneous tabledata moving across the network and improving performance (woefully slowabout 6 hours)?

View 3 Replies View Related

Efficiently Creating Random Numbers In Very Large Table

Jan 19, 2007

Hello,

I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).

I'd like to take the top 5%, for instance, based upon a column containing random numbers.

Can anyone suggest a highly efficient method of populating a column with random numbers.

Thanks in advance.

Rod

View 10 Replies View Related

Large Log Table ....SELECT * FROM Statement....killing The Performance Of Server..Help Me Out..

Mar 12, 2008

Hi all

I have a Large log table with large size data(I month only),If I run a query like SELECT * FROM <table_name> Server will go€¦very very slow€¦.

Because of large Data system is going slow€¦..

Please some body helps me with suggestion how get good performance.

View 4 Replies View Related

Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)

Oct 4, 2015

I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.

Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based 
column b: is the source filename of the data load (varchar 256)
....

a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)

b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?

c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.

-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?

View 4 Replies View Related

Increase &#39;View&#39; Performance

Apr 1, 2001

Hi,
I created a view on two huge tables. I tried to run a simple SELECT statement on this view and it took me several hours to obtain the result. How can I improve the performance of a view? The view should make use of the indexes built in both table, am I right? Thanks.

View 1 Replies View Related

Cursor Will Increase Performance Or Not

Jun 29, 2000

"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database".

(Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this?

Srinivasan

View 3 Replies View Related

How To Increase SSIS Performance

Nov 3, 2006

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

View 2 Replies View Related

How Much Can A Stored Procedure Increase Performance ???

Sep 25, 1998

Hi,

I am writing an ASP based application that creates a dynamic querry and then
executes it and displays results. I was thinking about writing a stored procedure to increase performance. How much can the SP help me boost querry responce time ???

Thanks for your time,
Robert

View 1 Replies View Related

How To Increase Performance Of A Stored Procedure

May 28, 2008

Hi,

Can any one give me an idea how can i increase performance of the stored procedure.
In SP many temporary tables are used.

Also i need a information from any one you that is there any tool to find out the performance of a query or SP etc.

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 4 Replies View Related

SQL Server 2008 :: How To Increase Performance Of Insertion SP In While Loop

Aug 13, 2015

WHILE (@MyLoop3 > 0)
BEGIN
SELECT Top 1 @UploadId = UploadId,@FirstName = (CASE WHEN FirstName = '' THEN @Update ELSE FirstName END),
@LastName = (CASE WHEN LastName = '' THEN @Update ELSE LastName END),
@Claim = (CASE WHEN Claim = '' THEN @Update ELSE Claim END),
@Insurer = (CASE WHEN Insurer = '' THEN @Update ELSE Insurer END),
@InsurerBranch = (CASE WHEN InsurerBranch = '' THEN @Update ELSE InsurerBranch END),

[Code] .....

View 3 Replies View Related

Increase Performance In Query With Big Tables (milions Of Records)

Apr 4, 2008



Hello,

I have 3 tables (A, B, C) with milions of records (A ca 5 milions, B and C ca 10 milions).
I have created a join betwenn them

select some fields (A, B, C)
FROM
A as a
JOIN
B as B
on
a.a1 = b.a1
and
a.a2 = b.a2
JOIN
C as c
ON
b.b1 = c.b1
and
b.b2 = c.b2
Where fieldtime <= date/time

But it takes to much time: aftre 2 hours and half is still running.

Do you know how to increase the performance?

Thank

View 7 Replies View Related

Rebuilding Large Database Tables And Indexes

Jul 7, 2015

I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).

My plan is thus:

+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better

View 9 Replies View Related

Large Databases - Create Indexes For Fields That Are Used In Where Statements?

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

Performance After Indexes

Sep 7, 2007

how can we check the increase of performance after the creation of indexes

View 1 Replies View Related

Creating Indexes

Feb 26, 2001

Just a quick question, Is it better to build an Indexindicies after creating a new table or after populating the table with the data. Which is quicker? What about statistics? This table is dropped/and then recreated on a weekly basis. It is a reporting database.

Thanks.

View 1 Replies View Related

Creating Indexes

Nov 6, 2000

I ran the index tuning wizard and it suggested 4 new indexes w/ a 20% improvement to be gained..

I saved the index creations to a script file and I'm curious if I can implement them at any time or is it better to do that after most of the users are done w/ the system for the day..

Also, any other performance suggestions to help speed up queries? Thanks in advanced..

View 2 Replies View Related

Performance On Indexes With And Without Fillfactors

Mar 16, 2001

Hi all,
Data load on a database without indexes(only nonclustered indexes on primary keys) took appx. 45 minutes.
Data load on the same database after creating many indexes (with default fillfactor of 0%)to improve query execution time took appx. 45 minutes.
Data load on the same database after creating many indexes (with fillfactor of 70%)to improve load time took appx. 90 minutes.

Can someone see the reason for this or justify this behaviour please?

Thanks in advance,
Praveena

View 1 Replies View Related

Creating Primary Key And Indexes

Aug 19, 2002

Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command.
Ray also responded that this command does does not transfer Primary Key and Index information.

I have attempted to create an index by using the following statement:

create nonclustered index SchoolIndx on wrestlerstest (School)

I did not get an error, but can't tell ift the index was created. The question is, how do I find out if
there is an index for the column School? Is there a command that will tell me. I'm just sending commands
to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information
by browsing the

Thanks again in advance,

Greg Zafros

View 1 Replies View Related

Creating Indexes On View

Nov 9, 2000

Hi

I want to know if we can create indexes on Views in SQL 7.0. If yes, then how

Thanks

alsi

View 1 Replies View Related

Creating Clustered Indexes

May 6, 2008

Greetings all,

What's best practice for creating clustered indexes?! Should they be added to a table AFTER it has been populated or should the clustered index be created BEFORE?

Thanks for your advice in advance.

View 4 Replies View Related

Indexes: An Overview And Maintenance For Performance

Nov 30, 2004

Many people know the importance of creating indexes on SQL Server database tables. Indexes greatly improve the performance of a database. However, while many people create indexes on their SQL Server tables, many people don't maintain them properly to ensure queries run efficiently as possible.

Thought you may be interested in a new article.

http://www.orcsweb.com/articles/index_overview.aspx

Thank you.

View 1 Replies View Related

EAV Performance And Clustered Indexes On Views

Jan 25, 2008

OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.

Before you say "redesign", I need funding allocated for that. See my first statement.

Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?

View 10 Replies View Related

Doubt In Creating Indexes-Urgent

Jun 4, 2001

Hi,
I have a question do I have to increase the Transaction Log size to create new indexes after dropping existing one on a table which has 18108360 rows. I need to recreate 5 indexes on that table. As this table is corrupt. Let me know. The corruption was on one of the indexes of this table.
Thanks,
Cynthia

View 1 Replies View Related

SQL 2012 :: Disabling Indexes For Load Performance?

Sep 22, 2015

I'm trying to improve the loading of some tables with large amounts of data that forms part of an ETL. I was going to try removing any indexes before the inserting to speed up the process, but I had some questions on whether or not I should include the clustered index (assuming one exists).

I was originally planning on including a step to disable all indexes on the destination table using the following:

ALTER INDEX ALL ON MyTable DISABLE

Once the load had finished I'd simply rebuild all the indexes.

should I simply disable the non-clustered indexes?

View 9 Replies View Related

Question About Performance Issues W/SQL2000 With NO Indexes

Jul 23, 2005

hello,I've been assigned to do performance tuning on an SQL2000 database(around 10GB in size, several instances).So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate thelogs, database and OS(win2k).The one thing that was a bit odd to me was that I was told this placedoesn't use indexes. The company is a house builder. They are prettylarge.The IT manager isn't a programmer so she couldn't explain to me why noindexes are used. She told me the programmers just don't use indexes.Before I start investing more time on this, I'd really like to learnabout why you wouldn't want to use indexes - especially on such a largedatabase!Thanks,Oskar

View 9 Replies View Related

Large Number Of Tables And Performance

Jan 25, 2008

Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.

I would really appreciate if you can give me some advice and if you have any good links that would be great...

View 10 Replies View Related

Large Number Of Tables And Performance

Jan 25, 2008

Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.

I would really appreciate if you can give me some advice and if you have any good links that would be great...

Waleed Eissa
http://www.waleedeissa.com

View 9 Replies View Related

Performance Issues With Large Tables

Dec 5, 2007

Hi,

I have a table with over 61 million records having a clustered index on an identity column(Primary key). Simple count queries are taking minutes to execute on this table (ex: select count(1) from table1). I have checked the statistics on the primary key which displayed me the histogram having the 39th million record as the Range-hi-key. I updated the statistics on this column and tried requerying, but still it took atleast 5 minutes to give me the count of records in the table. Also, there were no users using the table when I queried. Inserts into this table were working fine. I have other tables in my database with 41 million records having no such issues. Can anyone point me to the problem areas in such scenarios?


Thanks,
Harish

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved