select multi range - 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

SQL Server 2012 Forums

Transact-SQL (2012)

select multi range

Author

Topic

jaleel2007
Starting Member

4 Posts

Posted - 2015-03-06 : 23:55:42

Hi there

Simply put, I want to select multi range from a table

It is like that:
CREATE TABLE uorr(
[A_Y] [int] NULL,
[A_X] [int] NULL,
[B_y] [int] NULL,
[B_X] [int] NULL,
[C_Y] [int] NULL,
[C_X] [int] NULL,
)
uorr has more than 30000 rec.
my way to query is very slow

declare @ax int
declare @ay int
set @ax =552507
set @ay =3627106

SELECT A_Y, A_X, B_Y,B_X, C_Y,C_X
FROM uorr
where ((A_X between @ax - 5 And @ax +5) and (A_Y between @ay - 5 And @ay +5))
or
((b_X between @ax - 5 And @ax +5) and (b_Y between @ay - 5 And @ay +5))
or
((C_X between @ax - 5 And @ax +5) and (C_Y between @ay - 5 And @ay +5))

sorry my English speaking is not very good

jaleel2007
Starting Member

4 Posts

Posted - 2015-03-07 : 01:34:09

Is there any better way?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-03-07 : 14:52:41

You require to check all 6 columns, so the system has no choice but scan the entire table. Likely, there is no other better query.

I guess your table has more than the 6 columns. Because of that the query can be slow. The importance is here : your select statement return only the 6 columns? If so, create a non-clustered index for that 6 columns.

30000 rec of 6 int columns is not big one. With the index, the query will be fast.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-07 : 16:51:41

Each one of the WHERE filters are multiple range criterias.
Never going to have performance with the current design.
For 30,000 records, how can you tell what is slow and what is fast?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jaleel2007
Starting Member

4 Posts

Posted - 2015-03-08 : 06:14:00

thanks for reply.

actually there are 24 columns and 100 rec. adds to it everyday.
these number are UTM coordinate and it is a land database that I using in GIS. It is about landuse.
is there any way to do this query and kept result. something like this

declare @ax int
declare @ay int
set @ax =552507
set @ay =3627106

INSERT INTO t1
select A_Y, A_X, B_Y,B_X, C_Y,C_X,....,L_Y,L_X
FROM uorr into test
where ((A_X between @ax - 5 And @ax +5) and (A_Y between @ay - 5 And @ay +5))

INSERT INTO t1
select A_Y, A_X, B_Y,B_X,C_Y,C_X,....,L_Y,L_X
FROM uorr into test
where ((b_X between @ax - 5 And @ax +5) and (b_Y between @ay - 5 And @ay +5))

INSERT INTO t1
select A_Y, A_X, B_Y,B_X,C_Y,C_X
FROM uorr into test
where ((C_X between @ax - 5 And @ax +5) and (C_Y between @ay - 5 And @ay +5))
.
.
.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-08 : 10:11:19

Have you tried geometry datatype?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jaleel2007
Starting Member

4 Posts

Posted - 2015-03-10 : 02:48:14

quote:
Originally posted by SwePeso

Have you tried geometry datatype?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

No, because the data as you see, is come from GPS receiver, and I have an interface c# not work directly on sql server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources