i****a
发帖数: 36252 | 1 need to query between TableA and TableB. and it's taking too long
because the columns on lookup do no have indexes
TableA.CustNum data can appear in any or all of the following columns in
TableB
TableB.CustNum1
TableB.CustNum2
TableB.CustNum3
about 240M rows in TableB, on index on the CustNum fields, no foreign
keys
I am thinking creating a separate table with only 3 CustNum1/2/3
columns, with indexes defined, then insert 3 columns of data into it.
but it takes 20 hours to insert with indexes de |
j*****n
发帖数: 1781 | 2 you can still partition your table by using the old fashion. say 10 tables
that each holds 24M records and a view with UNION ALL.
would your limited log can handle 24M each time instead of 240M? |
i****a
发帖数: 36252 | 3 that's an idea. will try this weekend. thx
tables
【在 j*****n 的大作中提到】
: you can still partition your table by using the old fashion. say 10 tables
: that each holds 24M records and a view with UNION ALL.
: would your limited log can handle 24M each time instead of 240M?
|
B*********L
发帖数: 700 | 4 Are you able to change data type for CustNum1/2/3 to reduce the size of your
index. |
i****a
发帖数: 36252 | 5 no, they are INT. I was actually able to create index one-by-one by
issuing SQL commands
I found out doing this via managment studio interface will create all 3
indexes in one shoot in a single transaction... that's why it was taking
forever and filling up the log file
your
【在 B*********L 的大作中提到】
: Are you able to change data type for CustNum1/2/3 to reduce the size of your
: index.
|
a9
发帖数: 21638 | 6 从来不敢用managment studio创建index
【在 i****a 的大作中提到】
: no, they are INT. I was actually able to create index one-by-one by
: issuing SQL commands
: I found out doing this via managment studio interface will create all 3
: indexes in one shoot in a single transaction... that's why it was taking
: forever and filling up the log file
:
: your
|
g***l
发帖数: 18555 | 7 INDEX在三个COLUMN上？这样对查询帮助不大吧。为什么不能一个COLUMN上一个INDEX呢
，你有PK么或者CLUSTERED INDEX么，如果没有的话，NONCLUSTED INDEX也不快。
240M太多了，应该SPLIT成多个TABLE,上面套个VIEW，用VIEW给用户查询,多个TABLE的话，一个一个加INDEX快多了 |
