Indexing And Queries

Dec 1, 2005

Hi everybody,

After days reading stuff about indexing, extents, pages, 8KB, 64 KB,
IGNORE_DUP_KEY, CREATE INDEX, bla bla, lalalala, lllllll, grrrrrrr and
testing with different kinds of queries, indexes, situations i'm
getting confused, irritated, etc.

SITUATION
I have the following situation. We have a fact table with 1,8 million
records (Testsituation) and i'm inserting 100000 records and i want to
make it faster. Records can only be inserted when it's different from
the one in the Fact table. Currently we don't have any index on the
table. So i thought that could be quicker when i build some indexes. So
i started experimenting, lalala, and some further more and more...

The facttable has Foreign keys and measures. The foreign keys are
referenced with the primary keys in the dimensions. Also i have a field
called Source_key which indicates an unique value (This could be an
integer, but also a varchar). In this case its a varchar. Also i have
an Checksum field. An example of the query is like this (based on the
northwind database):

INSERT INTO DYN.dbo.SAW_Fact_Order_Detail
SELECT 20051130, 62667,
Customer_Dim_Key =
ISNULL(DYN.dbo.SAW_B_LU_Customer.Customer_Dim_Key, 0),
Product_Dim_Key = ISNULL(DYN.dbo.SAW_B_LU_Product.Product_Dim_Key,0) ,
FK_Order_Date,
FK_Required_Date,
FK_Shipped_Date,
Shipped_Flag,
Order_Duration_Open_Days,
Order_Required_Exceed_Days,
Order_Detail_Unit_Price,
Order_Detail_Quantity,
Order_Detail_Discount,
ExtendedPrice,
Order_Number,
Order_Detail_Number,
Order_Detail_Count,
binary_checksum(
ISNULL(DYN.dbo.SAW_B_LU_Customer.Customer_Dim_Key, 0),
ISNULL(DYN.dbo.SAW_B_LU_Product.Product_Dim_Key,0) ,
DYN.dbo.TR_Fact_Order_Detail_V.FK_Order_Date,
DYN.dbo.TR_Fact_Order_Detail_V.FK_Required_Date,
DYN.dbo.TR_Fact_Order_Detail_V.FK_Shipped_Date,
DYN.dbo.TR_Fact_Order_Detail_V.Shipped_Flag,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Duration_Open _Days,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Required_Exce ed_Days,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Unit_P rice,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Quanti ty,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Discou nt,
DYN.dbo.TR_Fact_Order_Detail_V.ExtendedPrice,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Number,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Number ,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Count) ,
14,
'N',
getdate(),
'1/1/2049'
FROM DYN.dbo.TR_Fact_Order_Detail_V
LEFT OUTER JOIN DYN.dbo.SAW_B_LU_Customer ON
DYN.dbo.TR_Fact_Order_Detail_V.Customer_Code =
DYN.dbo.SAW_B_LU_Customer.Customer_Code
LEFT OUTER JOIN DYN.dbo.SAW_B_LU_Product ON
DYN.dbo.TR_Fact_Order_Detail_V.Product_Code =
DYN.dbo.SAW_B_LU_Product.Product_Code
WHERE NOT EXISTS
(Select *
From DYN.dbo.SAW_Fact_Order_Detail
Where Checksum_Field = binary_checksum(
ISNULL(DYN.dbo.SAW_B_LU_Customer.Customer_Dim_Key, 0),
ISNULL(DYN.dbo.SAW_B_LU_Product.Product_Dim_Key,0) ,
DYN.dbo.TR_Fact_Order_Detail_V.FK_Order_Date,
DYN.dbo.TR_Fact_Order_Detail_V.FK_Required_Date,
DYN.dbo.TR_Fact_Order_Detail_V.FK_Shipped_Date,
DYN.dbo.TR_Fact_Order_Detail_V.Shipped_Flag,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Duration_Open _Days,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Required_Exce ed_Days,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Unit_P rice,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Quanti ty,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Discou nt,
DYN.dbo.TR_Fact_Order_Detail_V.ExtendedPrice,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Number,
DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Number ,
AND DYN.dbo.TR_Fact_Order_Detail_V.Order_Number =
DYN.dbo.SAW_Fact_Order_Detail.Order_Number
AND DYN.dbo.TR_Fact_Order_Detail_V.Order_Detail_Number =
DYN.dbo.SAW_Fact_Order_Detail.Order_Detail_Number
AND Expired_Date = '1/1/2049')


EXPERIMENTS
So i focused on the NOT Exists clause. My thought was that when an
index is created on the fields in WHERE clause of the SELECT statement
in the NOT EXISTS part it would be quicker. Because SQL Server should
be quicker decisioning whether a record existed or not. So far theory.
So i started experimenting.

I No Index.
It took about 118 seconds.

II. I created an referencetable (took some time but not relevant here)
so the insert table and the comparetable were not the same anymore. The
fields in the reference table are Checksumfield, Dossier_Source_Code
(in query example Order_Number and Order_Detail_Number) and
expired_date. So the not exists clause rebuild to reference this newly
created table and now it took about 85 seconds.

III. The part i don't understand is this. So the prior step was a
perfomance gain so i decided to build indexes on the reference table. I
tried al types of different indexes:
* index on checksum (clustered), source_key (Non clustered, unique) and
on expired date (non clustered) --> 99 seconds
* Index on source_key (clustered, unique), checksum_field (non
clustered) and on expired date (non clustered) --> 91 seconds
* the Source key (in the example Order_Number and Order_Detail_Number)
was unique so i decided to build a checksum on these fields and build
an index on the checksum and of course on the Source_key and
expired_date. This took about 101 seconds. What?

So as you can see it took only longer when i build a index. So why? And
has someone any clues to make the query faster?


Thanx ,
Hennie

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Indexing Tips For MIN And MAX In Queries

Jun 6, 2014

Are there any best practices for indexing to support queries with MIN() and MAX() in them? what if MIN() and MAX() are partitioned? Super bonus question: what if MIN() and MAX() are not only partitioned, but are called on a field in a derived table, and one of the partitioning elements comes from a table that's being joined in the derived table?

I experimented with inserting the derived table into a temp table, putting a POC index on that, and querying out, but that actually took longer.

View 9 Replies View Related

SQL Server, Full Text Indexing, And ASP.NET Parameterized Queries

Aug 12, 2006

I've been driving myself nuts trying to get a sensible product search
going. The existing live site search is just a LIKE %searchterm% on
the Title field in our Products table. Fast, but not great ;) Talks
between IT and Marketing have resulted in this being the desired
results and order:

