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 |
|
satish.gorijala
Posting Yak Master
182 Posts |
 Posted - 2009-05-25 : 05:01:33
|
| One of the column in my database table contains Zipcode data. This column contains non-unquie,non clustered index.The data in this column looks as below. This table contains nearly 30 million rowsZipcode-------94122-879094022-84539412294356-779987653I have one excel file which contains two columns. one column contains zipcode of format 94122-8790(hypen zip) and another column contains zipcode of format 94122(Normal zip). my applicaitn reads this data from excel and compare the valueswith database and process it.The problem here is if the index on column is non-clustered index, the process of zipcodes(i.e matching excel zipcode to database zipcode)of type(94122-8790) is faster compare to normal zipcode(94122) i.e taking more time to process all the zipcodes.If the index on same column changed to clustered index, then process of normal zipcode is faster than the zipcode contianing hypen.what the reason for this difference? how can i over come this differenceG. Satish |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-25 : 05:02:51
|
Don't use LIKE to match data. With LIKE you can't use hashed joins.Use SUBSTRING for faster performance.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-05-25 : 05:04:32
|
| >> if the index on column is non-clustered indexWhich column - the one with hyphen or without?Do you access any other columns in the query?==========================================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. |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-05-25 : 06:56:13
|
I think u r confused. The zipcode column in table is non-clustered index and it contains both zipcodes(with hypen and without hypen). The excel file contains different column(zipcode with hypen as one column and zipcode with out hypen as another column]. i will read data from excel and then compare with database column. I am not using like operator. Direct comparision with equal symbol(=).quote:
Originally posted by nr
>> if the index on column is non-clustered indexWhich column - the one with hyphen or without?Do you access any other columns in the query?==========================================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.
G. Satish |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-05-25 : 06:58:40
|
I am not using like any where in the query. By direct comparision(.i.e using equal sign) i am retriving data. The query contians other columns also while retriving.Example:Select col1, Col2, zipcode from table1 where zipcode = zcode(excel data).Here zcode contains either 94122-89760 or 94122quote:
Originally posted by Peso
Don't use LIKE to match data. With LIKE you can't use hashed joins.Use SUBSTRING for faster performance.
E 12°55'05.63"N 56°04'39.26"
G. Satish |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-05-25 : 06:59:09
|
| >> The zipcode column in table is non-clustered index and it contains both zipcodes(with hypen and without hypen).It will only be really useful for the first entry in the index - for the other it will index scan rather than seek.You should probably have 2 indexes - one for each column.Also include any other columns that are needed for your query.==========================================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. |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-05-25 : 07:16:52
|
You should probably have 2 indexes - one for each column.----Boss, i have only one column. zipcode col contains both zipcodes(with hypen and without hypen).You are saying crate index one for each column. you get my point exactly ?quote:
Originally posted by nr
>> The zipcode column in table is non-clustered index and it contains both zipcodes(with hypen and without hypen).It will only be really useful for the first entry in the index - for the other it will index scan rather than seek.You should probably have 2 indexes - one for each column.Also include any other columns that are needed for your query.==========================================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.
G. Satish |
|
|
|
|
|
|
Index on column may affect in searching of a thing