| Author |
Topic |
|
ruan4u
Posting Yak Master
132 Posts |
 Posted - 2004-12-31 : 09:00:27
|
| I have a table with 20 million rows. i only have one primary filegroup. There will only be one query firing on this table to retreive data.Inspite of indexes its slow.They query analyzer takes 1 min to get 20000 records, but VB times out.Please help |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-31 : 09:14:03
|
| you can investigate changing the command/connection timeout parameter upwards from 30 9secs)....to a more useful number...or setting it to zero ...for 'never time out'otherwise you may need to go down the route of "raid drives" or "data partitioning"search here for instances/discussions of all 3 options. |
 |
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-31 : 09:45:31
|
| How can i partition the data? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-31 : 11:31:52
|
| one ways is via views....based on multiple underlying tables....as i said..."search here!!"there's far more information already typed that i can provide or am competant to provide....I just have read (and forgotten) the individual articles. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-02 : 18:26:51
|
| What is the query?And the table DDL?rockmoose |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-02 : 22:31:08
|
| Yeah, I'd hold off on partitioning or changing anything until we see the query and the DDL of the table ... make sure you show us all indexes on the table as well.- Jeff |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-02 : 22:36:22
|
| Have a look at the query plan. Add ijndexes or hints to remove anything that is scanning this table.The percentage costs of the parts of the query should show you where the problem is.Is the query in a stored proc? If not then put it in one - it will be more flexible and easier to optimise.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-01-03 : 08:51:47
|
| The table has 10 columns all chars.Only one column is of len 60 , but the key column used in the where clause is char(10).I did select col001 ... cocol10 from mytable where col002 in (select distinct id from <REF_table> where country='USA')REF_table has 50K records. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-03 : 08:58:33
|
| How many recs in (select distinct id from <REF_table> where country='USA') ?try select distinct id into #a from <REF_table> where country='USA'create index ix on #a (id) -- shouldn't be neededselect count(*) from mytable join #aon mytable.col002 = #a.idselect col001 ... cocol10 from mytable join #aon mytable.col002 = #a.idand check the difference in the first and second selects in the query plans.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-03 : 09:17:27
|
| is col002 indexed? Do you know how to generate the DDL for this table for us?- Jeff |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-01-03 : 09:33:34
|
| Smith,Yes . am assuming u need the table structure?The index is on col002 which is used in the where clause.But it does an index scan on my_table(69%),a table scan on ref_table, and then does a hash match(39%).Now if i select frm my_ref and concatenate all ids in a string and then call the select col001 ...col010 from mytable where col001 in (@inval),then it does an index seek |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-03 : 09:46:58
|
| But it won't return anything because it's not actually doing an in on all the values.Have you run the queries I posted?Are col002 and id the same datatype?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
20 mil records