Exact Title match
Substring Title match
Substring Keywords match
Substring Description match

OK, I can easily do this with UNION queries using LIKE (and a virtual
"weight" column in each query), but the query takes too long. So I'm
trying out SQL Server full text indexing, in an attempt to get the
speed up (and the natural language stuff is just plain cool).

Where I'm running into problems is doing a FULLTEXT match on
Description. It seems that to do a phrase match (e.g. "new york"
should match only Descriptions where the phrase "new york" occurs) I
need to enclose the search term in quotation marks in the query (or
maybe single AND double quotes).

But using parameters in ASP.NET (which I'm supposed to do to avoid SQL
injection attacks, yes?) I don't really have full control of the
quoting - ASP.NET and/or SQL Server automagically quotes strings for
me before passing them into the query. I think.

For example, this doesn't find any description matches:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)

set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"

SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"@theSearchTerm"'))

ORDER BY theWeight DESC, m.ShortName
==========================================

But this one (where I put in the actual string instead of using the
parameter) *does* get the desired results, including matches in the
Description:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)

set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"

SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"new york"'))

ORDER BY theWeight DESC, m.ShortName
==========================================

Trying various permutations of quotes around the parameter gives me
either syntax errors or undesirable results.


Has anybody tried this sort of thing? How did you do it?

Thanks,

Greg Holmes

View 4 Replies View Related

FLAT File Indexing Vs RDBMS Indexing

Sep 22, 2006

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?

View 14 Replies View Related

Online Re-indexing Vs Offline Re-indexing

Sep 10, 2007

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

View 5 Replies View Related

Indexing

Jul 18, 2005

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.

View 1 Replies View Related

Re-Indexing

Jun 13, 2002

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!

View 3 Replies View Related

