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 |
|
ereader
Yak Posting Veteran
50 Posts |
 Posted - 2002-12-03 : 06:10:37
|
| Hi!Can any sql guru try hands on it to optimize and reduce the I/O cost of the following queryi'll be thankful to u in advance coL1----------------------------------------------------------------------ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV------(row1)FSD-FSM-FWA-GGG-GRB-GRR-HSV-ICT-ILE-IND-JAN-JLN-LAW-LBB-LIT-LNK-LRD-LSE-MAF-MCI-MFE-MKE-MKL-MLI-MQT-MSN-MSY-OKC-OMA-OWB-PAH-PIA-RFD-RST-SAT-SAV-SBN-SDF-SGF-SHV-SJT-SPI-SPS-TOL-TRI-TUL-TVC-TXK-TYR-TYS-UIN-XNA------(row2)ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV-FSD-FSM-FWA-GGG-GRB-GRR------ ..HSV-ICT-ILE-IND-JAN-JLN-LAW-LBB-LIT-LNK-LRD-LSE-MAF-MCI-MFE-MKE-MKL-MLI-MQT-MSN-MSY-OKC-OMA-OWB-PAH-PIA-RFD-RST-SAT-SAV-SBN-SDF-SGF-SHV-SJT-SPI-SPS-TOL-TRI-TUL-TVC-TXK-TYR-TYS-UIN-XNA------- ..ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV-FSD-FSM-FWA-GGG-GRB-GRR--------------------------------------------------------------------- coL2----------------------------------------------------------------------ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV--------FSD-FSM-FWA-GGG-GRB-GRR-HSV-ICT-ILE-IND-JAN-JLN-LAW-LBB-LIT-LNK-LRD-LSE-MAF-MCI-MFE-MKE-MKL-MLI-MQT-MSN-MSY-OKC-OMA-OWB-PAH-PIA-RFD-RST-SAT-SAV-SBN-SDF-SGF-SHV-SJT-SPI-SPS-TOL-TRI-TUL-TVC-TXK-TYR-TYS-UIN-XNA--------ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV-FSD-FSM-FWA-GGG-GRB-GRR--------------------------------------------------------------------- coL3----------------------------------------------------------------------FSD-FSM-FWA-GGG-GRB-GRR-HSV-ICT-ILE-IND-JAN-JLN-LAW-LBB-LIT-LNK-LRD-LSE-MAF-MCI-MFE-MKE-MKL-MLI-MQT-MSN-MSY-OKC-OMA-OWB-PAH-PIA-RFD-RST-SAT-SAV-SBN-SDF-SGF-SHV-SJT-SPI-SPS-TOL-TRI-TUL-TVC-TXK-TYR-TYS-UIN-XNA-------ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV-FSD-FSM-FWA-GGG-GRB-GRR--------BMI-BRL-BTR-CHA-CID-IBM-CMH-CMI-CRP-DAY--------------------------------------------------------------------- coL4----------------------------------------------------------------------FSD-FSM-FWA-GGG-GRB-GRR-HSV-ICT-ILE-IND-JAN-JLN-LAW-LBB-LIT-LNK-LRD-LSE-MAF-MCI-MFE-MKE-MKL-MLI-MQT-MSN-MSY-OKC-OMA-OWB-PAH-PIA-RFD-RST-SAT-SAV-SBN-SDF-SGF-SHV-SJT-SPI-SPS-TOL-TRI-TUL-TVC-TXK-TYR-TYS-UIN-XNA--------------ABI-ACT-ALO-AMA-AUS-AZO-BMI-BRL-BTR-CHA-CID-CLL-CMH-CMI-CRP-DAY-DBQ-DEC-DLH-DSM-EVV-FSD-FSM-FWA-GGG-GRB-GRR---------------------------------------------------------------------Above is the sample data from the table containing 200000 rowsI want to query the tbl like select * from tb1where(((((col1 like '%AUS%') or (col1 like '%LAW%') or (col1 like '%ALO%') or (col1 like '%OMA%') or (col1 like '%DLH%'))or ((col2 like '%LCY%') or (col2 like '%LGW%') or (col2 like '%LHR%') or (col2 like '%LTN%') or (col2 like '%STN%'))) /*it might be the case where we could have the same value in col2! so we have to check*/and(((col3 like '%LBB%') or (col3 like '%DAY%') or (col3 like '%IBM%')) or ((col4 like '%LBB%') or (col4 like '%DAY%') or (col4 like '%IBM%')))) or ((((col3 like '%AUS%') or (col3 like '%LAW%') or (col3 like '%ALO%') or (col3 like '%OMA%') or (col3 like '%DLH%'))or ((col4 like '%AUS%') or (col4 like '%LAW%') or (col4 like '%ALO%') or (col4 like '%OMA%') or (col4 like '%DLH%'))) /*it might be the case where we could have the same value in col4! so we have to check*/and (((col1 like '%LBB%') or (col1 like '%DAY%') or (col1 like '%IBM%'))or ((col2 like '%LBB%') or (col2 like '%DAY%') or (col2 like '%IBM%')))i want to check a pattern in 4 steps1) First i'll check the pattern in the col1 or in col2 2) Secondly, first result(and) either in col3 or col4 3) or again i'll check the same pattern in col3 or col44) last, 3rd result (and) either in col1 and col2 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
A Simple Query