Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aschaeffer
Starting Member
2 Posts |
 Posted - 2010-04-21 : 16:05:50
|
| I have a table that currently contains a varchar(50) column that is used to categorize the record.tbl_transaction( id PRIMARY, transaction_type varchar(20), ...)This table is fairly large and is getting bigger (300,000 rows currently). Many of my queries use the transaction_type field to include/exclude records. For exampleselect * from tbl_transactionwhere transaction_type in (select transaction_type from tbl_transaction_group where group = 'foobar')I have noticed some performance issues in the more complicated queries. I have considered modifying this table to have transaction_type_id (INT) instead of transaction_type. transaction_type_id would have a foreign key to a tbl_transaction_type table.Would this change increase the performance of my queries that do a lot of the varchar comparisons?Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aschaeffer
Starting Member
2 Posts |
Posted - 2010-04-21 : 16:34:35
|
| Thanks for your prompt response.We have a Non_unique, Non-Clustered index on transaction_type along with other columns that are very often used to filter the result sets further.The group column in tbl_transaction_group is indexed but not the transaction_type column.I am going to modify my queries to use joins and index the transaction_type column in tbl_transaction_group. The switch to using ids instead of varchar in tbl_transaction is a slightly bigger change that we will work on in the next iteration.Thanks for your help! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
varchar compare vs int compare