Indexing

Mar 13, 2008

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

View 14 Replies View Related

SQL Indexing

Apr 26, 2006

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

View 5 Replies View Related

Sql Indexing

Nov 10, 2006

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."

View 5 Replies View Related

Indexing Help

May 25, 2007

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.

View 16 Replies View Related

Indexing

Nov 19, 2007

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"

View 2 Replies View Related

Indexing

Jul 20, 2005

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 Related

Indexing

Mar 21, 2007

Hi,
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

View 3 Replies View Related

Where Can I Go For Help With MS Indexing Services?

Mar 26, 2007

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 Related

Indexing - Is It Powerful?

Jan 2, 2008

If 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 Related

Re-indexing In Parallel

Apr 25, 2001

Hi 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

View 2 Replies View Related

SQL Indexing - ***BASIC****

Sep 15, 2005

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.

View 3 Replies View Related

Indexing Is Not Helping

Mar 25, 1999

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.

View 2 Replies View Related

Indexing And Duplicates

Aug 5, 2002

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

View 4 Replies View Related

SQL Indexing Question

Aug 26, 2005

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

View 1 Replies View Related

Indexing Suggestions

Jun 29, 2006

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.

View 4 Replies View Related

Indexing Questions

Nov 6, 2007

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

Searching And Indexing

Apr 13, 2008

i have to make the following but i have no clue any help will be appreciated

i have to search through three tables based on user preferences.

the tablkes are author name, book name and topics.( i have created ttables and their relations)

now i want to the user to select the option from the drop down menu. The problem is how do i ascertain(dynamically) which table to search based on the action selected by the user. Thanks

View 11 Replies View Related

Indexing On Databases

Apr 29, 2008

Hi,

I am new to this forum, could any one help me in scripting the INDEXING on all the databases which runs weekly basis on setting up through the JOB.

I thank everyone who help in this urgent request.

Regards

View 6 Replies View Related

Indexing Help Tools

Jun 12, 2008

Dear All,
in my current databases, indexing is very poor. same columns are having clustered index and non clustered indexes. is there any tool to help me out?
i'm thinking in this way...please correct me if i'm thinking wrong...

1) i'm planning to drop all the indexes first.
2) i'm planning to create clustered index on ID column.
3) i'm planning to create non clustered index on some columns which are using where conditions.(many procedures and functions, as well as report queries).
4)planning to run the index rebuild script everday at non-peak time
5)planning to run the index defragmentation script every week at non-peak time
6) planning to run shrink database command every week.

please correct me and add flavour with your great experience.

thank you very much



Arnav
Even you learn 1%, Learn it with 100% confidence.

View 7 Replies View Related

Indexing A Table..

Apr 18, 2006

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

View 5 Replies View Related

Indexing Issues

Apr 15, 2007

Okay, so first off, here is a sample query i'm using:


SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o WHERE 1 = 1

and per1_age>=20
and wealth_rating>=1
and hm_purprice>=100 --6 sec / 3 sec
AND oo_mtg_amnt >= 100
and est_inc >= 'B'
and per1_ms='M'
and hm_year_build>='1905'
and oo_mtg_lender_name<>' '
and oo_mtg_rate_t in ('f','v')
and oo_mtg_loan_t in ('c','f')
and hm_purdate>='20000101'
and child_pres='y'
and zip in (85302,85029)
and state_abbrv in ('az')


and rtrim(city)+' '+state_abbrv in ('glendale az','phoenix az')
and rtrim(county_name)+' '+state_abbrv in ('maricopa az')
and substring(phone,1,3) in ('602','623')


group by o.state_abbrv ORDER BY o.state_abbrv


i'm trying to fine tune the database to come back with quries in less then 30 seconds. EVERY query ran will be a count.

i've managed to fine tune it to the point where anything above the rtrim(city) comes back in about 3-7 seconds. my problem is everything below that. i cant seem to get a query to respond fast enough, any recommendations? i've tried pluging the whole query into the index tuning wizard and it gives me nothing.

here is the database layout:


