A Simple Query - SQL Server Forums

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.

All Forums

Old Forums

CLOSED - General SQL Server

A Simple Query

Author

Topic

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-03 : 05:59:58

Hi!

Can any sql guru try hands on it to optimize and reduce the I/O cost of the following query
i'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 rows

I want to query the tbl like

select * from tb1
where
(((((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 steps

1) 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 col4

4) last, 3rd result (and) either in col1 and col2

Edited by - ereader on 12/03/2002 06:02:50

Edited by - ereader on 12/03/2002 06:04:41

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-03 : 06:20:41

Please do not cross-post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22102

As far as optimizing your query, as I indicated in the other thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22061

You cannot do any optimization when using LIKE matches with leading % wildcards. Indexes cannot be used and the entire table would have to be scanned. The only way to improve the situation is to normalize the table so that only one value appears in the column or columns. It will increase the number of rows, but by indexing them properly you will reduce I/O operations significantly, as only those rows you are searching for will be retrieved.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-03 : 06:33:49

It wouldn't be more efficient but to make the query easier to understand/write you might consider putting the values into a table variable or temp table so the query becomes

select distinct tbl.*
from tb1, #col
where ((col1 like #col.value and #col.num = 1)
or (col2 like #col.value and #col.num = 1))
and ((col3 like #col.value and #col.num = 3)
or (col4 like #col.value and #col.num = 3))

==========================================
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.

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-03 : 08:15:25

quote:

It wouldn't be more efficient but to make the query easier to understand/write you might consider putting the values into a table variable or temp table so the query becomes

select distinct tbl.*
from tb1, #col
where ((col1 like #col.value and #col.num = 1)
or (col2 like #col.value and #col.num = 1))
and ((col3 like #col.value and #col.num = 3)
or (col4 like #col.value and #col.num = 3))

==========================================
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.

can u explain little bit more about implementing in temp tables

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 09:19:21

Try normalizing your tables. It may seem like a paid, but take the fields of "xxx-xxx-xxx-xxx" and break them up into different records in another table.

For example, if the string "BOS-LGA-BRT" means you travelled from BOS to LGA to BRT, instead of storing:



ID  Code
1   BOS-LGA-BRT

store it as:

ID   Leg   Code
1    1     BOS
1    2     LGA
1    3     BRT

Then you have data you can analyze. Or, you can create a temp table or reporting table that does this for certain ranges of records for you.

If you have a table of ints, with values of the starting point of each code in the string (that is, 1,5,9, ..etc...) called "Positions" you could create a large table like this:


SELECT 
   ID, ((Pos-1)/4)+1 as Leg, SUBSTRING(Col1,Pos,3) as AirportCode
FROM
   tb1
CROSS JOIN
   Positions
WHERE
   SUBSTRING(Col1,Pos,3) <> ''

Do something like that into a table, with indexes, and you can find whatever code you like.

Think about the pros and cons of restructuring your data, either for reporting or the way in which is it stored and used. Of course, you will have a very long table potentially (many more records than before), but SQL should be able to handle it.

I just don't like storing important information all concatenated like that into a varchar() field.

- Jeff

Edited by - jsmith8858 on 12/03/2002 21:53:17

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-03 : 09:45:24

quote:

Try normalizing your tables. It may seem like a paid, but take the fields of "xxx-xxx-xxx-xxx" and break them up into different records in another table.

For example, if the string "BOS-LGA-BRT" means you travelled from BOS to LGA to BRT, instead of storing:

[code]

ID Code
1 BOS-LGA-BRT

store it as:

ID Leg Code
1 1 BOS
1 2 LGA
1 3 BRT

Jeff is it possible for the table of 200000 rows if we consider it for optimization and I/o cost

-xxx-xxx-xxx-xxx-xxx-xxx-xxx-xxx-xxx- ( with length of 500 char)
this is a single row(s) length, we have data more than of 400 bytes /row and am concerning about the data we have in our table of 200000 rows and it's keep on increasing

Thanks a lot for your valuable suggestion

Edited by - ereader on 12/03/2002 09:46:49

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 09:54:23

That is a lot of data ... it's the only thing I can think of to avoid the %LIKE% operator, unless my select query works faster than using LIKE, which I am not so sure of.

That is try a query like:

SELECT
ID, ((Pos-1)/4)+1 as Leg, SUBSTRING(Col1,Pos,3) as AirportCode
FROM
tb1
CROSS JOIN
Positions
WHERE
SUBSTRING(Col1,Pos,3) = 'BOS'

you'd have to test .... doubt it'd be faster, though.

Good luck!

- Jeff

Tim
Starting Member

392 Posts

Posted - 2002-12-03 : 20:01:03

....and it might help if you posted t-sql to create and populate the example together with an expected result output.

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-04 : 06:55:58

quote:

quote:

It wouldn't be more efficient but to make the query easier to understand/write you might consider putting the values into a table variable or temp table so the query becomes

select distinct tbl.*
from tb1, #col
where ((col1 like #col.value and #col.num = 1)
or (col2 like #col.value and #col.num = 1))
and ((col3 like #col.value and #col.num = 3)
or (col4 like #col.value and #col.num = 3))

==========================================
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.

can u explain little bit more about implementing in temp tables

create table #col (value char(4), num int)
insert #col select '%AUS%', 1
insert #col select '%LAW%', 1
insert #col select '%ALO%', 1
insert #col select '%OMA%', 1
insert #col select '%LGW%', 1
insert #col select '%LHR%', 1
insert #col select '%LTN%', 1
insert #col select '%STN%', 1
insert #col select '%LBB%', 3
insert #col select '%DAY%', 3
insert #col select '%IBM%', 3
insert #col select '%AUS%', 3
insert #col select '%LAW%', 3
insert #col select '%ALO%', 3
insert #col select '%OMA%', 3
insert #col select '%DLH%', 3
insert #col select '%LBB%', 3
insert #col select '%LBB%', 3

select distinct tbl.*
from tb1, #col
where ((col1 like #col.value and #col.num = 1)
or (col2 like #col.value and #col.num = 1))
and ((col3 like #col.value and #col.num = 3)
or (col4 like #col.value and #col.num = 3))

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources