| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2009-12-30 : 05:52:06
|
| Hallo, I have a table called tblopat, this table is built with 110,000,000 records and the data is static and the table will remain the way it is. On the other hand, the table is indexed with the first column as the primary key. The problem am facing is that a simple query as shown below will take a very very long time, where o_pat_eid = 9173 is ONLY one line, why is this happening?Select * from dbo.tblopatwhere o_pat_eid = 9173 Thanks |
|
|
Sachin.Nand
2937 Posts |
Posted - 2009-12-30 : 06:22:48
|
| Which columns are there in that * ?How long it takes if u query like this?Select o_pat_eid from dbo.tblopatwhere o_pat_eid = 9173PBUH |
 |
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-30 : 06:59:08
|
| I have 16 columns , and when changed the query to Select o_pat_eid from dbo.tblopatwhere o_pat_eid = 9173Its 5 minutes and still running.......Any ideas, thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-30 : 07:25:34
|
Is o_pat_eid the indexed column?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-30 : 07:32:52
|
| No the indexed column is o_pat_uid, this column is the one set as primary key and is unique for every record. Note: this column is always incremented when a new record was created and hence set as primary key. However when I run a select query based on o_pat_uid the result is instant for example select o_pat_uid from tblopatwhere o_pat_uid = 22252Any ideas??Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-30 : 07:35:06
|
If you are searching your table by o_pat_eid you should index that column - too.You will see a performance boost!
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-30 : 07:36:08
|
| how can i do that? Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-30 : 07:40:50
|
CREATE NONCLUSTERED INDEX IX_o_pat_eid ON dbo.tblopat(o_pat_eid)It will take a while...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-30 : 09:04:08
|
| Is it acceptable to have o_pat_uid as clustered and o_pat_eid as unclustered and will it improve the performance? Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-30 : 09:06:34
|
yes 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-30 : 09:08:27
|
| ok, Thanks ... Can I also uncluster another 2 columns which are used for data extraction? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-30 : 09:09:40
|
you can build up to 249 indexes on a table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-30 : 09:15:59
|
| Ok.. Thanks So the next index can be something like; CREATE NONCLUSTERED INDEX IX_o_pat_ageON dbo.tblopat(o_pat_age)This means, is a clumn is unclustered and I write a select query to extract data based on that column, it will be quicker.. I initially thought that all other columns will be regarded as Unclustered by default, but I was wrong.. Hope this procedure resolves my problem. Many thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-30 : 09:21:25
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Select query takes Hours