CREATE TABLE [dbo].[mortgage] (
[fips_state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state_abbrv] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip_four] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_point] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[car_rte] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city_abbrv] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_house_num] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_pre_dir] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_st_name] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_st_suff] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_post_dir] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_unit_des] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr_unit_desnum] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fips_cnty] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[county_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[census_tract] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[census_block] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lattitude] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[longitude] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fips_ispsa] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wealth_rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[time_zone] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[homeowner] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[est_inc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_fname] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_mi] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_lname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_title] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_dob] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_ageconf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_age] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_msconf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per1_ms] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_fname] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_mi] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_lname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_title] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_dob] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_ageconf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[per2_age] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_pres] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_0_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_0_3_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_4_6] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_4_6_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_7_9] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_7_9_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_10_12] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_10_12_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_13_18] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_13_18_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[religious_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[political_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[health_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[general_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hm_purprice] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hm_purdate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hm_year_build] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[donate_env] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[char_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pres_cc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pres_perm_cc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_mtg_amnt] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_mtg_lender_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_mtg_rate] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_mtg_rate_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_mtg_loan_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dnc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_refi_deed_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_refi_amnt] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_refi_lender_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_refi_rate_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_refi_month_term] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oo_refi_loan_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE INDEX [mortgage1] ON [dbo].[mortgage]([oo_mtg_rate_t], [state_abbrv], [wealth_rating], [est_inc], [per1_age], [per1_ms], [hm_purprice], [hm_purdate], [hm_year_build], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_loan_t]) ON [PRIMARY]
GO

CREATE INDEX [mortgage11] ON [dbo].[mortgage]([oo_mtg_rate_t], [state_abbrv], [zip], [wealth_rating], [phone], [est_inc], [per1_age], [per1_ms], [hm_purprice], [hm_purdate], [hm_year_build], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_loan_t]) ON [PRIMARY]
GO

CREATE INDEX [mortgage2] ON [dbo].[mortgage]([oo_mtg_rate_t], [state_abbrv], [wealth_rating], [phone], [est_inc], [per1_age], [per1_ms], [child_pres], [hm_purprice], [hm_purdate], [hm_year_build], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_loan_t]) ON [PRIMARY]
GO

CREATE INDEX [mortgage4] ON [dbo].[mortgage]([zip]) ON [PRIMARY]
GO


i assume thie issue is the substring.
any assistance would be GREAT!

View 16 Replies View Related

Indexing Question

Apr 18, 2007

Okay, so I am writing a program that takes any combination of about 30 parameter passed via .ASPX and Visual Basic Code. my question is this, do have to create an index for each possible combination of parameters in order to get the query to come back REALLY fast?

or would it be maybe a better method to have the program pass Every parameter even if it would be selecting all the data and just setting up a few indexes?

What say you O' SQL Gods/Goddesses

View 5 Replies View Related

Re-Indexing Jobs

May 2, 2007

Hi folks,
I have setup a re-indexing job on a sql2005 server.
It runs great. But...
in the morning the first Transaction log backup equals almost the size of the entire database. Am I missing something here.
I am looking at a similar job on SQL 7 and the transaction logs are not changing much from normal.

Regards
Paresh Motiwala
Boston, USA

View 3 Replies View Related

Indexing Suggestions

Jul 20, 2007

I have a set of tables with about the same structure

dataID, recordID, 15 other columns

dataID is unique but is never referenced in queries

recordID is one of the most referenced columns but only has a cardinality of about 30%

The current structure has a clustered PK on (dataID,recordID)

Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance any

After staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.

create a non-clustered PK on dataID
create a non-unique clustered index on recordID

Let me know if any other information is needed. Thanks

View 5 Replies View Related

Dynamic Indexing

Jul 24, 2007

How will i dynamically generate number for my selcted rows

eg: output

coloumn 1 Coloumn 2 Column 3
Priority 1 Speciality Angography
Priority 2 Country India
Prority 3 xxxx yyyyy

I want the cloumn 1 to be genrated dynamically and it should go
on like 1 2 3 4

View 2 Replies View Related

Indexing Views

Mar 17, 2008

Hi,

I've got some tables which are indexed on primary key and a couple of search columns.

I've also got some views based on these tables, some are quite complex. Do I index the views on the same search columns or do they automatically index themselves based on the tables they reference?

Thanks

View 2 Replies View Related







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